How do I retrieve an Auto Increment Primary Key after Insert

[ASasek]

I need to retrieve the primary key of a table after I do an insert into the table. I have a function with parameters for my connection info, a dataset and the SQL used to get the dataset (used for the command builder). Everything works fine... the row is getting written back to the table, but I need to get the Auto Increment Primary Key for the new row that was created.

Currently I am using another select statement after the row is written to the table ("Select max(primarykeyfield) as MaxID from TableName") to get the Primary Key of the row just written. I was hoping to reduce the number of trips to the database. Any advice would be appreciated.

-ASasek

[Oleg]

If you don't fill your key field in DataSet, you'll automatically get there a value of autoincrement field after the execution of DataAdapter.Update.
You can use MySqlCommand.InsertId property also to get the last inserted value.

[Guest]

Thanks Oleg.

After I call the DataAdapter.Update and I check my dataset, it still says that the keyfield is NULL. I will check the InsertID.

[ASasek]

After I UPDATE, I still have a NULL in my dataset (in the newly added row in the table).

Here's my table structure:

CREATE TABLE `game` (
`GameID` int(10) NOT NULL auto_increment,
`Player1ID` int(4) NOT NULL default '0',
`Player2ID` int(4) NOT NULL default '0',
`WinnerID` int(4) NOT NULL default '0',
`GameDate` date NOT NULL default '0000-00-00',
PRIMARY KEY (`GameID`)
) TYPE=MyISAM;



And here is the code that I'm using:

Code: Select all

        Dim ds As New DataSet
        Dim ds2 As New DataSet
        Dim dr As DataRow
        Dim sSQL As String
        Dim sUserID As String
        Dim sPassword As String
        Dim sServer As String
        Dim sDatabase As String
        Dim MyConnection As New MySqlConnection
        Dim MyCommand As New MySqlCommand
        Dim MyAdapter As New MySqlDataAdapter
        Dim MyCB As MySqlCommandBuilder

        sUserID = "UserID"
        sPassword = "Password"
        sServer = "localhost"
        sDatabase = "Database"

        m_GameID = 0
        sSQL = "select * from game where GameID = -1"

        MyConnection.UserId = sUserID
        MyConnection.Password = sPassword
        MyConnection.Host = sServer
        MyConnection.Database = sDatabase

        MyCommand.CommandText = sSQL
        MyCommand.Connection = MyConnection

        MyAdapter.SelectCommand = MyCommand
        MyAdapter.Fill(ds, "Data1")

        If ds.Tables(0).Rows.Count = 0 Then
            dr = ds.Tables(0).NewRow

            dr.Item("Player1ID") = 1
            dr.Item("Player2ID") = 2
            dr.Item("WinnerID") = 2
            dr.Item("GameDate") = Now.Date
            ds.Tables(0).Rows.Add(dr)

            If ds.HasChanges(DataRowState.Added) Then
                MyCommand.CommandText = sSQL
                MyCommand.Connection = MyConnection
                MyAdapter.SelectCommand = MyCommand
                MyCB = New MySqlCommandBuilder(MyAdapter)

                ds2 = ds.GetChanges(DataRowState.Added)
                MyAdapter.Update(ds2, "Data1")

                'What is GameID?
            End If

            MyConnection.Close()
            MyConnection = Nothing
            MyAdapter = Nothing
        End If

The code works and the row is successfully added to the table. The Auto Increment Primary key is properly set to the next number, but I am still unable to access it through code.

What am I doing wrong ... Any help would be greatly appreciated.

Thanks!
-ASasek

[Oleg]

DataAdapter automatically sets values of autoincrement fields if you pass the same DataSet to Fill and Update methods. In your example another dataset is passed to Update. In this case you can write the next code:

Code: Select all

MyConnection.Open()
ds2 = ds.GetChanges(DataRowState.Added)
MyAdapter.Update(ds2, "Data1")
m_GameID = MyCB.GetInsertCommand.InsertId
MyConnection.Close()

or pass an original dataset to Update:

Code: Select all

MyAdapter.Update(ds, "Data1")
m_GameID = ds.Tables(0).Rows(0).Item("GameId")

[ASasek]

Thanks!

That worked perfectly.

[nuntio2000]

I've used this for insert new data leaving the PK fied as NULL.
It works. But i don't know how to avoid multiple update of the same data.

Code: Select all

///  
		/// Tables must be created before you call this function 
		///  
		/// if you have InnoDB table, set this parameter = false 
		public void CopyDataSet(DataSet dataset, string connectionString, bool delayed) 
		{ 

			MySqlConnection connection = new MySqlConnection(connectionString); 
			connection.Open(); 

			MySqlLoader loader = new MySqlLoader(); 
			loader.Connection = connection; 
			loader.Delayed = delayed; 

			foreach (DataTable table in dataset.Tables) 
			{ 
				loader.TableName = table.TableName; 
				loader.CreateColumns(); 
				loader.Open(); 

				foreach (DataRow row in table.Rows) 
				{ 
					for (int i = 0; i  
		/// Tables must be created before you call this function 
		///  
		/// if you have InnoDB table, set this parameter = false 
		public void CopyDataSet(DataSet dataset, MySqlConnection  connection, bool delayed) 
		{ 

			if (connection.Ping())
			{
				MySqlLoader loader = new MySqlLoader(); 
				loader.Connection = connection; 
				loader.Delayed = delayed; 

				foreach (DataTable table in dataset.Tables) 
				{ 
					loader.TableName = table.TableName; 
					loader.CreateColumns(); 
					loader.Open(); 

					foreach (DataRow row in table.Rows) 
					{ 
						for (int i = 0; i < table.Columns.Count; ++i) 
							loader.SetValue( i, row[i]); 
						loader.NextRow(); 
					} 
					loader.Close(); // flush table to database 
				} 
			}
		}

[Oleg]

You cannot get values of autoincrement fields in case of using Loader.
You can execute a query SELECT last_insert_id().
It will be a last inserted value, previous values can be calculated just decreasing this value on 1.