Returning Identifier on a database Insert command

  • Thread starter Thread starter Russ
  • Start date Start date
R

Russ

How can I return the indentifier column that is autogenerated by SQL Server
when a new record is created?

I have a form where the top half is used to enter a company name, address,
and contact information, the bottom half is used to add additional business
locations. In order to save additional locations I need the unique
identifier that was created when the top half of my form is saved so that
the relationships can be preserved.

Perhaps this is a question better suited for the sql programming newsgroup,
but I am hoping someone here can help me.
 
How can I return the indentifier column that is autogenerated by SQL Server
when a new record is created?

I have a form where the top half is used to enter a company name, address,
and contact information, the bottom half is used to add additional business
locations. In order to save additional locations I need the unique
identifier that was created when the top half of my form is saved so that
the relationships can be preserved.

Perhaps this is a question better suited for the sql programming newsgroup,
but I am hoping someone here can help me.
Click to expand...

Here is one possible way:

private void SQLEntry () {
SqlCommand sqlCmd = new SqlCommand ( );
sqlCmd.Connection = new SqlConnection ("server =
'XXX';Database = 'Test'; Trusted_Connection=Yes");

sqlCmd.CommandText = "INSERT INTO Test (Firstname, Lastname)" +
"SET @Id = @@Identity"; <-- THAT'S THE KEY PART

//Create a parameter called Id with value set to nothing
sqlCmd.Parameters.Add ("@Id", SqlDbType.Int);
sqlCmd.Parameters ["@Id"].Direction = ParameterDirection.Output;

sqlCmd.Connection.Open ( );
sqlCmd.ExecuteNonQuery ( );

Stephan.
 
You should use "select scope_identity()" if you're using SQL Server 2000
because if the table has any triggers it could return the wrong value. If
the trigger performs an insert into another table then @@Identity will
return the value for the other table, not the table you just inserted into.

HTH,

Morgan
 
Back
Top