Retrieving Primary Key field after insert

[al]

I have this code that utilizes the command builder to generate commands for a data adapter.

Private da as CoreLab.MySql.MySqlDataAdapter

Public Sub Update(ByRef ds As DataSet, ByVal Table As String)
Dim cmdBuilder As New CoreLab.MySql.MySqlCommandBuilder
Dim strPrimaryKey As String
Dim strInsertCommand As String

strPrimaryKey = ds.Tables(Table).PrimaryKey(0).ColumnName()

cmdBuilder.DataAdapter = da
cmdBuilder.RefreshSchema()
da.InsertCommand = cmdBuilder.GetInsertCommand(True)
da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
strInsertCommand = da.InsertCommand.CommandText
da.InsertCommand.CommandText = strInsertCommand & "; select " & strPrimaryKey & " from " & Table & " where " & strPrimaryKey & " = last_insert_id()"
da.InsertCommand.FetchAll = True

da.Update(ds, Table)
End Sub

I do the extra work with the InsertCommand because I want to retrieve the new PK when an insert occurs. Problem is the datarow is not getting updated with the new primary key. Any idea what I am missing here?

Andy

[Serious]

Please provide full code for this example. Do you initialize MySqlDataAdapter with select command? Moreover, what is the sense of such query?

Code: Select all

select PK from TABLE where PK = last_insert_id()

Maybe better is

Code: Select all

select last_insert_id()