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?
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 MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
0
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

0
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'
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

chaauCommented:
Conn is an object of type Adodb.connection, or similar. How do you construct the data source? Show us your code
0
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

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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

0
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?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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?
0
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...???
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
0
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
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
0
mambechAuthor Commented:
Could it be related to mySQL database?

My id-row is called Id, Datatype mediumINT(9), Extra: AUTOINCREMENT
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
0
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?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
create a simple insert statement into that table, then run the statement

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

Capture.PNG
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
did you run an insert statement right before the select statement?

do you have a phpmyadmin config file for MySql?
0
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
0
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
0
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
0

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!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.