Getting the id of new record-classic asp

Hi all,

I'd like to obtain the id of the new record being inserted in the database. I've been trying lots of things with no luck.

This is the code (I've removed my futile attempts at geting the id):

strSQL = "Insert into Fejlmelding (UserIPAddress, Navn, Udstyr, Beskrivelse, Datomodtaget, DatoAfsluttet, Status, Bemaerkninger, Email, tlf) values('" & UserIPAddress & "','" & Navn_ok & "','" & Request.Form("Udstyr") & "','" & Beskrivelse & "','" & d & "','" & d2 & "','" & Request.Form("Status") & "','" & bemaerkninger & "','" & Email & "','" & Request.Form("tlf") & "')"

Open in new window


What is the correct code to obtain this? I'm using mySQL and classic asp (yes, I'm that old).
mambechAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
assuming you have an identity column defined in your table, it's as easy as doing:

sql = "insert into Tbl (fields, defined, here ) values( values, defined, here); select @@identity;"
set rs = conn.Execute( sql )
myID = rs( 0 )

now, with the approach you're using, you're subjecting yourself to sql injection. To protect yourself, I recommend using either a stored procedure or parameterized queries.

if you'd like help, let me know, and I can provide additional examples. Personally, I would use both techniques, using parameterized queries to call a stored procedure that will do the insert and return the ID for you
chaauCommented:
For MySQL you need to use last_insert_id()
SELECT LAST_INSERT_ID();

Open in new window

Please note that some DB providers do not allow the multiple statements in one query. Therefore use two queries:
sql = "insert into Tbl (fields, defined, here ) values( values, defined, here);"
conn.execute(sql)
sql = "SELECT LAST_INSERT_ID();"
set rs = conn.Execute( sql )
myID = rs( 0 )

Open in new window

mambechAuthor Commented:
Thanks both of you! I've tried this:

strSQL = "Insert into Fejlmelding (UserIPAddress, Navn, Udstyr, Beskrivelse, Datomodtaget, DatoAfsluttet, Status, Bemaerkninger, Email, tlf) values('" & UserIPAddress & "','" & Navn_ok & "','" & Request.Form("Udstyr") & "','" & Beskrivelse & "','" & d & "','" & d2 & "','" & Request.Form("Status") & "','" & bemaerkninger & "','" & Email & "','" & Request.Form("tlf") & "')"
conn.execute(sql)
sql = "SELECT LAST_INSERT_ID();"
set rs = conn.Execute( sql )
myID = rs( 0 )

response.write myID

Open in new window


And get this: Microsoft VBScript runtime error '800a01a8'

Object required: 'conn'
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

chaauCommented:
Conn is an object of type Adodb.connection, or similar. How do you construct the data source? Show us your code
mambechAuthor Commented:
Certainly!

Database connection:
strConnect = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=********; DATABASE=***********; UID=********; PWD=*****

Open in new window


And the rest:
Set DATABASE = Server.CreateObject("ADODB.Connection")
  DATABASE.Open strConnect 
 
strSQL = "Insert into Fejlmelding (UserIPAddress, Navn, Udstyr, Beskrivelse, Datomodtaget, DatoAfsluttet, Status, Bemaerkninger, Email, tlf) values('" & UserIPAddress & "','" & Navn_ok & "','" & Request.Form("Udstyr") & "','" & Beskrivelse & "','" & d & "','" & d2 & "','" & Request.Form("Status") & "','" & bemaerkninger & "','" & Email & "','" & Request.Form("tlf") & "')"
conn.execute(sql)
sql = "SELECT LAST_INSERT_ID();"
set rs = conn.Execute( sql )
myID = rs( 0 )

response.write myID

  DATABASE.Execute(strSQL)

  DATABASE.Close
  Set DATABASE = Nothing

Open in new window

Big MontyWeb Ninja at largeCommented:
conn is just a variable for your database object, all you have to do is change it to:

Set DATABASE = Server.CreateObject("ADODB.Connection")
  DATABASE.Open strConnect 
 
strSQL = "Insert into Fejlmelding (UserIPAddress, Navn, Udstyr, Beskrivelse, Datomodtaget, DatoAfsluttet, Status, Bemaerkninger, Email, tlf) values('" & UserIPAddress & "','" & Navn_ok & "','" & Request.Form("Udstyr") & "','" & Beskrivelse & "','" & d & "','" & d2 & "','" & Request.Form("Status") & "','" & bemaerkninger & "','" & Email & "','" & Request.Form("tlf") & "')"
DATABASE.execute(strSQL)
sql = "SELECT LAST_INSERT_ID();"
set rs = DATABASE.Execute( strSQL)
myID = rs( 0 )

response.write myID

  DATABASE.Close
  Set DATABASE = Nothing

Open in new window

mambechAuthor Commented:
Now I get this:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/development/send_fejlmelding.asp, line 37

even though I'm a total classic asp dumb-ass I do however suspect that this is to do with the tables I'm updating not matching each other or not existing in the database - right?
Big MontyWeb Ninja at largeCommented:
being new at something doesn't make you a dumb-ass :)

plus, you're spot on with your assessment, something is not matching up column-wise.

which line of code is line 37 referring to?
mambechAuthor Commented:
Ok, now I'm confused...

I inserted this and kept just one table (UserIPAddress) for update...

Same error...
<!--#include file="database.inc"--><!--#include file="SqlCheckInclude.asp"--><html>

