Solved

Characters found after end of SQL statement.

Posted on 2004-04-27
10
1,681 Views
Last Modified: 2007-11-27
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
Comment
Question by:avivak1
10 Comments
 
LVL 6

Accepted Solution

by:
Lord_McFly earned 200 total points
ID: 10934513
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
 

Author Comment

by:avivak1
ID: 10934544
Is it as safe as can be - are there any protection code involved other than creating a new query a line afterwoulds?
0
 
LVL 6

Expert Comment

by:Lord_McFly
ID: 10934561
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
 

Author Comment

by:avivak1
ID: 10934699
so something like commitTrans, rollbacktrans beginTrans isn't relevant?
0
 
LVL 6

Expert Comment

by:Lord_McFly
ID: 10934837
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Expert Comment

by:leechoonhwee
ID: 10935379
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
 
LVL 28

Expert Comment

by:sybe
ID: 10936003
>> 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
 

Author Comment

by:avivak1
ID: 10937003
standard if statement for error control?
0
 
LVL 6

Expert Comment

by:Lord_McFly
ID: 10938663
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
 

Author Comment

by:avivak1
ID: 10943536
Thanks :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ODBC connection string for ODBC 6.01 24 71
migrate from classic ASP to C# .NET 5 68
currencylayer API intergrate to ASP 25 65
Choose the older file FSO 6 39
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now