Selecting @@Identity from an Access Database

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Hi to the group.

I am at my wits end with this problem. I am working in Visual Web
Developer 2005, .NET 2, C#, MS-Access.

I have created a dataset (xsd) with some datatables in for interacting
with my database tables. I am attempting to create a new insert query
in a table adapter which will return the @@identity value of the newly
inserted column. Everything i have read while researching this problem
states to create a new query on the table adaper containing two sql
statements i.e:

INSERT INTO myTable VALUE (?,?,?,?);
SELECT @@Identity;

however, when i do this the required parameters arent automatically
added to the query method, and then when i enter them manually and run
it i get an OleDbException error of:

"System.Data.OleDb.OleDbException : Characters found after end of SQL
statement."

Am quickly coming to the conclusion that the only way to get round
this is to create my own data access classes as i would with .net 1.1.
Can anyone help me?

Ta!

Marc
 
¤ Hi to the group.
¤
¤ I am at my wits end with this problem. I am working in Visual Web
¤ Developer 2005, .NET 2, C#, MS-Access.
¤
¤ I have created a dataset (xsd) with some datatables in for interacting
¤ with my database tables. I am attempting to create a new insert query
¤ in a table adapter which will return the @@identity value of the newly
¤ inserted column. Everything i have read while researching this problem
¤ states to create a new query on the table adaper containing two sql
¤ statements i.e:
¤
¤ INSERT INTO myTable VALUE (?,?,?,?);
¤ SELECT @@Identity;
¤
¤ however, when i do this the required parameters arent automatically
¤ added to the query method, and then when i enter them manually and run
¤ it i get an OleDbException error of:
¤
¤ "System.Data.OleDb.OleDbException : Characters found after end of SQL
¤ statement."
¤
¤ Am quickly coming to the conclusion that the only way to get round
¤ this is to create my own data access classes as i would with .net 1.1.
¤ Can anyone help me?

Jet doesn't support multi-statement batch commands.

There is an MS KB article that covers this topic but it may not help you if this is an ASP.NET app.

http://support.microsoft.com/kb/815629

You may want to consider creating your own method for generating a unique identity value.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
¤ Hi to the group.
¤
¤ I am at my wits end with this problem. I am working in Visual Web
¤ Developer 2005, .NET 2, C#, MS-Access.
¤
¤ I have created a dataset (xsd) with some datatables in for interacting
¤ with my database tables. I am attempting to create a new insert query
¤ in a table adapter which will return the @@identity value of the newly
¤ inserted column. Everything i have read while researching this problem
¤ states to create a new query on the table adaper containing two sql
¤ statements i.e:
¤
¤ INSERT INTO myTable VALUE (?,?,?,?);
¤ SELECT @@Identity;
¤
¤ however, when i do this the required parameters arent automatically
¤ added to the query method, and then when i enter them manually and run
¤ it i get an OleDbException error of:
¤
¤ "System.Data.OleDb.OleDbException : Characters found after end of SQL
¤ statement."
¤
¤ Am quickly coming to the conclusion that the only way to get round
¤ this is to create my own data access classes as i would with .net 1.1.
¤ Can anyone help me?

Jet doesn't support multi-statement batch commands.

There is an MS KB article that covers this topic but it may not help you if this is an ASP.NET app.

http://support.microsoft.com/kb/815629

You may want to consider creating your own method for generating a uniqueidentity value.

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

Many thanks for this suggestions. Eventually i came up with my own
awful bodge....

_myAdapter.Connection.Open();
//do my insert
_myAdapter.Insert(this.Number, this.Title,
this.FileName);
//create a command object to return @@identity from
the same db.
_myAdapter.Connection.Close();

makes you feel dirty writing code like that....
 
Back
Top