none
How to newly inserted row's identity column value? RRS feed

  • Question

  • NOTE: im using VS 2010 with MS Access

    I have searched the intenet and tried the solutions they mentioned to get the value of the identity column. (newly inserted).

    I tried TableAdapter.Update() and TableAdapter.Insert() method but both methods always returns 1.

    I also tried doing DataSet.AcceptChyanges() and then checking value of the column, but it remains -1

    how do i solve the problem

    Tuesday, December 7, 2010 6:28 PM

Answers


  • The methods you mention will return the number of rows affected by the insert or update.
    I.e. you will get 1 because you insert 1 row.

    You need to use @@IDENTITY to get the value of the last inserted row (I assume you mean AutoNumber since this is Access).

    You can do it like this, the MyTable has ID (AutoNumber) and MyText (text).

        static string path = @"C:\<your path>";
        static string db = @"<your .mdb>";
        static void Main(string[] args)
        {
          string cs = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\{1}", path, db);
          using (OleDbConnection con = new OleDbConnection(cs))
          {
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM MyTable";
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
    
            OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da);
            da.InsertCommand = cmdBuilder.GetInsertCommand();
            
            DataRow newRow = dt.NewRow();
            newRow["MyText"] = "Test";
            dt.Rows.Add(newRow);
            Console.WriteLine("Number of updated rows: {0}", da.Update(dt));
    
            cmd.CommandText = "SELECT @@IDENTITY";
            Console.WriteLine("New identity: {0}", cmd.ExecuteScalar());
            con.Close();
    
            // NOTE: You can't use "SELECT @@IDENTITY" on a different connection. So the below will return 0
            con.Open();
            cmd.CommandText = "SELECT @@IDENTITY";
            Console.WriteLine("New identity: {0}", cmd.ExecuteScalar());
            con.Close();
          }
    

     

     


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:32 AM
    Wednesday, December 8, 2010 10:13 AM
  • Edit: Just noticed you mentioned using Access as the db.  I don't know if you added that later or if I simply missed/ignored that.  With that said, I am unsure of Scope_identity equivalent in Access (if there is one).  Perhaps consider implementing SQLServer/Express -- superior in every way to MS Access. Maybe this: http://support.microsoft.com/kb/815629

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/thread/b64f9aee-b074-4c37-a2f8-c881da6358b1

     

    Basically, you want to add a query to the TableAdapter and end the SQL Statement with "SELECT @scope_identity":

    http://www.asp.net/data-access/tutorials/creating-a-data-access-layer-cs

    Scroll down to about midway at the section titled: "Creating Custom Insert, Update, and Delete Methods"


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:32 AM
    Tuesday, December 7, 2010 7:33 PM

All replies

  • Edit: Just noticed you mentioned using Access as the db.  I don't know if you added that later or if I simply missed/ignored that.  With that said, I am unsure of Scope_identity equivalent in Access (if there is one).  Perhaps consider implementing SQLServer/Express -- superior in every way to MS Access. Maybe this: http://support.microsoft.com/kb/815629

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataset/thread/b64f9aee-b074-4c37-a2f8-c881da6358b1

     

    Basically, you want to add a query to the TableAdapter and end the SQL Statement with "SELECT @scope_identity":

    http://www.asp.net/data-access/tutorials/creating-a-data-access-layer-cs

    Scroll down to about midway at the section titled: "Creating Custom Insert, Update, and Delete Methods"


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:32 AM
    Tuesday, December 7, 2010 7:33 PM

  • The methods you mention will return the number of rows affected by the insert or update.
    I.e. you will get 1 because you insert 1 row.

    You need to use @@IDENTITY to get the value of the last inserted row (I assume you mean AutoNumber since this is Access).

    You can do it like this, the MyTable has ID (AutoNumber) and MyText (text).

        static string path = @"C:\<your path>";
        static string db = @"<your .mdb>";
        static void Main(string[] args)
        {
          string cs = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\{1}", path, db);
          using (OleDbConnection con = new OleDbConnection(cs))
          {
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM MyTable";
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
    
            OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da);
            da.InsertCommand = cmdBuilder.GetInsertCommand();
            
            DataRow newRow = dt.NewRow();
            newRow["MyText"] = "Test";
            dt.Rows.Add(newRow);
            Console.WriteLine("Number of updated rows: {0}", da.Update(dt));
    
            cmd.CommandText = "SELECT @@IDENTITY";
            Console.WriteLine("New identity: {0}", cmd.ExecuteScalar());
            con.Close();
    
            // NOTE: You can't use "SELECT @@IDENTITY" on a different connection. So the below will return 0
            con.Open();
            cmd.CommandText = "SELECT @@IDENTITY";
            Console.WriteLine("New identity: {0}", cmd.ExecuteScalar());
            con.Close();
          }
    

     

     


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:32 AM
    Wednesday, December 8, 2010 10:13 AM