asked on

Characters found after end of SQL statement.

Hey,

It's 3 oclock in the Am and I'm soo tired! Someone put me out of my misery.....

Here's the question - I wanted to add a Record to a table and then take the newest added record's id number for future reference. The Database is Access.

The problem is I only have the code for adding to a sql server database.

Sample Code Below:

strSql = "insert into atable(field1,field2,field3,field4) values ('value','value,'value','value');select @@IDENTITY ;"

I get this error:[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.

I have a feeling it doesn't work because Access doesn't allow multiple sql queries...

Can someone give me sample code that does the same thing for Access with code protecting me that I only get the newest id that was just entered using the insert statement

Thanks,
Aviva

ASKER CERTIFIED SOLUTION

Lord_McFly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

avivak1

ASKER

Is it as safe as can be - are there any protection code involved other than creating a new query a line afterwoulds?

Lord_McFly

The IDENTITY (ID) that it gets is the last record inserted on YOUR current connection so executing it directly after you INSERT statement gets the ID of that record - its full proof.

avivak1

ASKER

so something like commitTrans, rollbacktrans beginTrans isn't relevant?

Lord_McFly

That I couldn't answer - but my understanding would be that if you were using MTS and it failed on the insert it wouldn't perform the SELECT @@INDENTITY anyway.

leechoonhwee

CommitTrans still works but if it is only 1 sql statement, dun think you need it. Just put in an error control trap will do.

sybe

>> I have a feeling it doesn't work because Access doesn't allow multiple sql queries...

That is exactly what is going on. Unlike SQL Server, Access can accept only a single query at a time.

avivak1

ASKER

standard if statement for error control?

Lord_McFly

Ok, I have very limited (basically none) exposure to MTS but the following is approximately what you would need to ensure (absolutely) that nothing goes wrong.

<%
Dim objCon, objCmd
Dim rsChck
Dim sQuery
Dim nAffected

Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "\Misc\DataDB.mdb"

Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objCon

sQuery = "INSERT INTO Table (Field1,Field2,Field3) VALUES (1,2,3)"

objCmd.CommandText = sQuery
objCmd.CommandType = 1

objCon.BeginTrans

objCmd.Execute nAffected

If nAffected > 0 then
      objCon.CommitTrans

      Set rsChck = Server.CreateObject("ADODB.RecordSet")
      sQuery = "SELECT @@ IDENTITY AS NewID"
      rsChck.Open sQuery, objCon

      If rsChck.EOF then
            objCon.RollbackTrans
      Else
            Response.Write "New ID: " & rsChck("NewID")
            Response.End
      End If
Else
      objCon.RollbackTrans

      Response.Write "Process Failed."
      Response.End
End If
%>

avivak1

ASKER

Thanks :)