Retrieving Primary Key field after insert

Retrieving Primary Key field after insert

Postby al » Tue 28 Jun 2005 06:00

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
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?


Postby Serious » Thu 30 Jun 2005 12:29

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()

Return to dotConnect for MySQL