• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1702
  • Last Modified:

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
0
avivak1
Asked:
avivak1
1 Solution
 
Lord_McFlyCommented:
Step 1...
----------------------------
Perform the insert by itself.

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

rsChck("NewID") is the ID of the last record inserted on the current connection.

I've also assumed that you have opened a connection to your database - in my example my connection is called objCon
0
 
avivak1Author Commented:
Is it as safe as can be - are there any protection code involved other than creating a new query a line afterwoulds?
0
 
Lord_McFlyCommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
avivak1Author Commented:
so something like commitTrans, rollbacktrans beginTrans isn't relevant?
0
 
Lord_McFlyCommented:
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.
0
 
leechoonhweeCommented:
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.
0
 
sybeCommented:
>> 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.
0
 
avivak1Author Commented:
standard if statement for error control?
0
 
Lord_McFlyCommented:
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
%>
0
 
avivak1Author Commented:
Thanks :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now