November 7, 2003 at 6:13 pm
Executing "Select @@IDENTITY as id" after an INSERT seems to return NULL.
I am using VBScript/ASP and have a VB function that inserts data into DB tables and another VB function that gets the @@IDENTITY which uses disconnected recordsets.
The following is an example:
'students_table has an id (PK - autogenerated)
sql = "insert into Students_Table (studentname) values ('John')"
exec_query (sql)
set rs = get_identity ("Select @@IDENTITY as id")
'rs("id") is NULL
Any clues?
November 7, 2003 at 6:26 pm
Absolutely. This is because, no matter how much it seems that it should work, each of the calls to the server is a separate process. To return the identity, you need to get @@IDENTITY in the same thread/process that executes the insert. This is where a Stored Procedure comes in handy:
Create Procedure usp_AddStudent
@Name varchar(20)
AS
insert into Students_Table (studentname) values (@Name)
Select @@IDENTITY
------------------
To call, you need a function to get an RS from a stored proc. You can genericize passing in parameters by building a Parameters collection for the ADO Command object, and using that as a parameter to the function.
You could, of course, also use a RETURN value, but you do need to do this in a singe SQL batch.
November 10, 2003 at 6:58 am
Actually, there is a better more fool-proof way besides "Select @@IDENTITY". The way @@IDENTITY works is that it will return the Last Identity Value entered for the ENTIRE Database that occurred during ANY Process.
What you need to do instead if you have a lot of updates happening in a short span of time is limit it to the table you are interested in. Try this instead:
Declare @ID Int
Set @ID = Ident_Current('MyTable')
Since "Students" is a Main entity in your Application, I would design a Stored Proc for Updates and Deletes against that Table. That way you are sure to always have the Identity working correctly and up-to-date as fast as possible.
For example, I would use the Recordset for Displaying the Data, but use an ADODB.Command Object for updates and Deletes. Then I would simply requery the Recordset to return all the most recent Changes.
Edited by - tymberwyld on 11/10/2003 07:00:32 AM
November 10, 2003 at 9:58 am
As a reply to brendthess, if I don't use disconnected recordsets it works!! Both functions are using the same connection.
tymberwyld, I don't have any updates. What I am doing is a very simple straight-forward single insert statement followed by getting @@IDENTITY. It became a problem only when I decided to use disconnected recordsets.
November 10, 2003 at 10:04 am
I speak of "updates" in a generalized manner. An update would be any Insert or Update statement.
With Disconnected Recordsets, I try to make it a general rule that during any Saving operations I will refresh the data, just in case other users have updated anything as well.
November 10, 2003 at 11:08 am
You could eliminate the 2nd query, by adding records to the recordset, then checking the identity column after the update:
Set rs = db.OpenRecordset("Select * from students_table WHERE 0=1"
rs.AddNew
rs("studentname") = "John"
rs.Update
rs.Filter = adFilterAffectedRecords
Msgbox "New ID is " & rs("id")
Mark
November 10, 2003 at 1:01 pm
IDENT_CURRENT returns the current identity value for the given table, regardless of scope or session. If you need to get the identity value you just inserted, you will want to use SCOPE_IDENTITY. This returns the last generated identity for the current session and scope.
November 10, 2003 at 1:20 pm
I always return the new ID as an output parameter:
CREATE PROCEEDURE sp_Insert_Student
(
@ID int = 0 OUTPUT, ---always give a default value
@StudentName varchar(30) --------or whatever length
)
insert into Students_Table (studentname) values (@StudentName)
SET @ID = @@IDENTITY ------------get new ID here
November 10, 2003 at 3:40 pm
tymberwyld, if Ident_Current('MyTable') is used, how would you know if the returned IDENTITY actually corresponds to your insert not any other user's insert?
Why @@IDENTITY is not returning what it's suppose to return!
November 10, 2003 at 4:52 pm
It is supposed to return the last identity value inserted across the database...
As per my experience, if you want to get the last inserted record's ID.. the SAFE way is like this.. even though there are several other methods described in this thread.....
INSERT INTO tbl1 (columns) VALUES(values)
SELECT idTbl1 from tbl1 where Column = value..
where we will check all the columns which make the row unique..
Linto
November 11, 2003 at 6:38 am
No, bdohmen is correct, use the "Scope_Identity" so that you're retrieving the ID from the Scope of your Application (i.e. Insert). I have been using Ident_Current('tablename') in my stored procs but I may change this. I'm thinking though that "Ident_Current" is simply "Select Max(ID) From Table".
I never using AutoNumber fields in any database I design because they cause the most problems when I write Synchronize programs. Most of the tables I have that have a numeric ID field I will simply Calculate myself in the Client App (Select Max(ID) + 1 As NewID From Table).
November 11, 2003 at 9:24 am
MaxID + 1 is tricky if you have concurrent users getting a key at the same time. This is really all the ident does, it just manages concurrency for you.
Andy
November 11, 2003 at 9:28 am
Yes, although any table I have with a numeric Key is only a lookup table and gets modified once every...couple years? =P
November 12, 2003 at 9:49 am
Thanks guys for all the help. I am going to try some of the ideas presented.
November 13, 2003 at 9:10 am
In ASP I think this can be handled by firing both the queries togather i.e.
sql = "insert into Students_Table (studentname) values ('John') Select @@IDENTITY as id".
You can access the value of id by nextrecordset property of recordset object.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply