Solved

Characters found after end of SQL statement.

Posted on 2004-04-27
10
1,679 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
so something like commitTrans, rollbacktrans beginTrans isn't relevant?
0
 
LVL 6

Expert Comment

by:Lord_McFly
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
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
Comment Utility
>> 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
Comment Utility
standard if statement for error control?
0
 
LVL 6

Expert Comment

by:Lord_McFly
Comment Utility
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
Comment Utility
Thanks :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

10 Experts available now in Live!

Get 1:1 Help Now