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?

Answer 1

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 [e-mail]

---

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.

Answer 2

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.

Answer 3

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.

Answer 4

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 [e-mail]

---

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.

Answer 5

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 [e-mail]

---

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.

Answer 6

Second adapter did the work, tx