Inserting record into database and returning the ID of the new row

[Dylan Parry]

Hi folks,

This is really confusing me. I am trying to insert a row into a
database, and then use scope_identity() to return the value of the ID
column for the record I have just created. The code I am using is
something like the below:

--- Start ---
// Build query
this.command = this.connection.CreateCommand();
this.command.CommandText = "INSERT INTO table (foo) VALUES(@foo)";

// Query parameters
this.command.Parameters.Add("@foo", SqlDbType.VarChar).Value = "bar";

// Execute query
int rows = this.command.ExecuteNonQuery();

// Clear the parameters
this.command.Parameters.Clear();

// Create a new command to get the ID
this.command.CommandText = "SELECT scope_identity()";
int id = Convert.ToInt32(this.command.ExecuteScalar());
---- End ----

But when I run this code I get an error telling me that I cannot cast to
Int32 from DBNull.Value. Obviously this is correct, but why am I not
actually getting the value of the ID column returned by the latter
query?

Also, if I change "scope_identity()" to "@@identity" I get errors about
some of my parameters not being defined, when they clearly are. This
also occurs if I attempt to combine the INSERT and SELECT statements
into the one command.

Any ideas?

 

[Sericinus hunter]

Hi folks,

This is really confusing me. I am trying to insert a row into a
database, and then use scope_identity() to return the value of the ID
column for the record I have just created. The code I am using is
something like the below:

--- Start ---
// Build query
this.command = this.connection.CreateCommand();
this.command.CommandText = "INSERT INTO table (foo) VALUES(@foo)";

// Query parameters
this.command.Parameters.Add("@foo", SqlDbType.VarChar).Value = "bar";

// Execute query
int rows = this.command.ExecuteNonQuery();

// Clear the parameters
this.command.Parameters.Clear();

// Create a new command to get the ID
this.command.CommandText = "SELECT scope_identity()";
int id = Convert.ToInt32(this.command.ExecuteScalar());
---- End ----

But when I run this code I get an error telling me that I cannot cast to
Int32 from DBNull.Value. Obviously this is correct, but why am I not
actually getting the value of the ID column returned by the latter
query?

That's because you are not in the scope of the insert command
any more.

Also, if I change "scope_identity()" to "@@identity" I get errors about
some of my parameters not being defined, when they clearly are. This
also occurs if I attempt to combine the INSERT and SELECT statements
into the one command.

scope_identity() is a way to go. Have you tried it in one batch with
the INSERT? Like:

command.CommandText = "INSERT dbo.MyTable (col) VALUES (1); SELECT SCOPE_IDENTITY()";

 

[Nicholas Paldino [.NET/C# MVP]]

This is part of the problem I have with using the database to insert
keys into tables. That's why I would rather use a GUID for an identifier,
and then create it myself.

It also helps when you are creating relations between tables and have to
pass that id along.

 

[Dylan Parry]

scope_identity() is a way to go. Have you tried it in one batch with
the INSERT? Like:

command.CommandText = "INSERT dbo.MyTable (col) VALUES (1); SELECT SCOPE_IDENTITY()";

Yes, I tried that before and it didn't work. So I tried it again, and
now it works fine. Perhaps I got so wound up by it that I couldn't work
out what I was doing wrong!