Avatar of cosmopolita
cosmopolitaFlag for Spain asked on

ASP - Get last record after INSERT

Dear eXperts
I have this INSERT and would like to get the number of the record where the INSERT has been taken place (that is to say: last record).

sql_insert = "insert into reservas (turnier_id, firstname, lastname, xtel, xdni, xplz, xtown, email, startdate, startmonth, enddate, endmonth, PLZ, town, country, xtitel, xtime, extra, region, res1, res2, res3, res4 ) values("&turnier_id&", '"&firstname&"','"&lastname&"','"&xtel&"','"&xdni&"','"&xplz&"','"&xtown&"','"&email&"', "&startdate&","&startmonth&","&enddate&","&endmonth&",'"&PLZ&"','"&town&"','"&country&"','"&xtitel&"','"&xtime&"','"&extra&"','"&region&"','"&res1&"','"&res2&"','"&res3&"','"&res4&"') "

'response.write sql_insert
conn = StrAdminProvider
Set rs=Server.CreateObject("ADODB.Recordset")
rs.open sql_insert, conn

*** The following does not work - gives mostly the previous record

conn2 = StrAdminProvider
Set rs2=Server.CreateObject("ADODB.Recordset")
strSqlStmt2 = "SELECT * FROM reservas WHERE id IN (SELECT MAX(id) FROM reservas)"
rs2.open strSqlStmt2, conn2

id_number=rs2("id")
Microsoft AccessASPSQL

Avatar of undefined
Last Comment
cosmopolita

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
cosmopolita

Microsoft JET Database Engine error '80040e14'

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

/admin/anmeldung_turniere_update.asp, line 98
Guy Hengel [angelIII / a3]

so, you are using MS Access... (btw, I asked what database you are using? )

in which case:
sql_insert = "insert into reservas (turnier_id, firstname, lastname, xtel, xdni, xplz, xtown, email, startdate, startmonth, enddate, endmonth, PLZ, town, country, xtitel, xtime, extra, region, res1, res2, res3, res4 ) values("&turnier_id&", '"&firstname&"','"&lastname&"','"&xtel&"','"&xdni&"','"&xplz&"','"&xtown&"','"&email&"', "&startdate&","&startmonth&","&enddate&","&endmonth&",'"&PLZ&"','"&town&"','"&country&"','"&xtitel&"','"&xtime&"','"&extra&"','"&region&"','"&res1&"','"&res2&"','"&res3&"','"&res4&"') " 
'response.write sql_insert
conn = StrAdminProvider
conn.execute sql_insert
Set rs= conn.execute("select @@identity id")
id_number=rs("id") 

Open in new window

ASKER
cosmopolita

Microsoft VBScript runtime  error '800a01a8'
Object required: 'conn'
/admin/anmeldung_turniere_update.asp, line 97

My conn = StrAdminProvider is:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.mappath("../db/laura.mdb") & "; User Id=admin; Password=;"
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Guy Hengel [angelIII / a3]

so, strAdminProvider is just the connection string...
sql_insert = "insert into reservas (turnier_id, firstname, lastname, xtel, xdni, xplz, xtown, email, startdate, startmonth, enddate, endmonth, PLZ, town, country, xtitel, xtime, extra, region, res1, res2, res3, res4 ) values("&turnier_id&", '"&firstname&"','"&lastname&"','"&xtel&"','"&xdni&"','"&xplz&"','"&xtown&"','"&email&"', "&startdate&","&startmonth&","&enddate&","&endmonth&",'"&PLZ&"','"&town&"','"&country&"','"&xtitel&"','"&xtime&"','"&extra&"','"&region&"','"&res1&"','"&res2&"','"&res3&"','"&res4&"') " 
'response.write sql_insert
conn = Server.CreateObject("ADODB.Connection")
conn.open StrAdminProvider
conn.execute sql_insert
Set rs= conn.execute("select @@identity id")
id_number=rs("id") 
conn.close
set conn = nothing

Open in new window

ASKER
cosmopolita

Microsoft VBScript runtime  error '800a01a8'

Object required: 'conn'

/admin/anmeldung_turniere_update.asp, line 97
Guy Hengel [angelIII / a3]

shit ...

replace:
conn = Server.CreateObject("ADODB.Connection")
by:
set conn = Server.CreateObject("ADODB.Connection")

I am working with .net too much, where the "set" keyword is not longer used
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
cosmopolita

Sorry to bother you:
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression '@@identity id'.

/admin/anmeldung_turniere_update.asp, line 99

After I have taken one @ off and left '@identity id' :
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression '@identity id'.

/admin/anmeldung_turniere_update.asp, line 99
SOLUTION
Leigh Purvis

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Guy Hengel [angelIII / a3]

thanks LPurvis.
the official support note on using @@Identity: http://support.microsoft.com/kb/232144
as you do use the jet 4 provider, that should work.
ASKER
cosmopolita

Hi Angel, Hi Purvis
I am so im`pressed by your job that I would
like to pay you both 125 points. But when I go ti
'Accept Multiple Solutions' I only an give you 125 points both
together. Any idea?
B Rgds
Manfred
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Guy Hengel [angelIII / a3]

>'Accept Multiple Solutions' I only an give you 125 points both
that's just fine. We are here to provide help, not to gather the points in the first place.
ASKER
cosmopolita

I have adjusted to price to 250 points and than
made the repartition. I hope that's okay.
Thanks for your help & B Rgds from Spain
Manfred