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

--
Swanand Mokashi
Microsoft Certified Professional
[url]http://www.swanandmokashi.com/[/url]
Home of the Stock Quotes, Quote of the day and Horoscope web services
"Micheal" <frooyo@sbcglobal.net> wrote in message
news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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();
>
>

[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:
[url]www.aboutfortunate.com[/url]

"Out of chaos comes order."
Nietzche
"Micheal" <frooyo@sbcglobal.net> wrote in message
news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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]

yes executescalar is a even better option

--
Swanand Mokashi
Microsoft Certified Professional
[url]http://www.swanandmokashi.com/[/url]
Home of the Stock Quotes, Quote of the day and Horoscope web services
"S. Justin Gengo" <sjgengo@aboutfortunate.com> wrote in message
news:uMe3HEuUDHA.736@TK2MSFTNGP09.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.CommandText = strSQL
> IdReturned = SQLCommand.ExecuteScalar
>
> I hope this helps.
> --
> S. Justin Gengo, MCP
> Web Developer
>
> Free code library at:
> [url]www.aboutfortunate.com[/url]
>
> "Out of chaos comes order."
> Nietzche
> "Micheal" <frooyo@sbcglobal.net> wrote in message
> news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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();
> >
> >

>
>

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






"Micheal" <frooyo@sbcglobal.net> wrote in message
news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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();
>
>

[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:
[url]www.aboutfortunate.com[/url]

"Out of chaos comes order."
Nietzche
"Micheal" <frooyo@sbcglobal.net> wrote in message
news:%23ZC9YZuUDHA.1956@TK2MSFTNGP10.phx.gbl...

> 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();
> --------------------------------------------------------
>
>
>
>
>
>
> "Micheal" <frooyo@sbcglobal.net> wrote in message
> news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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]

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();

--
Swanand Mokashi
Microsoft Certified Professional
[url]http://www.swanandmokashi.com/[/url]
Home of the Stock Quotes, Quote of the day and Horoscope web services
"Micheal" <frooyo@sbcglobal.net> wrote in message
news:%23ZC9YZuUDHA.1956@TK2MSFTNGP10.phx.gbl...

> 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();
> --------------------------------------------------------
>
>
>
>
>
>
> "Micheal" <frooyo@sbcglobal.net> wrote in message
> news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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();
> >
> >

>
>

[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" <frooyo@sbcglobal.net> wrote in message
news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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();
>
>

[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" <sjgengo@aboutfortunate.com> wrote in message
news:OPUtF5uUDHA.2004@TK2MSFTNGP11.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:
> [url]www.aboutfortunate.com[/url]
>
> "Out of chaos comes order."
> Nietzche
> "Micheal" <frooyo@sbcglobal.net> wrote in message
> news:%23ZC9YZuUDHA.1956@TK2MSFTNGP10.phx.gbl...

> > 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();
> > --------------------------------------------------------
> >
> >
> >
> >
> >
> >
> > "Micheal" <frooyo@sbcglobal.net> wrote in message
> > news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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();
> > >
> > >

> >
> >

>
>

[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:
[url]www.aboutfortunate.com[/url]

"Out of chaos comes order."
Nietzche
"Micheal" <frooyo@sbcglobal.net> wrote in message
news:OA4qRJrVDHA.3404@tk2msftngp13.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.ExecuteScalar();
> ----------------------------------------------------------
>
>
>
>
>
> "S. Justin Gengo" <sjgengo@aboutfortunate.com> wrote in message
> news:OPUtF5uUDHA.2004@TK2MSFTNGP11.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:
> > [url]www.aboutfortunate.com[/url]
> >
> > "Out of chaos comes order."
> > Nietzche
> > "Micheal" <frooyo@sbcglobal.net> wrote in message
> > news:%23ZC9YZuUDHA.1956@TK2MSFTNGP10.phx.gbl...

> > > 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();
> > > --------------------------------------------------------
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Micheal" <frooyo@sbcglobal.net> wrote in message
> > > news:%23KbRy7tUDHA.2184@TK2MSFTNGP10.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.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();
> > > >
> > > >
> > >
> > >

> >
> >

>
>