SELECT SCOPE_IDENTITY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SELECT SCOPE_IDENTITY

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2010-08-03 : 19:13:08

Hi,

How to get ID?

My table:
UserId
CardId - Primarykey - autoincrement
AttibuteName

Id save the CardId. Why will not get my ID, which I have saved?
Using SCORE_IDENTITY

My code:

string queryString = "SELECT SCOPE_IDENTITY()";

SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();

command.CommandText = "SELECT SCOPE_IDENTITY()";
object o = command.ExecuteScalar();
// System.Diagnostics.Debug.WriteLine("SCOPE_IDENTIY TYPE: " + o.GetType().FullName);
// System.Diagnostics.Debug.WriteLine("SCOPE_IDENTIY VALUE: " + o.ToString());

Label3.Text = ("SCOPE_IDENTIY VALUE: " + o.ToString());

if (o != DBNull.Value)
{
// Do something }
}
}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-03 : 19:23:07

You have to run your INSERT statement right before you get it. It seems you are opening a new connection to get SCOPE_IDENTITY(), so you can't get the id value inserted. Use the same connection and grab the id directly after the INSERT command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

programer
Posting Yak Master

221 Posts

Posted - 2010-08-03 : 19:39:31

quote:
Originally posted by tkizer

You have to run your INSERT statement right before you get it. It seems you are opening a new connection to get SCOPE_IDENTITY(), so you can't get the id value inserted. Use the same connection and grab the id directly after the INSERT command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

You mean like this:
InsertCommand = "INSERT INTO [tbl_paymentDetails] ([UserId], [Value], [AttributeName] [CardID]) VALUES (@ UserId, @ Value, @ AttributeName, @ CardId) SELECT SCOPE_IDENTITY ()"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 10:47:55

I don't think it can be done in the same statement, I believe you need to different queries in your application. If you could switch to a stored procedure, I could easily help you out! Application code is not my specialty.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-04 : 11:29:02

If you're INSERTing to a SQL Server 2005 or higher database, use the OUTPUT clause of the INSERT command:

INSERT INTO [tbl_paymentDetails] ([UserId], [Value], [AttributeName]) 
OUTPUT inserted.IDENTITYCOL AS CardID 
VALUES (@UserId, @Value, @AttributeName)

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 11:56:13

quote:
Originally posted by robvolk

If you're INSERTing to a SQL Server 2005 or higher database, use the OUTPUT clause of the INSERT command:
INSERT INTO [tbl_paymentDetails] ([UserId], [Value], [AttributeName])
OUTPUT inserted.IDENTITYCOL AS CardID
VALUES (@UserId, @Value, @AttributeName)

thanks this is work:
InsertCommand="INSERT INTO [tbl_paymentDetails] ([UserId], [Value],[CardId], [AttributeName]) OUTPUT inserted.CardId AS CardId VALUES (@UserId, @Value, @AttributeName,@CardId)"

I wonder how the last stored record show (CardId)?
thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-04 : 12:40:00

Your column list is incorrect:

InsertCommand="INSERT INTO [tbl_paymentDetails] ([UserId], [Value],[CardId], [AttributeName])
OUTPUT inserted.CardId AS CardId VALUES (@UserId, @Value, @AttributeName,@CardId)"

Should be:

InsertCommand="INSERT INTO [tbl_paymentDetails] ([UserId], [Value], [AttributeName],[CardId])
OUTPUT inserted.CardId AS CardId VALUES (@UserId, @Value, @AttributeName,@CardId)"

And why are you supplying a @CardID parameter when you want to retrieve the auto-generated CardID?

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 12:43:56

quote:
Originally posted by robvolk

Your column list is incorrect:

InsertCommand="INSERT INTO [tbl_paymentDetails] ([UserId], [Value],[CardId], [AttributeName])
OUTPUT inserted.CardId AS CardId VALUES (@UserId, @Value, @AttributeName,@CardId)"

Should be:

InsertCommand="INSERT INTO [tbl_paymentDetails] ([UserId], [Value], [AttributeName],[CardId])
OUTPUT inserted.CardId AS CardId VALUES (@UserId, @Value, @AttributeName,@CardId)"

And why are you supplying a @CardID parameter when you want to retrieve the auto-generated CardID?

Because the record CardId need to save data in another table.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-04 : 12:52:03

You won't get the auto-generated CardID in the variable, it will be returned as a result. Passing it to the VALUES clause indicates you want to INSERT that value into the table, not return the value from it.

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 13:07:19

"INSERT INTO MyTable (Col1, Col2, ...) VALUES (@Param1, @param2, ...) SELECT SCOPE_IDENTITY()"

I reckon that should be fine.

Not sure about having a blank space between "@" and "Name", or space between "SCOPE_IDENTITY" and "()" though ...

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 16:14:10

quote:
Originally posted by Kristen

"INSERT INTO MyTable (Col1, Col2, ...) VALUES (@Param1, @param2, ...) SELECT SCOPE_IDENTITY()"

I reckon that should be fine.

Not sure about having a blank space between "@" and "Name", or space between "SCOPE_IDENTITY" and "()" though ...

This is my code: InsertCommand="INSERT INTO [tbl_payments] ([UserId], [PaymentType]) VALUES (@UserId, @PaymentType);SELECT SCOPE_IDENTITY()"

Works!

Now I want to know:

Label1.Text - show how the last recorded information?

Please help

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 16:28:14

SELECT SCOPE_IDENTITY() is returned as a resultset, so you need to process it as a resultset

I don't know anything about DotNet, but my understanding is that you cannot use

command.ExecuteNonQuery()

for this type of query, because it IS a Query and returns a ResultSet

You may need to do:

SET NOCOUNT ON INSERT INTO [tbl_payments] ([UserId], [PaymentType]) VALUES (@UserId, @PaymentType);SELECT SCOPE_IDENTITY()

in order not to get an informational message after the INSERT statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources