469,126 Members | 1,326 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

empty @@identity (sql server 2000)

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
Jul 19 '05 #1
9 2136
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)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Phil W" <yo******************@hotmail.com> wrote in message
news:40********@mk-nntp-2.news.uk.tiscali.com...
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

Jul 19 '05 #2
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:e1**************@TK2MSFTNGP11.phx.gbl...
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.
Jul 19 '05 #3
> 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
Jul 19 '05 #4
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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


Thanks for all the info Aaron - I shall ditch AddNew for good!
Jul 19 '05 #5
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

"Phil W" <yo******************@hotmail.com> wrote in message
news:40********@mk-nntp-2.news.uk.tiscali.com...
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

Jul 19 '05 #6

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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.
Jul 19 '05 #7
> 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
Jul 19 '05 #8
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
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:ev**************@tk2msftngp13.phx.gbl...
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

Jul 19 '05 #9
> 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
Jul 19 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ann Marinas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.