how do I get the ID of a row I just added using OleDbDataAdapter.Update() ?

Discussion in 'ASP .Net' started by Bennett Haselton, Oct 17, 2004.

  1. Suppose I add a new row to a table in a dataset, and then I use an
    OleDbDataAdapter to add that new row to a SQL Server database using
    OleDbDataAdapter.Update(), as in the following code:

    dsLocalDataSet.user_postRow newRow =
    dsLocalDataSet1.user_post.Newuser_postRow();
    newRow.post_text = this.lblHiddenMessageStorage.Text;
    newRow.post_datetime = System.DateTime.Now;
    dsLocalDataSet1.user_post.Adduser_postRow(newRow);
    this.oleDbUserPostAdapter.Update(dsLocalDataSet1.user_post);

    How do I get the ID in the underlying database, of the new row that I
    just added?

    It might not be the same as the ID of the row that was just added to
    the DataSet table. Even if new IDs are generated sequentially in both
    the underlying database table and in the DataSet table, and say the
    records already in the table had IDs 1, 2, 3, 4, and 5, when I add the
    new row to the DataSet table it will get ID 6. However, in the
    meantime, some other process might have added a new row to the table
    in the underlying database, so that when the row actually gets added
    through adapter's Update() method, in the database it will get ID 7.
    So I can't just look at the ID of the row in the DataSet.

    In MySQL you can do SELECT LAST_INSERT_ID() to get the last
    automatically generated value that was inserted into an AUTO_INCREMENT
    column by your current connection. Is there an equivalent for SQL
    Server? (Or even better, a way to get the value using the built-in
    functions of the typed DataSet, so you don't have to get it
    inelegantly passing a raw command to SQL Server, which is error-prone
    in case you ever want to switch to a different data source type, etc.)

    -Bennett
     
    Bennett Haselton, Oct 17, 2004
    #1
    1. Advertising

  2. (Bennett Haselton) wrote in message news:<>...
    > Suppose I add a new row to a table in a dataset, and then I use an
    > OleDbDataAdapter to add that new row to a SQL Server database using
    > OleDbDataAdapter.Update(), as in the following code:
    >
    > dsLocalDataSet.user_postRow newRow =
    > dsLocalDataSet1.user_post.Newuser_postRow();
    > newRow.post_text = this.lblHiddenMessageStorage.Text;
    > newRow.post_datetime = System.DateTime.Now;
    > dsLocalDataSet1.user_post.Adduser_postRow(newRow);
    > this.oleDbUserPostAdapter.Update(dsLocalDataSet1.user_post);
    >
    > How do I get the ID in the underlying database, of the new row that I
    > just added?
    >
    > It might not be the same as the ID of the row that was just added to
    > the DataSet table. Even if new IDs are generated sequentially in both
    > the underlying database table and in the DataSet table, and say the
    > records already in the table had IDs 1, 2, 3, 4, and 5, when I add the
    > new row to the DataSet table it will get ID 6. However, in the
    > meantime, some other process might have added a new row to the table
    > in the underlying database, so that when the row actually gets added
    > through adapter's Update() method, in the database it will get ID 7.
    > So I can't just look at the ID of the row in the DataSet.
    >
    > In MySQL you can do SELECT LAST_INSERT_ID() to get the last
    > automatically generated value that was inserted into an AUTO_INCREMENT
    > column by your current connection. Is there an equivalent for SQL
    > Server? (Or even better, a way to get the value using the built-in
    > functions of the typed DataSet, so you don't have to get it
    > inelegantly passing a raw command to SQL Server, which is error-prone
    > in case you ever want to switch to a different data source type, etc.)
    >
    > -Bennett


    Well I found how to do this -- in SQL Server, you call SELECT
    @@IDENTITY to get the last automatically inserted ID, however it's
    only valid if the connection has not been closed since the update
    occurred for which you're trying to get the last inserted ID. So if
    you just call a data adapter's Update() method, and the connection was
    in the closed state before you called it, the connection will be
    closed again when Update returns, and you'll lose the information
    about the last inserted ID. So you have to put the adapter's
    connection object in the Open state first, then call Update() and then
    do a SELECT @@IDENTITY query.

    Here's a utility function that does it:

    public static int GetLastInsertID(OleDbConnection conn)
    {
    /*
    * The connection object must be open at the time this method
    * is called, and the connection cannot have been closed since
    * the insertion occurred for which you are trying to get the
    * last inserted ID. Otherwise, an exception will be thrown.
    */
    OleDbDataAdapter objAdapter = new OleDbDataAdapter(
    "SELECT @@IDENTITY AS 'Identity';", conn
    );
    DataSet ds = new DataSet();
    objAdapter.Fill(ds, "tablename");
    string strID = ds.Tables["tablename"].Rows[0]["Identity"].ToString();
    if (strID == "")
    {
    throw new Exception("GetLastInsertID called but @@IDENTITY returned
    nothing");
    }
    int nID = System.Int32.Parse(strID);
    return nID;
    }

    and the code that calls the function:

    >>>

    dsLocalDataSet.wbuserRow newRow =
    this.dsLocalDataSet1.wbuser.NewwbuserRow();
    newRow.username = "abc";
    newRow.password_hash = "def";
    newRow.email_address = "ghi";
    this.dsLocalDataSet1.wbuser.AddwbuserRow(newRow);

    // must open connection first or GetLastInsertID won't work
    this.oleDbLocalConnection.Open();

    this.oleDbWbuserAdapter.Update(dsLocalDataSet1.wbuser);
    Response.Write("last insert: " +
    DatabaseUtils.GetLastInsertID(this.oleDbLocalConnection).ToString() +
    "<br>\n");
    this.oleDbLocalConnection.Close();
    >>>
     
    Bennett Haselton, Oct 18, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. darrel

    OleDbDataAdapter vs OleDbCommand

    darrel, Nov 23, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    7,036
    Scott M.
    Nov 25, 2005
  2. amessimon

    Issues with OledbDataAdapter.Update()

    amessimon, Apr 30, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    3,254
    Natty Gur
    May 2, 2004
  3. joun
    Replies:
    9
    Views:
    2,280
    W.G. Ryan eMVP
    Nov 30, 2004
  4. joun
    Replies:
    5
    Views:
    2,706
  5. tom c
    Replies:
    6
    Views:
    524
    tom c
    Sep 6, 2006
Loading...

Share This Page