[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Characters found after end of SQL statement.

Posted on 2004-04-27
10
Medium Priority
?
1,699 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Accepted Solution

by:
Lord_McFly earned 800 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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
 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 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