473,225 Members | 1,369 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,225 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 2276
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: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.