Select ID After INSERT INTO - 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

General SQL Server Forums

New to SQL Server Programming

Select ID After INSERT INTO

Author

Topic

mellamokb
Starting Member

39 Posts

Posted - 2006-01-10 : 17:42:49

Hi,

I have a table consisting of a primary key (ID) and some fields. I want to (from an ASP page) insert some data entered by a user from the table, and then retrieve the ID from the record that was just inserted. Then that ID will be used in another table to tie the two tables together. How do I write the SQL to do this?

thanks,
mellamokb

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-10 : 17:49:09

use the scope_identity() function

something like this


declare @id = int
select @id = scope_identity()

Read up books online

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

Its best to use scope_identity() , because, it returns values inserted only within the current scope

mellamokb
Starting Member

39 Posts

Posted - 2006-01-10 : 18:10:12

OK, so what do I do with that? All I get are errors!

Here's what I tried first:

command.commandText = "INSERT INTO table VALUES (value1, value2, value3) SELECT SCOPE_IDENTITY() AS ID"

When I execute the command in ASP, I get an error telling me that I'm missing a semicolon. Adding a semicolon does no good. I tried this as well (from Microsoft):

command.commandText = "SET NOCOUNT ON; INSERT INTO table VALUES (value1, value2, value3); SELECT SCOPE_IDENTITY() AS ID"

I get an error indicating that 'SET NOCOUNT ON' is not a recognized statement. When I create a command containing only "SELECT SCOPE_IDENTITY() AS ID" and nothing else, I get an error stating that the function "SCOPE_IDENTITY" is unknown. I don't know AT ALL what I'm doing here. Could you please tell me how I would write the commandText (vbscript) like above so that the SQL code would retrieve the identity value after inserting data?

BTW, I hope you realize that I'm using a ADODB.Command object on an ASP page and giving it SQL, in case that makes any difference, whereas all I see online is information about something called T-SQL which is probably different

... But then I'm not even sure what I'm talking about.

thanks,
mellamokb

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-10 : 18:28:39

no, no, no

Just a minute

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-10 : 18:42:59

Have you ever heard of using stored proceedures ?

It works better. Basically you call a SP from your ASP page as thus

Command.CommandText = "dbo.Your_Stored_Proceedures_name"

Basically, your SP does all the work


create  PROCEDURE [dbo].[whatever]
	(@user varchar (20), --Other parameters would be here)


as 
declare @id = int
select @id = scope_identity()


return @id

then in your asp page, you read the value of @id


after the command.execute statement put below

....
Command.CommandText = "dbo.whatever"
Command.CommandType = 4
Command.CommandTimeout = 0
Command.Prepared = true
Command.Parameters.Append Command1.CreateParameter("@RETURN", 3, 4)
Command.Execute()

results = Command.Parameters.Item("@RETURN").Value

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-10 : 18:47:03

Do you understand ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 00:29:55

>>INSERT INTO table VALUES (value1, value2, value3) SELECT SCOPE_IDENTITY() AS ID

What are you trying to do?
You didnt give values for value2,value3
If value1 is identity column, then omit it from list and write

INSERT INTO table (value2, value3) VALUES someval, someval

Also
select ident_current('tableName')
will tell you the maximum identity value if any

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-12 : 02:18:16

quote:
Originally posted by madhivanan

Also
select ident_current('tableName')
will tell you the maximum identity value if any

Madhivanan

Failing to plan is Planning to fail

ident_current does not work within the scope of the transaction. So if you have multiple transactions, it might give you an error

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

mellamokb
Starting Member

39 Posts

Posted - 2006-01-13 : 14:47:18

Hi,

Sorry I haven't responded in awhile - i've been busy with finals. I've used stored procedures, and the project I'm working on has over 50 stored procedures with it. But the other programmer at our business doesn't have access to the SQL Server b/c I work on the server computer. He is working on another internet project, and he's using ASP just like I, but on a different computer, so he saves everything to the computer I work on. Rather than messing with another entire database just for a couple (like 3 or 4) of stored procedures that I'd have to maintain, build and work with while I have my own deadlines for my own project, I'd like him to use SQL code entered into ADODB.command objects. I understand now you're referring to stored procedures, but is there a way to retrieve the identity from just SQL code sent by command objects? My buddy wants to compare the information by a different field than the identity field, but I want him to use the identity field in case there would be duplicates, and the easiest way is just through a command. I hope you kinda understand what I'm talking about... If I HAVE to use stored procedures, I will, but it's just another hassle right now.

Another thing, there could be any number of people using this site at once. If I used the scope identity you refer to, will it ever allow the server to update a record from a second person using the page before it finishes updating with the first person, i.e., the ID used is changed by the second person before the first person's request to retrieve the ID is carried out? Then the ID's would be messed up. If there is any other information I need to give you in order to determine what I can do for a solution, please let me know :).

thanks very much for your replies!
mellamokb

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-13 : 16:17:02

quote:
Originally posted by mellamokb

Hi,
I understand now you're referring to stored procedures, but is there a way to retrieve the identity from just SQL code sent by command objects?

Another thing, there could be any number of people using this site at once. If I used the scope identity you refer to, will it ever allow the server to update a record from a second person using the page before it finishes updating with the first person, i.e., the ID used is changed by the second person before the first person's request to retrieve the ID is carried out? Then the ID's would be messed up. If there is any other information I need to give you in order to determine what I can do for a solution, please let me know :).

thanks very much for your replies!
mellamokb

I dont know of any way, using the command object

You need to understand "TRANSACTIONS", when you run a transaction in a SP no matter how many people are updating, deleting, inserting etc you are working with a scope/session, which is either successful or failed.

Thats where the scope_identity funciton comes into play, with the commands directly, you are not working within a scope

hope u do understand

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-13 : 16:38:35

If the table has a natural key, meaning a combination of columns that uniquely identify the row, you could query by those columns to get the ID.

As an alternative, it would require a change to the table, but you could have the front end supply a uniqueidentifier (GUID), and then use that to query the table to get the identity.

Either way, it would be better to use a stored procedure, because it would require only one trip to the database.

CODO ERGO SUM

mellamokb
Starting Member

39 Posts

Posted - 2006-01-13 : 18:21:09

Hi,

I finally went ahead and created a new database and made a stored procedure :). I agree with you that it is the best way, even thought it has taken a lot of work to build it and I had to take some time away from my project to help my buddy on his. I tried out the ID retrieving and it works great! Thank you very much for your help.

thanks,
mellamokb

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-13 : 18:40:30

Glad to know it worked well.

To be candid, SP (Seperating your application logic to multiple tiers ) is the way to go.

Afrika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources