473,320 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

C# Database @@identity

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();
Nov 17 '05 #1
9 20110
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
http://www.swanandmokashi.com/
Home of the Stock Quotes, Quote of the day and Horoscope web services
"Micheal" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();

Nov 17 '05 #2
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
"Micheal" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();

Nov 17 '05 #3
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*****@aboutfortunate.com> wrote in message
news:uM*************@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:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"Micheal" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();


Nov 17 '05 #4
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" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();

Nov 17 '05 #5
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****@sbcglobal.net> wrote in message
news:%2****************@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" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();


Nov 17 '05 #6
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
http://www.swanandmokashi.com/
Home of the Stock Quotes, Quote of the day and Horoscope web services
"Micheal" <fr****@sbcglobal.net> wrote in message
news:%2****************@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" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();


Nov 17 '05 #7

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****@sbcglobal.net> wrote in message
news:%2****************@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();

Nov 17 '05 #8
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" <sj*****@aboutfortunate.com> wrote in message
news:OP**************@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:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"Micheal" <fr****@sbcglobal.net> wrote in message
news:%2****************@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" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();



Nov 17 '05 #9
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****@sbcglobal.net> wrote in message
news:OA**************@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" <sj*****@aboutfortunate.com> wrote in message
news:OP**************@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:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"Micheal" <fr****@sbcglobal.net> wrote in message
news:%2****************@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" <fr****@sbcglobal.net> wrote in message
news:%2****************@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();
>
>



Nov 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Devesh Aggarwal | last post by:
Hi, I have a backup and restore module in my project. The backup uses a typed dataset object (XSD) to get the data from database and creates a xml file as the backup file (using the WriteXml...
4
by: Nanchil | last post by:
Hi, We created a database (DB2 UDB 7.2 on solaris 8) without this (COLLATE USING IDENTITY ) option. But we need this now for binary sorting. The database is siebel database. Is it possible to drop...
2
by: WhiteEagl | last post by:
Hello, I would need some help with this identity column problem in SQLServer. I have a database with two tables. The Parent table has an Identity column. Parent (ParentID IDENTITY, Name)...
1
by: dusty | last post by:
Hi, I'll try to simplify the problem: I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work...
3
by: dusty | last post by:
Hi, I'll try to simplify the problem: I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work...
3
by: Wm. Scott Miller | last post by:
What is the difference between using a username and password in the processmodel section vs using one in impersonation in the machine.config file? What are the advantages of each and what are the...
8
by: Razak | last post by:
Hi, I have a class which basically do Impersonation in my web application. From MS KB sample:- ++++++++++++++++++++code starts Dim impersonationContext As...
3
by: Rob | last post by:
Hi all, I have a bit of a complicated question, hope we have an SQL guru out there that can help us solve this killer problem. Due to the size of SQL Database we have (largest in the US), we...
11
by: stegze | last post by:
Hi All, I have a problem with a DB2 server of my customer. It is a Debian Linux running DB2 Express-C. I have an IDENTITY field as PK in a table and I use this value as FK in another table. Two...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.