Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Get ID of record you just inserted

How do I capture the ID of the record I just inserted into DB.

column name is ID.
SQL = "INSERT INTO homeFormUsers (firstName,lastName,emailAdd, ipAddress, interestedIn, datesubmitted)" 
SQL = SQL & "VALUES ('" & strFname & "','" & strLname & "','" & strEmailAdd & "','" & strIP & "','" & strIntersted & "','" & strCurrDateTime & "')"
objConn.execute SQL

Open in new window

0
pGustafson
Asked:
pGustafson
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do (presuming it's a identity
SQL = "SET NOCOUNT ON; INSERT INTO homeFormUsers (firstName,lastName,emailAdd, ipAddress, interestedIn, datesubmitted)" 
SQL = SQL & "VALUES ('" & strFname & "','" & strLname & "','" & strEmailAdd & "','" & strIP & "','" & strIntersted & "','" & strCurrDateTime & "'); SELECT SCOPE_IDENTITY() ID "
dim rst
set rst = objConn.execute(SQL) 
response.write rst.fields("ID").Value

Open in new window

0
 
pGustafsonAuthor Commented:
When I use that I get:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you must have applied the suggested change incompletely.
either the SET NOCOUNT ON is missing, or the ID after SCOPE_IDENTITY() ...
0
 
pGustafsonAuthor Commented:
Thanks that worked!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now