ASP Update statement causing syntax error

I can find no problem

[chris_f]

Hi All,

I am having a wierd problem. I am getting an error saying there is something wrong with my SQL statement. The error says:

Syntax error in UPDATE statement.

However, I can find no problem. I have narrowed the problem to the field 'RealName' in the 'Users' table. If you user has not entered a real name then the database enters it as 'No Real Name' instead of it being blank. If I leave the field blank the query looks like this:

UPDATE Users SET Password='xxxxxx', RealName='No Real Name' WHERE Username='chris_felstead'

If I give it the value 'Test Real Name' then the query looks like this:

UPDATE Users SET Password='xxxxxx', RealName='Test Real Name' WHERE Username='chris_felstead'

Where I leave the field blank or not, the code falls over. Can anyone see anything I have missed.

Chris

[aspdaddy]

It could be password or username are reserved words in the db?

The syntax looks fine, does it look ok with a
response.write strSQL

If you run it in access it will put the cursor on the error.

[chris_f]

I've been using username and password with no problem so I have ruled that out. The response.write produces the statements I put in my first post. Both queries run fine in access.

Chris.

[aspdaddy]

Thats very wierd, if it works in the same cccess db locally, but not on the webserver...then it suggests the Jet engine.

Has the MDAC version has been updated recently?.

objConn.Properties("Provider Version")

<added>Does it work in PWS>?</added>

[txbakers]

I think username is a reserved word. Even though it worked once, I think you got lucky.

I had the same problem with "date" It ran fine for a while then gave me syntax errors later. It was the word "date" after all.

Try changing the name of the field and rewriting the query.

The syntax is correct, but I do suspect reserved words.

[aspdaddy]

Maybe they they changed the rules on reserved words from Jet 3.51 to Jet 4.0.

I knwo when my host added a service pack that included MDAC 2.6, lots of wierd things happened with ASP/Access.

<added>Just found this one..
Note that 'password' causes 0x80040E14 errors when used in an Access table
Reserved Words [aspfaq.com]
[/added]

[wardbekker]

you can put brackets around words when reserved by access/sql server

UPDATE Users SET [Password]='xxxxxx', RealName='No Real Name' WHERE [Username]='chris_felstead'

In this example i assume Password & Username are reserved words

[chris_f]

Thanks Guys,

I've not updated the MDAC recently so I can't see it being that, anyway I'll try the brackets first. Then renaming the fields. It's going to be alot of work though.

Chris.