ADO: Identity using Table Adapter

  • nevets2001uk2 / 102 / Sun, 05 Apr 2009 11:09:00 GMT / Comments (5)

  • I'm using an access database for my website and have been creating table adapters as per the Data Access tutorials.

    However I can't work out how to return a new record's ID after inserting it since access won't accept multiple statements. I have a query in the tableadapter which inserts the new record with default values. Then plan would then be to get the ID of the new record and used that to update the related tables.

    I've tried created a new query in the table adapter of SELECT ......IDENTITY as NEWID to return the most recent ID but this does not seem to work.

    I have seen posts suggesting it can be done using a partial class but I can't see any that fit my situation and I'm still quite new to this.

    Any help is much appreciated

  • Keywords:

    identity, table, adapter, ado

  • http://dotnet.itags.org/dotnet-ado/103067/«« Last Thread - Next Thread »»

    1. I haven't used table adapters with Access, but I suspect that the way to do this is to run some SQL to Select ......Identity in the ObjectDataSource's Inserted event. Hopefully the connection will still be open at this point, otherwise it won't work.

      mikesdotnetting | Sun, 06 Jan 2008 09:48:00 GMT |

    2. Thanks. Any ideas or pointers how I can get that working?

      nevets2001uk2 | Sun, 06 Jan 2008 09:49:00 GMT |

    3. nevets2001uk2:

      However I can't work out how to return a new record's ID after inserting it since access won't accept multiple statements.

      Hi

      Not a direct answer -- But can you lookhttp://www.experts-exchange.com/Databases/MS_Access/Q_21752215.html

      andhttp://support.microsoft.com/default.aspx/kb/232144

      They are not .NET related, but i am sure you can port the code from ADODB to ADO.NET.

      Hope it helps to some extent.

      VJ

      vijayvrr_2001 | Sun, 06 Jan 2008 09:50:00 GMT |

    4. Having looked at it a bit more closely, I don't think you can use the Inserted event after all. It seems that this is the way to do it:

      http://groups.archivesat.com/Access_Databases_and_AccessDataSource_Control/thread382893.htm
      http://www.redmountainsw.com/wordpress/archives/returning-identity-column-of-autoincrement-fields

      mikesdotnetting | Sun, 06 Jan 2008 09:51:00 GMT |

    5. After a bit more investigation I found an article that suggested a solution... I followed it through and ended up with the following code in a code file.

      1Imports Microsoft.VisualBasic2Imports System.Data34Namespace SJGTableAdapters5 PartialPublic Class PHOTOGRAPHSTableAdapter67Private IdentityQueryAs New System.Data.OleDb.OleDbCommand("SELECT ......IDENTITY",Nothing)89Private Sub _adapter_RowUpdated(ByVal senderAs Object,ByVal eAs System.Data.OleDb.OleDbRowUpdatedEventArgs)Handles _adapter.RowUpdated10If e.StatementType = StatementType.InsertAndAlso e.Status = UpdateStatus.ContinueThen11 IdentityQuery.Connection = e.Command.Connection12 e.Row("PHOTOGRAPH_ID") = IdentityQuery.ExecuteScalar13End If14 End Sub1516 End Class17End Namespace

      It looks similar to the suggestions from above but I can't work out how I use this now that I've written it. The insert query is called from the codebehind from one of my pages and debugging it, it seems it does not touch this partial class.

      nevets2001uk2 | Sun, 06 Jan 2008 09:52:00 GMT |