How do I retrieve an Auto Increment Primary Key after Insert

How do I retrieve an Auto Increment Primary Key after Insert

Postby ASasek » Wed 10 Nov 2004 14:20

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
ASasek
 
Posts: 8
Joined: Wed 10 Nov 2004 14:03

Re: How do I retrieve an Auto Increment Primary Key after In

Postby Oleg » Wed 10 Nov 2004 15:34

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.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Postby Guest » Wed 10 Nov 2004 16:33

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.
Guest
 

Postby ASasek » Thu 11 Nov 2004 16:45

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 :cry: ... Any help would be greatly appreciated.

Thanks!
-ASasek
ASasek
 
Posts: 8
Joined: Wed 10 Nov 2004 14:03

Postby Oleg » Wed 17 Nov 2004 13:45

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")
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Postby ASasek » Wed 17 Nov 2004 21:01

Thanks!

That worked perfectly.
ASasek
 
Posts: 8
Joined: Wed 10 Nov 2004 14:03

If could be usefull....

Postby nuntio2000 » Sat 20 Nov 2004 10:03

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 < table.Columns.Count; ++i)
                  loader.SetValue( i, row[i]);
               loader.NextRow();
            }
            loader.Close(); // flush table to database
         }
      }
      
      ///
      /// 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
            }
         }
      }
nuntio2000
 
Posts: 19
Joined: Fri 19 Nov 2004 18:51
Location: South Italy

Re: If could be usefull....

Postby Oleg » Mon 22 Nov 2004 11:12

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.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56


Return to dotConnect for MySQL