|
 |
asp_databases thread: RE: @@IDENTITY not always returning the id?
Message #1 by "Owen Mortensen" <ojm@a...> on Fri, 5 Apr 2002 12:55:41 -0700
|
|
OUCH! The answer has been staring me in the face the whole time!
The @@IDENTITY statement DOES NOT include the table name.
So, instead of:
SELECT @@IDENTITY AS id FROM link_contact
It should be:
SELECT @@IDENTITY AS id
Owen
-----Original Message-----
From: Owen Mortensen [mailto:ojm@a...]
Sent: Friday, April 05, 2002 11:33 AM
To: ASP Databases
Subject: [asp_databases] @@IDENTITY not always returning the id?
Here's some code:
<%Option Explicit%>
<!-- #INCLUDE VIRTUAL = "/functions/dbutils.asp" -->
<%
dim objComm
dim rs
dim strContactEmail
dim contactRS, rsID
dim intLinkID
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = sConnectDB
objComm.CommandType = adCmdText
dim strSQL
dim contactID
strSQL = "SELECT * FROM Links ORDER BY headline"
set rs = getdbrecordset(strSQL)
rs.movefirst
do while not rs.eof
'Set the variables from SQL call
strContactEmail = rs("contact_email")
strContactEmail = replace(strContactEmail, "'", "''")
'Determine if the contact already exists
strSQL = "SELECT contactID, ContactEmail FROM link_contact WHERE
ContactEmail = '" & strContactEmail & "'"
set contactRS = getDBrecordset(strSQL)
if (contactRS.bof and contactRS.eof) then
'********************************* link_contact update
*******************************
objComm.CommandText = "INSERT INTO link_contact
(ContactEmail) VALUES ('" & strContactEmail & "')"
objComm.Execute
Response.Write "Inserted: " & strContactEmail & "<br>" & vbcrlf
contactRS.Close
'Get identity from last update
objComm.CommandText = "SELECT @@IDENTITY AS id FROM
link_contact"
set rsID = objComm.Execute
contactID = rsID("id")
Response.write "ID: " & contactID & "<br>" & vbcrlf
'***********************************************************************
****************
end if
rs.movenext
loop
rs.close
contactRS.close
set rs = nothing
set contactRS = nothing
Response.Write "DB update successful"
%>
This is what I get:
Inserted: synrg@c...
ID: 1
Inserted: brownp@o...
ID: 2
Inserted: kullnig@d...
ID: 3
Inserted:
ID: 4
Inserted: radman697@a...
ID: 5
Inserted: dhovelson@a...
ID: 6
Inserted: ellenhillman@y...
ID: 7
Inserted: sedazenger@h...
ID: 8
Inserted: osmo.rasanen@k...
ID: 9
Inserted: tabruzz@e...
ID: 10
Inserted: rew4050@a...
ID: 11
Inserted: cinnamonlove38@h...
ID:
Inserted: jfnaouri@i...
ID:
Inserted: rschneider@a...
ID: 14
Inserted: nouirakais@v...
ID: 15
Inserted: ramdave@i...
ID: 16
Inserted: partridg@g...
ID: 17
Inserted: suphott@h...
ID: 18
Inserted: roabdi@h...
ID:
Inserted: esoll@h...
ID:
Inserted: KelyGrl75@a...
ID:
Inserted: hsbajwa@h...
ID:
Inserted: alihayder77@h...
ID:
Inserted: ben@s...
ID:
Inserted: rsthak@y...
ID:
Inserted: helga.roedler@k...
ID:
Inserted: radio1@i...
ID:
Inserted: tbouit@y...
ID:
Inserted: Laurent.Trouillard@w...
ID:
Inserted: bahattinh@h...
ID:
Inserted: cmhdr@h...
ID:
Inserted: wc_lin@v...
ID:
Inserted: puchatek30@p...
ID:
Inserted: ch.aldea@t...
ID:
Inserted: tk.oei@r...
ID:
Inserted: kathy.pavlis@m...
ID:
Inserted: pertaangelo@i...
ID:
Inserted: bcasey@a...
ID: 38
Inserted: ajmedical@a...
ID:
Inserted: film@a...
ID:
Inserted: albiin@y...
ID: 41
Inserted: support@A...
ID: 42
Inserted: kdevereaux@m...
ID: 43
Inserted: supple@m...
ID: 44
Inserted: lcarter@d...
ID: 45
Inserted: kmacura@a...
ID: 46
Inserted: eridley@a...
ID:
Inserted: angelo@a...
ID: 48
Inserted: georgettes@a...
ID:
Inserted: rbakshi@u...
ID:
Inserted: koch@b...
ID: 51
Inserted: bidu@.... wwwbidu .com.br
ID: 52
Inserted: bdodd@b...
ID: 53
Inserted: himanshu_gupta@b...
ID: 54
Inserted: croberts@u...
ID:
Inserted: skclear@a...
ID: 56
Notice the BLANK "ID:" lines. Note that the blank lines come in
sequence (for example, 12 and 13 are missing and then it picks back up
at 14). What am I missing? I use @@IDENTITY all over the place on my
site and this is the first time it's acted like this. YES, the
link_contact table has an auto-incrementing identity field.
PLEASE HELP!
TIA,
Owen
Message #2 by "Owen Mortensen" <ojm@a...> on Fri, 5 Apr 2002 11:32:30 -0700
|
|
Here's some code:
<%Option Explicit%>
<!-- #INCLUDE VIRTUAL = "/functions/dbutils.asp" -->
<%
dim objComm
dim rs
dim strContactEmail
dim contactRS, rsID
dim intLinkID
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = sConnectDB
objComm.CommandType = adCmdText
dim strSQL
dim contactID
strSQL = "SELECT * FROM Links ORDER BY headline"
set rs = getdbrecordset(strSQL)
rs.movefirst
do while not rs.eof
'Set the variables from SQL call
strContactEmail = rs("contact_email")
strContactEmail = replace(strContactEmail, "'", "''")
'Determine if the contact already exists
strSQL = "SELECT contactID, ContactEmail FROM link_contact WHERE
ContactEmail = '" & strContactEmail & "'"
set contactRS = getDBrecordset(strSQL)
if (contactRS.bof and contactRS.eof) then
'********************************* link_contact update
*******************************
objComm.CommandText = "INSERT INTO link_contact
(ContactEmail) VALUES ('" & strContactEmail & "')"
objComm.Execute
Response.Write "Inserted: " & strContactEmail & "<br>" & vbcrlf
contactRS.Close
'Get identity from last update
objComm.CommandText = "SELECT @@IDENTITY AS id FROM
link_contact"
set rsID = objComm.Execute
contactID = rsID("id")
Response.write "ID: " & contactID & "<br>" & vbcrlf
'***********************************************************************
****************
end if
rs.movenext
loop
rs.close
contactRS.close
set rs = nothing
set contactRS = nothing
Response.Write "DB update successful"
%>
This is what I get:
Inserted: synrg@c...
ID: 1
Inserted: brownp@o...
ID: 2
Inserted: kullnig@d...
ID: 3
Inserted:
ID: 4
Inserted: radman697@a...
ID: 5
Inserted: dhovelson@a...
ID: 6
Inserted: ellenhillman@y...
ID: 7
Inserted: sedazenger@h...
ID: 8
Inserted: osmo.rasanen@k...
ID: 9
Inserted: tabruzz@e...
ID: 10
Inserted: rew4050@a...
ID: 11
Inserted: cinnamonlove38@h...
ID:
Inserted: jfnaouri@i...
ID:
Inserted: rschneider@a...
ID: 14
Inserted: nouirakais@v...
ID: 15
Inserted: ramdave@i...
ID: 16
Inserted: partridg@g...
ID: 17
Inserted: suphott@h...
ID: 18
Inserted: roabdi@h...
ID:
Inserted: esoll@h...
ID:
Inserted: KelyGrl75@a...
ID:
Inserted: hsbajwa@h...
ID:
Inserted: alihayder77@h...
ID:
Inserted: ben@s...
ID:
Inserted: rsthak@y...
ID:
Inserted: helga.roedler@k...
ID:
Inserted: radio1@i...
ID:
Inserted: tbouit@y...
ID:
Inserted: Laurent.Trouillard@w...
ID:
Inserted: bahattinh@h...
ID:
Inserted: cmhdr@h...
ID:
Inserted: wc_lin@v...
ID:
Inserted: puchatek30@p...
ID:
Inserted: ch.aldea@t...
ID:
Inserted: tk.oei@r...
ID:
Inserted: kathy.pavlis@m...
ID:
Inserted: pertaangelo@i...
ID:
Inserted: bcasey@a...
ID: 38
Inserted: ajmedical@a...
ID:
Inserted: film@a...
ID:
Inserted: albiin@y...
ID: 41
Inserted: support@A...
ID: 42
Inserted: kdevereaux@m...
ID: 43
Inserted: supple@m...
ID: 44
Inserted: lcarter@d...
ID: 45
Inserted: kmacura@a...
ID: 46
Inserted: eridley@a...
ID:
Inserted: angelo@a...
ID: 48
Inserted: georgettes@a...
ID:
Inserted: rbakshi@u...
ID:
Inserted: koch@b...
ID: 51
Inserted: bidu@.... wwwbidu .com.br
ID: 52
Inserted: bdodd@b...
ID: 53
Inserted: himanshu_gupta@b...
ID: 54
Inserted: croberts@u...
ID:
Inserted: skclear@a...
ID: 56
Notice the BLANK "ID:" lines. Note that the blank lines come in
sequence (for example, 12 and 13 are missing and then it picks back up
at 14). What am I missing? I use @@IDENTITY all over the place on my
site and this is the first time it's acted like this. YES, the
link_contact table has an auto-incrementing identity field.
PLEASE HELP!
TIA,
Owen
|
|
|
View Cart
