C# Database @@identity

[Micheal]

How do I get the @@identity value without using stored procedures in C#?

I have the a table named 'sports' that has an @@identity value for the
primary key.

What I want to do is after I insert into into the 'sports' table, I want to
get the @@identity value (primary key) for what I just inserted.

I have to following code if this helps, thanks in advance:

---------------------------------------------------------

string sql = "insert into sports (name.......";

conn.Open();

// insert record into the database
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader result = cmd.ExecuteReader();
result.Close();

// ??? GET THE IDENTITY OF WHAT WAS JUST INSERTED ???
SqlCommand ident = new SqlCommand("select @@identity from sports", conn);
SqlDataReader ident_result = ident.ExecuteReader();

int sport_id = -1;
if (ident_result.Read()) { ident_result.GetDecimal(0); }

ident_result.Close();
conn.Dispose();

 

[Swanand Mokashi]

rather use :
string sql = "insert into sports (name.......);SELECT @@IDENTITY;"
Now use your SqlDataReader result = to getvalue of the first column which is
the new identity

 

[S. Justin Gengo]

Micheal,

It will work if you just do the select @@identity within your insert
statement.

Then use ExecuteScalar to get it out.

Like this:

strSQL = "INSERT INTO sports (name) VALUES (@name);SELECT @@Identity"
SQLCommand.CommandText = strSQL
IdReturned = SQLCommand.ExecuteScalar

I hope this helps.
--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche

 

[Swanand Mokashi]

yes executescalar is a even better option

 

[Micheal]

Hmm....

I am doing the following (see below) and it does __not__ work? It error's
at cmd.ExecuteScalar() stating:
"cannot implicity convert type 'object' to SqlDataReader"

Any ideas?

-------------------------------------------------
string sql = "insert into sports (name) value ('test'); select @@identity";

conn.Open();

// insert record into the database
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader result = cmd.ExecuteScalar();

int sport_id = -1;
if (result.Read()) { sport_id = result.GetDecimal(0); }

result.Close();
conn.Dispose();
--------------------------------------------------------

 

[S. Justin Gengo]

Micheal,

The ExecuteScalar method returns a single value. Instead of a datareader it
is returning the id itself you just need to declare an integer variable and
set it equal to the execute scalar line like I showed in my example.

It returns as a type object so you'll have to cast it as int.


--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche

 

[Swanand Mokashi]

ExecuteScalar returns the 1st column of the 1st row of your query result.
This is by default returned as type object
use :
int sport_id = (int) cmd.ExecuteScalar();

 

[Neil Ramsbottom]

Just for the record (I know that people have pointed you in the right
direction), but the code you posted wouldn't have worked because the
@@identity would only be in scope for that transaction.

Given that no other statements were executed prior to the select (in
particular, nothing that affects an identity field) - @@identity would'nt
return a value... actually it might return 0, but I could be wrong.



Neil Ramsbottom

 

[Micheal]

The below does _not_ work! The datatype for the autoincrement value is INT
in SQL Server 2000. The strange thing is that if I use a 'decimal' it works
(using .NET framework 1.1)

---------------------------------------------------------
string sql = "insert into sports (name) value ('test'); select
@@identity";

conn.Open();

// insert record into the database
SqlCommand cmd = new SqlCommand(sql, conn);
int result = (int)cmd.ExecuteScalar();

 

[S. Justin Gengo]

Interesting!

Well, I mostly program in vb.net and only a little in C#. It's good to know
about this difference.

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche