473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2279
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ken Lindner | last post by:
I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
5
by: Philippa | last post by:
I'm trying to access data in vba using the openrecordset command. The data in on a SQL Server 2000 database, and I have linked tables to that data. the Table I'm trying to access is one of these...
6
by: mcollier | last post by:
I am running a Windows Server 2003 machine as my web server. I would like to use Windows authentication for connections to my SQL Server 2000 instance on a Windows 2000 server. I've read where...
4
by: Vince Varallo | last post by:
Hello, I do not have SQL Server 2005 installed on my computer and will not be able to based on company policy. However I do have SQL Server 2000. I want to create the application service...
3
by: Ann Marinas | last post by:
Hi there, I am currently developing an ASP.NET program that connects to a SQL Server 2000 database. I also have SQL Server 2005 Express installed on the same local machine. Prior to...
0
by: PaulOak | last post by:
Hi all, I have the following problem: 1. web site (asp.net 2.0 C#). 2. WebPartZone, which contains one user control. 3. SQL Server 2000 running on dedicated DB server, hosting the aspnetdb...
3
by: --[zainy]-- | last post by:
AA! I am having problems on creating table through Enterprise Manager. It gives me Error 1038 i.e. is as follows Unexpected Error ODBC error: Cannot use empty object or column names. Use...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.