none
Using OleDbDataAdapter to add table row, then retrieve autonumber field

    Question

  • I want to use ADO.Net to add a row to a table with an autonumber field. After the row is added I need to retrieve the value for the autonumber field in the row just added. This ws easy using DAO recordsets since the recordset is updated with the value when the recordset is updated. This doesn't happen with DataSet. Here is an example of the code I am using in VB.Net:
    Dim odaGeo As New Data.OleDb.OleDbDataAdapter("SELECT * FROM geometry", m_cn.ConnectionString)
    Dim dsGeo As New Data.DataSet
    odaGeo.Fill(dsGeo, "geometry")
    Dim ocbGeo As New OleDb.OleDbCommandBuilder(odaGeo)
    odaGeo.InsertCommand = ocbGeo.GetInsertCommand
    Dim drGeo As Data.DataRow = dsGeo.Tables("geometry").NewRow
    drGeo.BeginEdit()
    drGeo.Item("BaseAngle1") = Geometry.BaseAnglesDeg(1)
    drGeo.Item("BaseAngle2") = Geometry.BaseAnglesDeg(2)
    drGeo.Item("Description") = Geometry.Description
    ... ' Other fields are set
    drGeo.EndEdit() dsGeo.Tables("geometry").Rows.Add(drGeo) Dim ID As Long = drGeo.Item("ID")
    In the last line the value for ID throws an exception.
    How can I get the value for the autonumber field for this INSERT statement?
    Sunday, January 10, 2010 7:58 AM

