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
Retrieving Primary Key field after insert
Please provide full code for this example. Do you initialize MySqlDataAdapter with select command? Moreover, what is the sense of such query?Maybe better is
Code: Select all
select PK from TABLE where PK = last_insert_id()
Code: Select all
select last_insert_id()