<head>
<meta name="robots" content=" noindex, nofollow">
		<link type="text/css" href="style.css" title="std" rel="stylesheet">
<title>Fejlmelding afsendt</title>
<%
Dim UserIPAddress
UserIPAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If UserIPAddress = "" Then
  UserIPAddress = Request.ServerVariables("REMOTE_ADDR")
End If
%>
 <% 
Set DATABASE = Server.CreateObject("ADODB.Connection")
  DATABASE.Open strConnect 
 
strSQL = "Insert into Fejlmelding (UserIPAddress) values('" & UserIPAddress & "')"
DATABASE.execute(strSQL)
sql = "SELECT LAST_INSERT_ID();"
set rs = DATABASE.Execute( strSQL )
myID = rs( 0 )

response.write myID


  DATABASE.Close
  Set DATABASE = Nothing

%>

Open in new window

This is the whole code until the issue arises.

I get:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/development/send_fejlmelding.asp, line 22


Line 22 is empty as far as I can see...???
Big MontyWeb Ninja at largeCommented:
the error is referring to:

myID = rs( 0 )

it must not like the fact that it's 2 separate queries being run, so let's try changing it to:

strSQL = "Insert into Fejlmelding (UserIPAddress) values('" & UserIPAddress & "');"
sql = sql & "SELECT LAST_INSERT_ID();"
set rs = DATABASE.Execute( strSQL )
myID = rs( 0 )

response.write myID

Open in new window


if that doesn't work, I recommend using a stored procedure
mambechAuthor Commented:
Same issue:

<!--#include file="database.inc"--><html>

<head>
<meta name="robots" content=" noindex, nofollow">
		<link type="text/css" href="style.css" title="std" rel="stylesheet">
<title>Fejlmelding afsendt</title>
<%
Dim UserIPAddress
UserIPAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If UserIPAddress = "" Then
  UserIPAddress = Request.ServerVariables("REMOTE_ADDR")
End If
%>
 <% 
Set DATABASE = Server.CreateObject("ADODB.Connection")
  DATABASE.Open strConnect 
 
strSQL = "Insert into Fejlmelding (UserIPAddress) values('" & UserIPAddress & "');"
sql = sql & "SELECT LAST_INSERT_ID();"
set rs = DATABASE.Execute( strSQL )
myID = rs( 0 )

response.write myID


  DATABASE.Close
  Set DATABASE = Nothing

%>

Open in new window


Error: ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/development/send_fejlmelding.asp, line 21
Big MontyWeb Ninja at largeCommented:
can you run the 2 sql statement directly in your database and see if you get anything back? This'll tell us if it's a driver issue or something else
mambechAuthor Commented:
Could it be related to mySQL database?

My id-row is called Id, Datatype mediumINT(9), Extra: AUTOINCREMENT
Big MontyWeb Ninja at largeCommented:
not sure, that's why I want you to run the sql statement directly in the database and see if you get the id back
mambechAuthor Commented:
Terribly sorry, but I get all sorts of sql errors when inserting the code. What exactly would you like me to write in the sql query in mysql?
Big MontyWeb Ninja at largeCommented:
create a simple insert statement into that table, then run the statement

SELECT LAST_INSERT_ID();
mambechAuthor Commented:
It returns this:

Capture.PNG
Big MontyWeb Ninja at largeCommented:
did you run an insert statement right before the select statement?

do you have a phpmyadmin config file for MySql?
chaauCommented:
Let's do this:
 <% 
Set DATABASE = Server.CreateObject("ADODB.Connection")
  DATABASE.Open strConnect 
Set rs = server.createObject("ADODB.recordset")
 
strSQL = "Insert into Fejlmelding (UserIPAddress) values('" & UserIPAddress & "')"
rs.Open strSQL, DATABASE
sql = "SELECT LAST_INSERT_ID();"
rs.open sql, DATABASE, 0, 1, 1
myID = adoRS.Fields(0).Value

response.write myID


  DATABASE.Close
  Set DATABASE = Nothing
 Set rs = Nothing
%>

Open in new window

This should work. It definitely works for me
mambechAuthor Commented:
Hi Chaau,

I posted this in the code:

<!--#include file="database.inc"--><html>

<head>
<meta name="robots" content=" noindex, nofollow">
		<link type="text/css" href="style.css" title="std" rel="stylesheet">
<title>Fejlmelding afsendt</title>
<%
Dim UserIPAddress
UserIPAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If UserIPAddress = "" Then
  UserIPAddress = Request.ServerVariables("REMOTE_ADDR")
End If
%>
 <% 
Set DATABASE = Server.CreateObject("ADODB.Connection")
  DATABASE.Open strConnect 
Set rs = server.createObject("ADODB.recordset")
 
strSQL = "Insert into Fejlmelding (UserIPAddress) values('" & UserIPAddress & "')"
rs.Open strSQL, DATABASE
sql = "SELECT LAST_INSERT_ID();"
rs.open sql, DATABASE, 0, 1, 1
myID = adoRS.Fields(0).Value

response.write myID


  DATABASE.Close
  Set DATABASE = Nothing
 Set rs = Nothing
%>

Open in new window


And got this: Microsoft VBScript runtime error '800a01a8'

Object required: ''

/development/send_fejlmelding.asp, line 23
chaauCommented:
Sorry, my fault. typo. Change the line 23 from adoRS to rs:
myID = rs.Fields(0).Value

Open in new window

Just copied this code from my file. The object in my file is called adoRS

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mambechAuthor Commented:
Fantastic! Problem solved!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.