empty @@identity (sql server 2000)

P

Phil W

Hi all,

Am having a bit of trouble with the @@identity field - I probably just have
that friday feeling and am missing off something obvious, but the below code
brings back am empty identity value ("sid" appears empty). I've definitely
set up an identity field in the tblSurvey:

set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.open "tblSurvey", conn, 3 , 3
rsAdd.AddNew
rsAdd("title") = title
rsAdd("intro") = intro
rsAdd("enddate") = enddate
rsAdd.Update

Set rsIdentity = Conn.Execute("SELECT @@IDENTITY AS sid")
sid = rsIdentity("sid")

....any ideas would be greatly appreciated.

TIA,
Phil
 
A

Aaron Bertrand - MVP

Why are you using a recordset and AddNew? Why are you using two separate
recordset objects? Why are you using @@IDENTITY, which is not as reliable
as SCOPE_IDENTITY()? Have you considered using a stored procedure?

Set rs_identity = conn.execute("SET NOCOUNT ON; INSERT ... ; SELECT
SCOPE_IDENTITY()"
response.write rs(0)
 
P

Phil W

Aaron Bertrand - MVP said:
Why are you using a recordset and AddNew? Why are you using two separate
recordset objects? Why are you using @@IDENTITY, which is not as reliable
as SCOPE_IDENTITY()? Have you considered using a stored procedure?

Set rs_identity = conn.execute("SET NOCOUNT ON; INSERT ... ; SELECT
SCOPE_IDENTITY()"
response.write rs(0)

Yes I'll most probably use a sproc. Oh, and @@identity is just habit after
using SQL97 for so long.

re. "why are you using a recordset and addnew" - is this really such a
terrible thing? I tend to find code a lot easier to read through (esp when
there's a lot of variables) than when using an INSERT. Also gets around
having to do so much validation on submitted values (with apostrphes, rogue
sql etc). btw I'm not trying to argue here, just genuinely interested in
your thoughts.
 
A

Aaron Bertrand - MVP

re. "why are you using a recordset and addnew" - is this really such a
terrible thing?

Yes! You lock the whole table to add a row. http://www.aspfaq.com/2191
I tend to find code a lot easier to read through (esp when
there's a lot of variables) than when using an INSERT.

You can easily structure INSERT code to be just as easy to read... if you
are planning to use a stored procedure (which you should!) this is not as
much of an issue; if you use ADODB.Command, you simply change out rsAdd for
cmd.Parameters.Add() (though it's a little more convoluted syntax).
Also gets around having to do so much validation on submitted values (with apostrphes, rogue
sql etc). btw I'm not trying to argue here, just genuinely interested in
your thoughts.

You can lump apostrophes and rogue sql together as one line item, I think.
Replacing all incoming strings with double apostrophes eliminates all sql
injection attacks I'm familiar with. Again, if you are using a stored
procedure and ADODB.Command.parameters, you don't have to worry about this.
Just please, please, please stop using AddNew.

A
 
P

Phil W

Aaron Bertrand - MVP said:
Yes! You lock the whole table to add a row. http://www.aspfaq.com/2191


You can easily structure INSERT code to be just as easy to read... if you
are planning to use a stored procedure (which you should!) this is not as
much of an issue; if you use ADODB.Command, you simply change out rsAdd for
cmd.Parameters.Add() (though it's a little more convoluted syntax).
(with
apostrphes, rogue

You can lump apostrophes and rogue sql together as one line item, I think.
Replacing all incoming strings with double apostrophes eliminates all sql
injection attacks I'm familiar with. Again, if you are using a stored
procedure and ADODB.Command.parameters, you don't have to worry about this.
Just please, please, please stop using AddNew.

A

Thanks for all the info Aaron - I shall ditch AddNew for good!
 
B

Bob Lehmann

I'm not condoning your method of adding a record this way - See Aaron's post
for the proper way - but, you can get the identity after doing your update
with..

sid = rsAdd("sid")

Bob Lehmann
 
P

Phil W

Bob Lehmann said:
I'm not condoning your method of adding a record this way - See Aaron's post
for the proper way - but, you can get the identity after doing your update
with..

sid = rsAdd("sid")

Bob Lehmann

Don't worry Bob - I've seen the error of my ways with AddNew ;-)

However I must just say that although the above method of yours works
perfectly with an Access database - I've had difficulty getting it working
with SQL Server.
 
A

Aaron Bertrand - MVP

However I must just say that although the above method of yours works
perfectly with an Access database - I've had difficulty getting it working
with SQL Server.

I mentioned the problem... you create a new recordset (set rs =
conn.execute("SELECT @@IDENTITY")). This is going to be null, because SQL
Server sees this as a new session and has no idea that you're the same user
that added a row to the table using AddNew.

A
 
T

TomB

I thought it was based on the connection rather than the recordset. So as
long as conn hasn't been closed and re-opened, it was the same session.

no?

Tom B
 
A

Aaron Bertrand - MVP

I thought it was based on the connection rather than the recordset. So as
long as conn hasn't been closed and re-opened, it was the same session.

I don't believe so. I think if you watch sp_who2 you might get misleading
results... since you might see the same spid re-used, you might see a new
one.

A
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
474,148
Messages
2,570,838
Members
47,385
Latest member
Joneswilliam01

Latest Threads

Top