Solved

"Incorrect syntax in Update" Error, since upsizing for Access to SQL server.

Posted on 2006-06-21
15
276 Views
Last Modified: 2010-04-17
Hello Post readers,

I have recently upsized from access to SQL server using the upsize wizard and i am now going through the code and trying to make it SQL Server friendly.
I have come accross and error that i cant figure out.

I first got an error

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."

to which i used this work around (Yes i tried putting in the dbseechanges but did not work).

cmdUpdateProjects.CommandType = adCmdText
cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & "'" & rsTasks.Fields ("sumofwork").Value & "'" & " Where ProjectId = " & "'" & rsTasks.Fields ("ProjectId").Value & "'"
cmdUpdateProjects.ActiveConnection = CurrentProject.Connection
cmdUpdateProjects.Execute


Now i get an error saying that "inccorect syntax in UPDATE....


Could anyone please check if my syntax is correct, provide me with an alternative and generally give me a heads up when doing an Access to SQL server Upsize.

Thanks in advanced.
All responses appreciated.
0
Comment
Question by:irfo
15 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16956997
See if this makes any difference.  Change:
cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & "'" & rsTasks.Fields ("sumofwork").Value & "'" & " Where ProjectId = " & "'" & rsTasks.Fields ("ProjectId").Value & "'"

To:
cmdUpdateProjects.CommandText = "UPDATE Projects Set Work = '" & rsTasks.Fields("sumofwork").Value & "' Where ProjectId = '" & rsTasks.Fields ("ProjectId").Value & "'"

If you are still getting the same error than output the result of cmdUpdateProjects.CommandText prior to the Execute method and you should see the error immediatelly.  If you don't post the result here.
0
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 16957258
try to print the query and check whether it is correct or not
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16959410
I think I just said that.
0
 

Author Comment

by:irfo
ID: 16966961
Hi guys,

Sorry same error.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16968847
>>Sorry same error.<<
Right, now can you "output the result of cmdUpdateProjects.CommandText prior to the Execute method and you should see the error immediatelly.  If you don't post the result here."
0
 

Author Comment

by:irfo
ID: 16984295

I guessing that you mean that you want me to check it in the imediate window.

(1)
cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & rsTasks.Fields("sumofwork").Value & " WHERE ProjectId = " & rsTasks.Fields("ProjectId").Value
     
I get;

UPDATE Projects Set Work = 80 WHERE ProjectId = 4

(2)
 cmdUpdateProjects.CommandText = " UPDATE Projects Set Work = " & "'" & rsTasks.Fields("sumofwork").Value & "'" & " WHERE ProjectId = " & "'" & rsTasks.Fields("ProjectId").Value & "'"

I get;
     
 UPDATE Projects Set Work = '80' WHERE ProjectId = '4'

Both give the same error.
0
 

Author Comment

by:irfo
ID: 16984359
Another thing is that the original code before i changed it to the above was

CurrentDb.Execute "UPDATE Projects Set Work = " & rsTasks.Fields("sumofwork").Value & " WHERE ProjectId = " & rsTasks.Fields("ProjectId").Value
     

With this i would get the error

You must use the dbSeeChanges option with OpenRecordset when accessing
a SQL Server table that has an IDENTITY column.

If anyone has any recomendations on how i could resolve this option then please let me know.

Thanks.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16984711
>>I guessing that you mean that you want me to check it in the imediate window.<<
No, that is not what I mean.  I know it gives an error and I have told you it would, repeatedly. All we are trying to do is find out WHY it is giving you an error.  So once again, please do the following from the Immediate window PRIOR to the Execute method:

? cmdUpdateProjects.CommandText

And post the results here.
0
 

Author Comment

by:irfo
ID: 16984791
And that is what i have done. Please look closer, all i have done is tried two different version of the syntax.

The results of what you are asking for are after

"I Get;"

Thanks
0
 

Author Comment

by:irfo
ID: 16984979
Here is the output just to end any confusion.

? cmdUpdateProjects.CommandText
UPDATE Projects Set Work = 80 WHERE ProjectId = 4
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16985051
There is nothing wrong with that syntax.  Please post all the relevant code that executes that UPDATE statement, starting with:

DIM cmdUpdateProjects As ADODB.Connection
Set cmdUpdateProjects = New ADODB.Connection

Also, post your connection string, the environment you are using (VB or MS Access), whether you are linking to the SQL tables or accessing them directly.
0
 

Author Comment

by:irfo
ID: 16990861
hello, all sorted now.

It was the 'work' column name. Because it is a reserved word it needs to be in [].

Thanks anyways.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16992173
Yep.  That is correct that is a reserved in JET but not in MS SQL Server, since you appear to be using JET or ODBC (as opposed to going through directly to MS SQL Server) you will have to enclose it in [].  

For a complete list of JET reserved keywords see here:

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

Now please close the question.  See here from the EE Help:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Programming/help.jsp#hi70
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17061395
Closed, 250 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
add text to end of existing text in file 16 61
copyEndy  challenge 15 55
countAbc challenge 9 49
topping3 challenge 14 48
Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
A short article about a problem I had getting the GPS LocationListener working.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

708 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

11 Experts available now in Live!

Get 1:1 Help Now