Answers

  • Hello Michael,

     

    Welcome to ADO.NET DataSet forum!

     

    It seems that the OleDbCommandBuilder does not create certain SQL command to get the identity value from the database after the insert operation.   We have some workarounds here to get the auto-increased number by manually editing the InsertCommand of the OleDbDataAdapter.  We can use the dynamic SQL created by OleDbCommandBuilder, but once the OleDbDataAdapter is related to an OleDbCommandBuilder, the Update method will use the dynamic SQL of the command builder even we have manually edit it.   So here I create another OleDbDataAdapter to make the update operations.   Here are some sample codes:

     

    1.      Use output parameter of the InsertCommand to set the auto-increased column value:

    ======================================================================================
            Dim connStr = "Provider=SQLNCLI10;Server=(local);Database=MyNewTest;Uid=user; Pwd=******;"

            Dim selectCmd = "select * from IdentityTest"

            Dim da As New OleDbDataAdapter(selectCmd, connStr)

            Dim table As New DataTable

            da.Fill(table)

            Dim builder As New OleDbCommandBuilder(da)

            da.InsertCommand = builder.GetInsertCommand

            da.InsertCommand.CommandText += "; set ? = SCOPE_IDENTITY()"

            da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

           

            Dim paramID As New OleDbParameter("ID", OleDbType.Integer)

            paramID.SourceColumn = "ID"

            paramID.Direction = ParameterDirection.Output

            da.InsertCommand.Parameters.Add(paramID)

           

            Dim da2 As New OleDbDataAdapter

            da2.InsertCommand = da.InsertCommand

            Dim row As DataRow = table.NewRow

            row.BeginEdit()

            row("Name") = "New Name"

            row.EndEdit()

            table.Rows.Add(row)

            da2.Update(table)

            MessageBox.Show(row("ID").ToString())
    ======================================================================================

     

    2.      Use first returned row to update auto-increased column value:

    ======================================================================================

            Dim connStr = "Provider=SQLNCLI10;Server=(local);Database=MyNewTest;Uid=sa; Pwd=******;"

            Dim selectCmd = "select * from IdentityTest"

            Dim da As New OleDbDataAdapter(selectCmd, connStr)

            Dim table As New DataTable

            da.Fill(table)

            Dim builder As New OleDbCommandBuilder(da)

            da.InsertCommand = builder.GetInsertCommand

            da.InsertCommand.CommandText += "; select * from IdentityTest where ID = @@IDENTITY"

            da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

            Dim da2 As New OleDbDataAdapter

            da2.InsertCommand = da.InsertCommand

            Dim row As DataRow = table.NewRow

            row.BeginEdit()

            row("Name") = "New Name"

            row.EndEdit()

            table.Rows.Add(row)

            da2.Update(table)

            MessageBox.Show(row("ID").ToString())

    ======================================================================================

     

    Here I am using SQL Server 2008 database for testing.  If you are using Microsoft Access database, it would be much harder to accomplish this, since Access does not support multiple-line SQL statements.   The workaround to get the autonumber column could be manually calling OleDbCommand to do the insert operations of all the added rows (DataTable.GetChanges(DataRowState.Added)), and then use the SQL statement “SELECT @@IDENTITY” to get the autonumber.  

    ======================================================================================

            Dim getIdentityString = "SELECT @@IDENTITY"

           

            Dim insertedRows = table.GetChanges(DataRowState.Added)

            For Each row In insertedRows.Rows

                ' set parameter values

     

                insertCmd.CommandText = builder.GetInsertCommand

                insertCmd.ExecuteNonQuery()

     

                insertCmd.CommandText = getIdentityString

                Dim index = DirectCast(insertCmd.ExecuteScalar(), Integer)

         Next

    ======================================================================================

     

    Or if the Access database file cannot be updated by multiple users, we can also set the autonumber column’s AutoIncrement to True, AutoIncrementSeed to 1 and AutoIncrementStep to 1. 

    ======================================================================================

            table.Columns(0).AutoIncrement = True

            table.Columns(0).AutoIncrementSeed = 1

            table.Columns(0).AutoIncrementStep = 1

    ======================================================================================

     

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, January 11, 2010 6:51 AM
    Moderator
  • Hi Michael,

    Example and some links found here:

    "Getting AutoNumber from Access via "SELECT @@IDENTITY" needs to be done in same connection as the INSERT."
    http://blogs.msdn.com/spike/archive/2009/12/02/getting-autonumber-from-access-via-select-identity-needs-to-be-done-in-same-connection-as-the-insert.aspx

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    Monday, January 11, 2010 8:17 AM
  • Thank you for your answer. I found the following code that seems to also work well:
    http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.71%29.aspx
    Here is the evet code that I used:

        Function WriteGeometry(ByVal Geometry As CGeometry) As Integer
            ' ...
            Dim odaGeo As New Data.OleDb.OleDbDataAdapter("SELECT * FROM geometry", m_cn.ConnectionString)
            ' Include an event to fill in the Autonumber value.
            AddHandler odaGeo.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
            ' ...
            Return Geometry.ID
        End Function
    
        Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
            ' http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.71%29.aspx
            ' Include a variable and a command to retrieve the identity value from the Access database.
            Dim newID As Integer = Integer.MinValue
            Dim idCMD As New OleDbCommand("SELECT @@IDENTITY", args.Command.Connection) 'm_cn)
    
            If args.StatementType = StatementType.Insert _
            AndAlso args.Status = UpdateStatus.Continue _
            Then
                ' Retrieve the identity value and store it in the CategoryID column.
                newID = CInt(idCMD.ExecuteScalar())
                args.Row("ID") = newID
            End If
    
        End Sub
    

    Notice that I used the connection string "args.Command.Connection" and not the connection that I opened the DB with, "m_cn". Using m_cn returns 0. To be sure the value was being set I initialized the newID to Integer.MinValue.

    This lead to another question-
    What about all the other fields in the record not explicitly set in the INSERT that may be set to default values. For example, (trivial) setting a date field using the default value as Now(). The only way I can see getting it is to do a query using the ID:
         "SELECT * FROM geometry WHERE ID=" & newID & ";"
    In fact, it looks like the code above does just that, executes a SELECT statement in the event code to get the ID.
         "SELECT @@IDENTITY"

    Will using the above code snip work with SQL Server? I am stubbing out the code using Access but the intention is to use a SQL Server DB later on.

    Why would I continue to use ADO.Net rather than ADODB or even DAO? Is ADO.Net so much more efficient that my code will be faster suing ADO.Net? Especially since a ADODB or DAO recordset takes all this complexity away.

    Monday, January 11, 2010 6:36 PM
  • Hello Michael,

     

    Thanks for sharing your solution here. 

     

    As you said, the default value at the database side can be retrieved by another SELECT query.  If the table also has autonumber column, the query can be “SELECT * FROM TableName WHERE ID = @@IDENTITY”.   This method works fine in SQL Server as well.  Besides, SQL Server supports the multiple-line SQL statements, so we can declare both the insert and select queries inside the DbDataAdapter’s InsertCommand.  I have provide some sample codes in my last post.  (the part: 2.  Use first returned row to update auto-increased column value)  

     

    Another option is to use ADO.NET DataColumn has a property DefaultValue, if the default value can be determined at the client side.

     

     

    For the question whether to use ADO.NET, ADODB, or DAO, in my opinion it is all determined by our detailed scenario.  For Access database files, ADODB or DAO can be enough.  But I think for large databases like SQL Server, Oracle and DB2, ADO.NET is much better on .NET platform.    Here are some references on the comparison on ADO.NET and ADO:

    http://msdn.microsoft.com/en-us/library/904fck4k.aspx

    http://msdn.microsoft.com/en-us/library/3y0bb1zd.aspx

    http://en.wikipedia.org/wiki/Comparison_of_ADO_and_ADO.NET

     

    In ADO.NET, we can also use strongly typed DataSet, which can be more Object-Oriented. 

     

    If you will use large database in the future, I recommend you migrate the application to use ADO.NET.   Also, from .NET 3.5, we have some new technologies to do the data access, like LINQ to SQL and Entity Framework.   ADO.NET is still their foundation. 

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, January 12, 2010 3:06 AM
    Moderator

