Hi All,

I could use a little assistance, I am trying to insert a new record into my access db and am trying to extract the Autonumber ID at the same time. I know this is the not the best way to do it but I am restricted by the clients existing db.

I have been trying multiple ways to do this and come up with numerous errors. Here my code.

varDate = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
SET DbConn = Server.CreateObject("ADODB.Connection")
DbConn.Open "DSN=materials;Password=" & PASS_DB
SET oRS = Server.CreateObject("ADODB.Recordset")
strSQL = "INSERT INTO Materiales" _
       & " (Clave, Concepto, Category, Updated)" _
       & " VALUES ('" & Request.Form("tbClave") & "', '" & Request.Form("taConcepto") & "', " & Request.Form("ddCategory") & ", '" & varDate & "')" _
       & " SELECT @@IDENTITY"
Response.Write strSQL
'Response.End
SET oRS = DbConn.Execute(strSQL)
Response.Write "New ID was " & oRS(0)

This one gives me the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of SQL statement.

But then you put the ; on the end and you get the same error

If you do this:
strSQL = "INSERT INTO Materiales" _
       & " (Clave, Concepto, Category, Updated)" _
       & " VALUES ('" & Request.Form("tbClave") & "', '" & Request.Form("taConcepto") & "', " & Request.Form("ddCategory") & ", '" & varDate & "');" _
       & " SELECT @@IDENTITY;"
You get this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.

I am at a loss... Any help would be greatly appreciated

Best Regards,

Paul

Hi All,

I figured it out for myself, seeing as no one helped me. Just if you are wondering how, here is the code:

varDate = Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
nOption = Request.Form("hidWithUnits")
SET DbConn = Server.CreateObject("ADODB.Connection")
DbConn.Open "DSN=materials;Password=" & PASS_DB
SET oRS = Server.CreateObject("ADODB.Recordset")
strSQL = "INSERT INTO Materiales" _
       & " (Clave, Concepto, Category, Updated)" _
       & " VALUES ('" & Request.Form("tbClave") & "', '" & Request.Form("taConcepto") & "', " & Request.Form("ddCategory") & ", '" & varDate & "');"
SET oRS = DbConn.Execute(strSQL)
SET rsData = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT @@IDENTITY AS NewID"
SET rsData = DbConn.Execute(strSQL)
nNewID = rsData("NewID")
rsData.Close
SET rsData = Nothing
oRS.Close
SET oRS = Nothing

Cheers,
Paul