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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[Swanand Mokashi]

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

--
Swanand Mokashi
Microsoft Certified Professional
http://www.swanandmokashi.com/
Home of the Stock Quotes, Quote of the day and Horoscope web services
"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[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.Comm andText = strSQL
IdReturned = SQLCommand.Exec uteScalar

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

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[Swanand Mokashi]

yes executescalar is a even better option

--
Swanand Mokashi
Microsoft Certified Professional
http://www.swanandmokashi.com/
Home of the Stock Quotes, Quote of the day and Horoscope web services
"S. Justin Gengo" <sj*****@aboutf ortunate.com> wrote in message
news:uM******** *****@TK2MSFTNG P09.phx.gbl...

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.Comm andText = strSQL
IdReturned = SQLCommand.Exec uteScalar

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

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[Micheal]

Hmm....

I am doing the following (see below) and it does __not__ work? It error's
at cmd.ExecuteScal ar() 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.ExecuteScal ar();

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

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


"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
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
"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Hmm....

I am doing the following (see below) and it does __not__ work? It error's
at cmd.ExecuteScal ar() 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.ExecuteScal ar();

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

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


"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[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.ExecuteScal ar();

--
Swanand Mokashi
Microsoft Certified Professional
http://www.swanandmokashi.com/
Home of the Stock Quotes, Quote of the day and Horoscope web services
"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Hmm....

I am doing the following (see below) and it does __not__ work? It error's
at cmd.ExecuteScal ar() 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.ExecuteScal ar();

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

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


"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[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" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

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

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[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.Execut eScalar();
----------------------------------------------------------

"S. Justin Gengo" <sj*****@aboutf ortunate.com> wrote in message
news:OP******** ******@TK2MSFTN GP11.phx.gbl...

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
"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Hmm....

I am doing the following (see below) and it does __not__ work? It error's

at cmd.ExecuteScal ar() 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.ExecuteScal ar();

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

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


"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

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.ExecuteRead er();
result.Close();

// ??? GET THE IDENTITY OF WHAT WAS JUST INSERTED ???
SqlCommand ident = new SqlCommand("sel ect @@identity from sports",

conn); SqlDataReader ident_result = ident.ExecuteRe ader();

int sport_id = -1;
if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }

ident_result.Cl ose();
conn.Dispose();

[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
"Micheal" <fr****@sbcglob al.net> wrote in message
news:OA******** ******@tk2msftn gp13.phx.gbl...

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.Execut eScalar();
----------------------------------------------------------

"S. Justin Gengo" <sj*****@aboutf ortunate.com> wrote in message
news:OP******** ******@TK2MSFTN GP11.phx.gbl...

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
"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Hmm....

I am doing the following (see below) and it does __not__ work? It error's at cmd.ExecuteScal ar() 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.ExecuteScal ar();

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

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


"Micheal" <fr****@sbcglob al.net> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..
> 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.ExecuteRead er();
> result.Close();
>
> // ??? GET THE IDENTITY OF WHAT WAS JUST INSERTED ???
> SqlCommand ident = new SqlCommand("sel ect @@identity from sports",

conn);

> SqlDataReader ident_result = ident.ExecuteRe ader();
>
> int sport_id = -1;
> if (ident_result.R ead()) { ident_result.Ge tDecimal(0); }
>
> ident_result.Cl ose();
> conn.Dispose();
>
>