Hi,

I am using an Access database (mdb file) and for the first time ever I'm connecting to it using a bunch of TableAdapters created in a DataSet.xsd file.

I added an Insert query to a TableAdapter, and now I need to retrieve the ID (autonumber) of the last inserted record. Usually, I would send a "SELECT SCOPE_IDENTITY()" query and it would return the ID, but for some reason I cannot get this to work with the TableAdapter.

I tried two methods:

1. Add the SELECT SCOPE_IDENTITY() query directly after the INSERT query in the TableAdapter. This method is described in every website I found by googling my problem. Suppose my INSERT query is this:
Code:
INSERT INTO Test VALUES("1", "2", "3")
then I should now modify it into
Code:
INSERT INTO Test VALUES("1", "2", "3");
SELECT SCOPE_IDENTITY()
I also changed the ExecuteMode to Scalar.

Ok, I run this code, but it doesn't work. It gives me an error saying Characters found after end of SQL statement.

Is it because I am using Access and not SQL Server maybe? I dunno... But whatever I try it doesn't work. It's not a spelling error, and I did type the semicolon to separate the two queries so as far as I know it should work...


2. Add a separate Select query (selected 'Select query that returns a single value' in the designer) that simply executes this:
Code:
SELECT SCOPE_IDENTITY()
Then after using the Insert query I can simply call this query method and get the last Id.
But unfortunately this doesn't work either. It doesn't even let me add this query in the designer, it says Unable to parse query text...


I'm at a loss here. This should be a simple problem but the solutions posted online don't work for me... Any help?