Results 1 to 18 of 18

Thread: Retrieve Access AutoNumber Value After Insert

  1. #1

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Retrieve Access AutoNumber Value After Insert

    C# version here.

    People are often asking how to retrieve the value from an AutoNumber column in an Access database after a record has been inserted. This is particularly important when working with related data, where you need to get the ID of a parent record to add to a child record. While doing this with an ADO.NET provider that accepts multiple SQL statements per batch, e.g. SqlClient, is simple, I've previously said that this cannot be done with Access and the Jet OLE DB provider without simply requerying the database. I've recently discovered that this is not the case, so I decided to create a demo project to illustrate how it can be done.

    The key is the RowUpdated event of the OleDbDataAdapter. It is raised each time the adapter sends a row to the database to be saved, whether that be for an insert, update or delete. We can handle this event and detect an insert, then query the database to get the last generated ID. Because the the event is raised immediately after a row is saved, we can get the ID for multiple inserted rows and each will be correct at the time.

    The attached solution was created in VS 2010, so it will require that or VB 2010 Express to open. If you don't already have either and don't want to install either, you can still at least look at the code in an older version. The required MDB file is included.

    Pay most attention to the way the PK columns of each DataTable are configured to generate local IDs and how the RowUpdated events are used to retrieve the database IDs to replace them.

    EDIT: I have added a second project that does basically the same thing but with a typed DataSet. I had to configure the relation in the DataSet designer to enforce a foreign key constraint and cascade updates. I also added the GetLastAutoNumber query to each table adapter. I originally tried using an extra table adapter with a single GetLastAutoNumber query so as not to duplicate. That didn't work though, because the insert was done inside a transaction and the query then could not see the new ID. By using the same table adapter for the insert and the query, the same connection and the same transaction is used.
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Re: Retrieve Access AutoNumber Value After Insert

    This seems useful! I haven't tried to use it in my own code yet, but it seems to be what I need. I am usually manually running the SELECT @@IDENTITY command after an insert, but for one application I am using the dataset designer and it does not allow me to create that command (it's not recognized as a valid SQL statement I guess). I even tried getting to the underlying OleDbConnection and running the command on that but it always returns 0 for some reason. I hope using the RowUpdated event it will give me the ID instead

  3. #3

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Using a Data Source and Typed DataSet

    Here's another version of the solution reworked to use a Data Source and typed DataSet. A lot of the code has gone because most of the configuration is now done in the DataSet designer. Note that I had to edit the relation in the designer to enforce a foreign key constraint and cascade updates. I also was not able to use a custom table adapter to retrieve the IDs. I still had to create my own command because it had to use the same transaction, and therefore the same connection, as the adapter that inserted the row.

    Attachment removed. See edit in post #1.

  4. #4
    PowerPoster
    Join Date
    Apr 2007
    Location
    The Netherlands
    Posts
    5,070

    Re: Retrieve Access AutoNumber Value After Insert

    Looks good. Correct me if I'm wrong, you are not using the 'QueriesTableAdapter', are you? I see you were able to enter the SELECT @@IDENTITY query though, I never managed that. It does warn that it can't parse the SQL but it still lets you finish, it didn't do that for me... But I wasn't using a separate table adapter for it (I tried to get the query in the same table adapter, one for every table), perhaps that was the problem...

  5. #5

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieve Access AutoNumber Value After Insert

    Quote Originally Posted by NickThissen View Post
    Looks good. Correct me if I'm wrong, you are not using the 'QueriesTableAdapter', are you? I see you were able to enter the SELECT @@IDENTITY query though, I never managed that. It does warn that it can't parse the SQL but it still lets you finish, it didn't do that for me... But I wasn't using a separate table adapter for it (I tried to get the query in the same table adapter, one for every table), perhaps that was the problem...
    I created that QueriesTableAdapter with a view to using it to retrieve the last ID. As you say, it did warn that the SQL couldn't be parsed but it did allow me to finish and the query did, in theory, work. The problem was, updating with the adapters used a transaction and the QueriesTableAdapter would create its own connection. The query would thus execute outside the transaction and therefore was unable to see the ID generated inside. I went back to a manual command but I neglected to remove that adapter.

    Maybe a query per table adapter would have executed within the same transaction, although doesn't seem right to use the same query in multiple adapters. I might have a bit closer look on the weekend. I just threw that together when I finished work so it might not be optimal.

  6. #6

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Using a Data Source and Typed DataSet

    Nick, I tried again and this time I added the same query to both table adapters. It still warned me that the SQL could not be parsed but it let me finish and it works. I'm now using the table adapter in the RowUpdated event handler. Because it's the same table adapter that did the insert, the query is executed inside the same transaction so the new ID is available. See post #1 for a new project.

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Retrieve Access AutoNumber Value After Insert

    jmc,

    I download and installed the Type Dataset demo. It works great. I was able to add a new form and use your dataset to mimic what how your form performed.

    I ran into problems when I tried to add a new datasource and modify it to work like yours.

    I cut and pasted the "GetLastautonumber" functions. It was giving me an invalid index error in "parentAdapter_RowUpdated" when it call "GetLastAutoNumber" . I added this code to each tableadapter,
    Code:
                Me._commandCollection(1) = New Global.System.Data.OleDb.OleDbCommand()
                Me._commandCollection(1).Connection = Me.Connection
                Me._commandCollection(1).CommandText = "SELECT @@IDENTITY"
                Me._commandCollection(1).CommandType = Global.System.Data.CommandType.Text
    and increased the _commandcollection array, like it was in your dataset,
    Code:
    Me._commandCollection = New Global.System.Data.OleDb.OleDbCommand(1) {}
    This solved the index error but then I got a casting error on this line,
    Code:
    DirectCast(e.Row, ParentChildDataSet.ParentRow).ParentID = lastAutoNumber
    Am I on the right track with my approach or have I missing something important. I made the mistake of modifying the relationship in the dataset designer and it removed all the code I had added.

    So I just want to no if I was headed in the right direction and what other code need to be added to the dataset designer file before I start over again.

    thanks

  8. #8

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieve Access AutoNumber Value After Insert

    If you're using a typed DataSet then you shouldn't be adding any code to the table adapters manually. I added the extra query to each table adapter in the DataSet designer.

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Retrieve Access AutoNumber Value After Insert

    jmc,
    Thanks, I was way off. Do I add the query like "SELECT @@IDENTITY", is that all it takes? I'll work on that tomorrow.

    Thanks again

  10. #10

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieve Access AutoNumber Value After Insert

    Quote Originally Posted by wes4dbt View Post
    jmc,
    Thanks, I was way off. Do I add the query like "SELECT @@IDENTITY", is that all it takes? I'll work on that tomorrow.

    Thanks again
    Yep. Right-click on the table adapter and select Add Query or the like and then follow the prompts. You may well be told that the SQL code couldn't be parsed but press on anyway.

  11. #11
    New Member
    Join Date
    May 2011
    Posts
    3

    Re: Retrieve Access AutoNumber Value After Insert

    Hi jmcilhinney,

    I am a newbie to VB.NET.

    I have an Access 2007 database which has 5 tables related to one another in a Parent-Child relationship. I have an application, created using Visual Basic 2008 Express Edition in which I enter details of a pastor into text boxes and 2 ComboBoxes, one for selecting District and the other for selecting Block (sub-district) and a CheckedListBox called VillageChkListBox1 on a form.

    The user enters details of a pastor into the TextBoxes and chooses a District from the District ComboBox which filters the Block ComboBox to display only Blocks (sub-districts) based on the selection from the District ComboBox. Based on the selection made in the Block ComboBox, the CheckedListBox is populated with the list of villages which the user can check mark for the particular pastor.

    I use the BindingNavigator bar at the top of the form to save the details entered into the database. The user presses the, '+' sign to do all the adds and the floppy sign to do all the saves.

    The new pastor details entered into the textboxes are saved in the Pastor table of the database and the villages selected from the CheckedListBox goes into a separate table called the PastorVillage table.

    The PastorVillage table has PastVillID(Auto-number field), PastorID, VillID among other fields in the database table.

    Beth Massi has shown how to retrieve the ParentID in a similar manner as yours, (http://blogs.msdn.com/b/bethmassi/ar...-database.aspx) BUT, this saves the Parent into a separate table and the Child into a separate table. However this is not what I want.

    My question is, how can I retrieve the PastorID from the Pastor table and the checked villages from the CheckedListBox and DUMP BOTH of them by adding rows to the PastorVillage table in Access ??
    Is this possible ???

    Can you please help me out ?
    Regards,
    ~~Maneesh

  12. #12

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieve Access AutoNumber Value After Insert

    You're situation is exactly the same, except that there are two parent tables and one child table. There are two relationships so you simply do the same thing twice. Insert the two parent records and get the generated IDs for those two records and put them into the child record.

  13. #13
    New Member
    Join Date
    May 2011
    Posts
    3

    Re: Retrieve Access AutoNumber Value After Insert

    Can you please be more descriptive ??? It would be very helpful for a newbie like me if you explained what you exactly mean to say in a little bit more detail so that I am comfortable with catching every word and working towards the right direction to find the solution I am looking for.

    Appreciate you very much !
    Thanks.
    ~~Maneesh
    Last edited by si_the_geek; Apr 6th, 2012 at 04:19 AM. Reason: removed email address to protect you from spam bots, members can contact you via the forums

  14. #14

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieve Access AutoNumber Value After Insert

    All you need to do is follow the example that I have already provided. You're trying to make this difficult when it's not. I've shown you what to do already. If you have two parent IDs to retrieve then you simply do it twice. That's all.

  15. #15
    Addicted Member
    Join Date
    Jul 2013
    Posts
    139

    Re: Retrieve Access AutoNumber Value After Insert

    Ok JM question. Now lets say i have the parent table on the first form, row is added and all fields are updated. I click the next button to open the child-table on the next form.
    A row is added the information is updated, and a query is run to get the last auto number.

    Now what i just can't wrap my head around since its a completely new form basically calling last number it can go through all the tables.
    How can i guarantee for it to look at a specific table for the id?

  16. #16
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Retrieve Access AutoNumber Value After Insert

    jmc -

    Thank you for your excellent tutorial!

    I was curious about the auto-generated code, so I put the following statements at the end of the Form1.Load method:

    Code:
    Debug.WriteLine(parentCommandBuilder.GetUpdateCommand.CommandText)
    Debug.WriteLine(parentCommandBuilder.GetDeleteCommand.CommandText)
    Here's the Debug information from the Immediate window:

    Code:
    UPDATE [Parent] SET [ParentName] = ? WHERE (([ParentID] = ?) AND ((? = 1 AND [ParentName] IS NULL) OR ([ParentName] = ?)))
    DELETE FROM [Parent] WHERE (([ParentID] = ?) AND ((? = 1 AND [ParentName] IS NULL) OR ([ParentName] = ?)))
    I don't understand the Where clauses of these commands. The first group of the clause is obvious "([ParentID] = ?)", but I'm not sure why the "AND ((? = 1 AND [ParentName] IS NULL) OR ([ParentName] = ?))" is necessary.

    Thanks for your help.

  17. #17

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieve Access AutoNumber Value After Insert

    Quote Originally Posted by Mark@SF View Post
    jmc -

    Thank you for your excellent tutorial!

    I was curious about the auto-generated code, so I put the following statements at the end of the Form1.Load method:

    Code:
    Debug.WriteLine(parentCommandBuilder.GetUpdateCommand.CommandText)
    Debug.WriteLine(parentCommandBuilder.GetDeleteCommand.CommandText)
    Here's the Debug information from the Immediate window:

    Code:
    UPDATE [Parent] SET [ParentName] = ? WHERE (([ParentID] = ?) AND ((? = 1 AND [ParentName] IS NULL) OR ([ParentName] = ?)))
    DELETE FROM [Parent] WHERE (([ParentID] = ?) AND ((? = 1 AND [ParentName] IS NULL) OR ([ParentName] = ?)))
    I don't understand the Where clauses of these commands. The first group of the clause is obvious "([ParentID] = ?)", but I'm not sure why the "AND ((? = 1 AND [ParentName] IS NULL) OR ([ParentName] = ?))" is necessary.

    Thanks for your help.
    That's doing concurrency checking. Rather than matching on just the primary key, it's matching on the original values of all columns. That's makes sure that you don't overwrite changes made by another user since you first retrieved the data. The parameter values used in the WHERE clause will be the original values stored in the DataRows and, if a column doesn't match, it is assumed that another user has made changes since you retrieve the data and the record is not modified and an exception is thrown. It's then up to you, as the application developer, to catch that exception, notify the user and merge the changes in the most appropriate way. You can look into Optimistic Concurrency for more information. It's done in order to allow multi-user access to data, i.e. no locking of rows or tables, while still ensuring changes aren't arbitrarily overwritten.

  18. #18
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Retrieve Access AutoNumber Value After Insert

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width