All replies

  • Hello Michael,

     

    Welcome to ADO.NET DataSet forum!

     

    It seems that the OleDbCommandBuilder does not create certain SQL command to get the identity value from the database after the insert operation.   We have some workarounds here to get the auto-increased number by manually editing the InsertCommand of the OleDbDataAdapter.  We can use the dynamic SQL created by OleDbCommandBuilder, but once the OleDbDataAdapter is related to an OleDbCommandBuilder, the Update method will use the dynamic SQL of the command builder even we have manually edit it.   So here I create another OleDbDataAdapter to make the update operations.   Here are some sample codes:

     

    1.      Use output parameter of the InsertCommand to set the auto-increased column value:

    ======================================================================================
            Dim connStr = "Provider=SQLNCLI10;Server=(local);Database=MyNewTest;Uid=user; Pwd=******;"

            Dim selectCmd = "select * from IdentityTest"

            Dim da As New OleDbDataAdapter(selectCmd, connStr)

            Dim table As New DataTable

            da.Fill(table)

            Dim builder As New OleDbCommandBuilder(da)

            da.InsertCommand = builder.GetInsertCommand

            da.InsertCommand.CommandText += "; set ? = SCOPE_IDENTITY()"

            da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

           

            Dim paramID As New OleDbParameter("ID", OleDbType.Integer)

            paramID.SourceColumn = "ID"

            paramID.Direction = ParameterDirection.Output

            da.InsertCommand.Parameters.Add(paramID)

           

            Dim da2 As New OleDbDataAdapter

            da2.InsertCommand = da.InsertCommand

            Dim row As DataRow = table.NewRow

            row.BeginEdit()

            row("Name") = "New Name"

            row.EndEdit()

            table.Rows.Add(row)

            da2.Update(table)

            MessageBox.Show(row("ID").ToString())
    ======================================================================================

     

    2.      Use first returned row to update auto-increased column value:

    ======================================================================================

            Dim connStr = "Provider=SQLNCLI10;Server=(local);Database=MyNewTest;Uid=sa; Pwd=******;"

            Dim selectCmd = "select * from IdentityTest"

            Dim da As New OleDbDataAdapter(selectCmd, connStr)

            Dim table As New DataTable

            da.Fill(table)

            Dim builder As New OleDbCommandBuilder(da)

            da.InsertCommand = builder.GetInsertCommand

            da.InsertCommand.CommandText += "; select * from IdentityTest where ID = @@IDENTITY"

            da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

            Dim da2 As New OleDbDataAdapter

            da2.InsertCommand = da.InsertCommand

            Dim row As DataRow = table.NewRow

            row.BeginEdit()

            row("Name") = "New Name"

            row.EndEdit()

            table.Rows.Add(row)

            da2.Update(table)

            MessageBox.Show(row("ID").ToString())

    ======================================================================================

     

    Here I am using SQL Server 2008 database for testing.  If you are using Microsoft Access database, it would be much harder to accomplish this, since Access does not support multiple-line SQL statements.   The workaround to get the autonumber column could be manually calling OleDbCommand to do the insert operations of all the added rows (DataTable.GetChanges(DataRowState.Added)), and then use the SQL statement “SELECT @@IDENTITY” to get the autonumber.  

    ======================================================================================

            Dim getIdentityString = "SELECT @@IDENTITY"

           

            Dim insertedRows = table.GetChanges(DataRowState.Added)

            For Each row In insertedRows.Rows

                ' set parameter values

     

                insertCmd.CommandText = builder.GetInsertCommand

                insertCmd.ExecuteNonQuery()

     

                insertCmd.CommandText = getIdentityString

                Dim index = DirectCast(insertCmd.ExecuteScalar(), Integer)

         Next

    ======================================================================================

     

    Or if the Access database file cannot be updated by multiple users, we can also set the autonumber column’s AutoIncrement to True, AutoIncrementSeed to 1 and AutoIncrementStep to 1. 

    ======================================================================================

            table.Columns(0).AutoIncrement = True

            table.Columns(0).AutoIncrementSeed = 1

            table.Columns(0).AutoIncrementStep = 1

    ======================================================================================

     

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, January 11, 2010 6:51 AM
    Moderator
  • Hi Michael,

    Example and some links found here:

    "Getting AutoNumber from Access via "SELECT @@IDENTITY" needs to be done in same connection as the INSERT."
    http://blogs.msdn.com/spike/archive/2009/12/02/getting-autonumber-from-access-via-select-identity-needs-to-be-done-in-same-connection-as-the-insert.aspx

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    Monday, January 11, 2010 8:17 AM
  • Thank you for your answer. I found the following code that seems to also work well:
    http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.71%29.aspx
    Here is the evet code that I used:

        Function WriteGeometry(ByVal Geometry As CGeometry) As Integer
            ' ...
            Dim odaGeo As New Data.OleDb.OleDbDataAdapter("SELECT * FROM geometry", m_cn.ConnectionString)
            ' Include an event to fill in the Autonumber value.
            AddHandler odaGeo.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
            ' ...
            Return Geometry.ID
        End Function
    
        Private Sub OnRowUpdated(ByVal sender As Object, ByVal args As OleDbRowUpdatedEventArgs)
            ' http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.71%29.aspx
            ' Include a variable and a command to retrieve the identity value from the Access database.
            Dim newID As Integer = Integer.MinValue
            Dim idCMD As New OleDbCommand("SELECT @@IDENTITY", args.Command.Connection) 'm_cn)
    
            If args.StatementType = StatementType.Insert _
            AndAlso args.Status = UpdateStatus.Continue _
            Then
                ' Retrieve the identity value and store it in the CategoryID column.
                newID = CInt(idCMD.ExecuteScalar())
                args.Row("ID") = newID
            End If
    
        End Sub
    

    Notice that I used the connection string "args.Command.Connection" and not the connection that I opened the DB with, "m_cn". Using m_cn returns 0. To be sure the value was being set I initialized the newID to Integer.MinValue.

    This lead to another question-
    What about all the other fields in the record not explicitly set in the INSERT that may be set to default values. For example, (trivial) setting a date field using the default value as Now(). The only way I can see getting it is to do a query using the ID:
         "SELECT * FROM geometry WHERE ID=" & newID & ";"
    In fact, it looks like the code above does just that, executes a SELECT statement in the event code to get the ID.
         "SELECT @@IDENTITY"

    Will using the above code snip work with SQL Server? I am stubbing out the code using Access but the intention is to use a SQL Server DB later on.

    Why would I continue to use ADO.Net rather than ADODB or even DAO? Is ADO.Net so much more efficient that my code will be faster suing ADO.Net? Especially since a ADODB or DAO recordset takes all this complexity away.

    Monday, January 11, 2010 6:36 PM
  • Hello Michael,

     

    Thanks for sharing your solution here. 

     

    As you said, the default value at the database side can be retrieved by another SELECT query.  If the table also has autonumber column, the query can be “SELECT * FROM TableName WHERE ID = @@IDENTITY”.   This method works fine in SQL Server as well.  Besides, SQL Server supports the multiple-line SQL statements, so we can declare both the insert and select queries inside the DbDataAdapter’s InsertCommand.  I have provide some sample codes in my last post.  (the part: 2.  Use first returned row to update auto-increased column value)  

     

    Another option is to use ADO.NET DataColumn has a property DefaultValue, if the default value can be determined at the client side.

     

     

    For the question whether to use ADO.NET, ADODB, or DAO, in my opinion it is all determined by our detailed scenario.  For Access database files, ADODB or DAO can be enough.  But I think for large databases like SQL Server, Oracle and DB2, ADO.NET is much better on .NET platform.    Here are some references on the comparison on ADO.NET and ADO:

    http://msdn.microsoft.com/en-us/library/904fck4k.aspx

    http://msdn.microsoft.com/en-us/library/3y0bb1zd.aspx

    http://en.wikipedia.org/wiki/Comparison_of_ADO_and_ADO.NET

     

    In ADO.NET, we can also use strongly typed DataSet, which can be more Object-Oriented. 

     

    If you will use large database in the future, I recommend you migrate the application to use ADO.NET.   Also, from .NET 3.5, we have some new technologies to do the data access, like LINQ to SQL and Entity Framework.   ADO.NET is still their foundation. 

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, January 12, 2010 3:06 AM
    Moderator
  • Hello Michael,

     

    How is the problem now?   If you need any further assistance, please feel free to let me know.


    Have a nice day!
     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, January 14, 2010 12:52 AM
    Moderator
  • Second adapter did the work, tx
    Monday, February 25, 2013 7:19 PM