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(); 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();
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();
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();
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();
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();
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();
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();
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();
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(); > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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)...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |