Access Database + TableAdapters: Return last ID

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?

Access will not do two command in the command text. You should do the insert if it goes ok with the same connection change the command text to Select Scope_Identity() and issue executescaler then. This will only work as long as you don;t change the connection.

Quote:

---

Originally Posted by GaryMazzone

Access will not do two command in the command text. You should do the insert if it goes ok with the same connection change the command text to Select Scope_Identity() and issue executescaler then. This will only work as long as you don;t change the connection.

---

So how do I do that..? Using a TableAdapter I mean?

I don't use any of the wizards all coded by hand using ADO.Net

Well yeah... I know how to do it by hand. But I'm not doing it by hand at the moment so that isn't really much use to me :(

I could browse through the (far too long) designer code for the DataSet and code it in myself, but I don't like that at all, as Designer files are often rewritten automatically and I'm afraid my changes will be lost.

[sarcasm on]
Yea for wizards they make everything easy Yea
[sarcasm off]

Bump?

The simple answer is that you don't. In order to get the last generated ID you must issue the query immediately after the insert. That's only possible if you're inserting one record at a time. If you're inserting multiple records from a DataTable then it's simply not possible. You basically have three choices:

1. Insert one record at a time and issue a query immediately afterwards.

2. Insert all the data and then re-query the database to get all the data.

3. Use a proper database.

I'm not inserting multiple records. It's only ever one record at a time.


What I need this for is simple: suppose I am adding users. I have a form with a grid that displays all users. I also have a form that views the properties of one user, and finally I have a form that allows one to edit or create a new user (basically the same as the view form, except the fields are now editable).

When someone creates a new user, he opens the 'edit' form, fills in the details, saves it, and then I want to send him to the View form with the user he just saved opened. The simplest way to do that, or so I thought, is simply to let the insert method return the ID of the user I just inserted, and then query the database for the user with that ID and display it. Of course I could also store the details of the user in some class and send that to the view form, and I guess that's what I have to do in this case, but I still think it's odd that I cannot get the last ID inserted...

I understand now that I cannot use the first method (point 1 in my first post) because I am using Access, ok, fair enough. But why can I not use the second method? I should be able to send whatever query I want, right? But the wizard doesn't let me use a SELECT SCOPE_IDENTITY() because it cannot parse this query (probably because there's no "FROM" clause...?) and I get an error (without proper description) when I run it.

Have you actually checked whether SCOPE_IDENTITY exists in Access? It's a function in SQL Server but it may well not exist in Access. Even if it did, what use would it be in this instance, given that your query is executed in a different scope to the insert? I think Access, or more specifically Jet, may support @@IDENTITY, which is a global variable that contains the last ID generated by the system.

I tried @@Identity as well as a few other variants that I came across, but none work. I'm sure if I just send those commands to the database it would work, but the wizard does not allow me to create those commands in the first place because it cannot parse them.

Quote:

---

Originally Posted by NickThissen

I'm sure if I just send those commands to the database it would work, but the wizard does not allow me to create those commands in the first place because it cannot parse them.

---

You're apparently wrong on both counts. The fact that the wizard can't parse the query is no impediment to creating it in the DataSet designer. The problem is, it appears not to work anyway. In my testing, @@IDENTITY returned zero every time. This is not a TableAdapter but an Access issue, so the question belongs in Database Development, not VB.NET.

It works just fine here, as long as I send the SELECT @@IDENTITY over the same connection as the insert query. If I close and re-open the connection (or create an entirely new connection I suppose) then it returns 0, but if I just use the same connection, such as the following test code, it returns the correct ID:

vb.net Code:

Dim connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;", _
                                       "C:\Users\Nick\Desktop\database.mdb")
 
        Using conn As New OleDbConnection(connString)
 
            conn.Open()
 
            '1. Insert new user
            Using cmd As New OleDbCommand()
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "INSERT INTO TestUsers (Username, Pw, Points) " & _
                                  "VALUES (@Username, @Pw, @Points)"
 
                cmd.Parameters.AddWithValue("@Username", "TestUser")
                cmd.Parameters.AddWithValue("@Pw", "TestPassword")
                cmd.Parameters.AddWithValue("@Points", 38)
 
                cmd.ExecuteNonQuery()
            End Using
 
            '2. Get its ID
            Using cmd As New OleDbCommand()
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT @@IDENTITY"
 
                Dim id = CInt(cmd.ExecuteScalar())
                MessageBox.Show(id.ToString())
            End Using
 
            conn.Close()
        End Using

I don't know how the wizard handles connections, I suppose it closes and re-opens the connection after every query which could be a problem for me even if I could send the query in the first place. So I guess my only option is not to use TableAdapters then..? Or switch to SQL Server of course, but that's not really an option at the moment.

In the DataSet designer, you can set the access level of the TableAdapter's Connection property to Public. You are then able to create your own OleDbConnection object and assign it to the Connection property of both TableAdapters.