Home » SQL ServerRSS

How To Get The Identity Primary Key Value?

Hi,

 

I have a table  with a automatic increasion primary key. I'm using DataTable to insert new row in the table. Every time I add new record after delete the lastetr record I getting diffirent primary key from what  is rely in the database.

After the first add the rest of the records are getting rigth primary key.

 

How can I fix this problm?

 

Technical information: I'm using with Microsoft SQL Server 2005 and Visual Studio 2008.

 

 

This is the code I use to insert new record:

 

 

SqlConnection cn = newSqlConnection(connString);

 

SqlDataAdapter da = newSqlDataAdapter("SELECT * FROM Region", cn);

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

 

SqlCommand cmd = newSqlCommand(@"INSERT INTO Region

                                  ( RegionDescription )

                                  VALUES( @RegionDescription )", cn);

           

cmd.Parameters.Add("@RegionDescription", SqlDbType.NVarChar, 50,

                "RegionDescription");

da.InsertCommand = cmd;

 

cn.Open();

DataTable dt = newDataTable("Region");

da.Fill(dt);

               

DataRow row = dt.NewRow();

row["RegionDescription"] = "Test " + row["RegionID"].ToString();

dt.Rows.Add(row);

               

da.Update(dt);

cn.Close();

 

Help me if you can.

Thanks.
 

4 Answers Found

 

Answer 1

Hi Magic 32,

you know the following code is kinda weird......

DataRow row  = dt.NewRow();
row["RegionDescription"] = "Test " + row["RegionID"].ToString();
dt.Rows.Add(row);

Since row is a whole new row, why you assign row["RegionID"] to row["RegionDescription"]...?

the "Region" field should be null then.......!

 

you will never know what number the automatic  increasion primary  key is,

before you update the table  by da.Update()........

 

Answer 2

Thanks for your reply.

This line:

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

 

Let my knew what is next primary key.

 

Answer 3

Hi Magic 32,

how it let you know?

 

Answer 4

Hi Magic,

You can use "SELECT SCOPE_IDENTITY()" statement to get the newly created identity  key for the new row. You can modify InsertCommand of SqlDataAdapter in order to refresh the content of the new row  in datatable  once a new row is inserted. For example:

da.InsertCommand.CommandText = "Insert into Orders (CustomerID, OrderDate) Values (@CustomerID,@OrderDate); Select @OrderID = SCOPE_IDENTITY()"

After you call da.Update(dt) method, the newly created identity key  for that new row is retrieved into the row in datatable.

In additon, you can handle the RowUpdated event of SqlDataAdapter to get the newly created  identity key. For example:

SqlDataAdapter da = CreateMyDataAdapter();
SqlConnection cn = da.SelectCommand.Connection;
string sql  = "Select @OrderID = SCOPE_IDENTITY()";
SqlCommand cmdRefresh = new SqlCommand(sql, cn);
SqlParameter p = cmdRefresh.Parameters.Add("@OrderID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
da.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);

da.Fill(dt);

//Modify the datatable//..........

da.Update(dt);

 

privatevoid da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
  if ((e.Status == UpdateStatus.Continue) && (e.StatementType == StatementType.Insert))
  {
    cmdRefresh.ExecuteNonQuery();
    e.Row["OrderID"] = cmdRefresh.Parameters["@OrderID"].Value; 
  }
}

 

Best regards,
Alex Liang

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter