ASP Update Access Record

[gene7135]

Hi there,I have what seems like a ver simple problem, but have gotten no where trying to fix this on my own.I am trying to update a record within an Access DB.

<%ID = request("ID")First = request("First")set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/websites/woodshack459/jetboatservices.com/fpdb/family.mdb"sql="UPDATE members SET "sql = sql & "First = 'Granville' WHERE ID='1'"Response.Write "<BR>" & SQLconn.Execute SQLResponse.Write "Record " & ID  & " Updated!"conn.closeSet conn = Nothing%>

I recieve the following syntax error when I execute it.The SQL Statement is also printed to the screen for debugging.Any Help would be great!!Thanks Gene

UPDATE members SET First = 'Granville' WHERE ID='1' Microsoft JET Database Engine error '80040e14' Syntax error in UPDATE statement. /Phillips/Tree/change_member.asp, line 39 

[jesh]

If the ID column is storing an integer (int) rather than a string (varchar), using WHERE ID='1' might be the cause of your problem. Have you tried:

UPDATE members SET First = 'Granville' WHERE ID=1

[gene7135]

If the ID column is storing an integer (int) rather than a string (varchar), using WHERE ID='1' might be the cause of your problem. Have you tried:

UPDATE members SET First = 'Granville' WHERE ID=1

Tried that. same error.[Output]UPDATE members SET First = 'Granville' WHERE ID=1 Microsoft JET Database Engine error '80040e14' Syntax error in UPDATE statement. /Phillips/Tree/change_member.asp, line 27 [/Output]Seems like such a simple problem.Any Other Ideas?

[pulpfiction]

[You must have already done this] check your DB table name and fieldnames in the query......here's a discussion about similar error...http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=81

[justsomeguy]

Unless Access is more quirky then I give it credit for, there is nothing syntactically wrong with that statement. "UPDATE members SET First = 'Granville' WHERE ID=1" is a perfectly valid SQL statement, so unless Access does not understand standard SQL, then the problem has to be in the database itself. Are you certain that this is the query on line 27? I'm not questioning your line counting abilities, but this statement is valid, assuming there's a table called members and fields called First and ID. Check the case on the field names as well.

[gene7135]

Unless Access is more quirky then I give it credit for, there is nothing syntactically wrong with that statement. "UPDATE members SET First = 'Granville' WHERE ID=1" is a perfectly valid SQL statement, so unless Access does not understand standard SQL, then the problem has to be in the database itself. Are you certain that this is the query on line 27? I'm not questioning your line counting abilities, but this statement is valid, assuming there's a table called members and fields called First and ID. Check the case on the field names as well.

I double CheckedLine 27 is: conn.Execute SQLThe SQL Vaiable is built from sql="UPDATE members SET " sql = sql & "First = 'Granville' WHERE ID=1"I verified the Field names and Table name are all correct. I have built many Access Update pages before and never had a problem such as this. I am not one to give up. There has to be an answer.

[justsomeguy]

Does Access have a problem with the presence or absence of spaces around the equal sign?

[pulpfiction]

Does Access have a problem with the presence or absence of spaces around the equal sign?

Think this could solve your error, try thissql="UPDATE [members] SET "sql = sql & "[First]='Granville' WHERE [iD]=1"

[pulpfiction]

Guess the error was because of reserved keyword "first" , this query worked with a sample DB to test...... sql="UPDATE members SET "sql = sql & "[First]='Granville' WHERE ID=1"

[gene7135]

Guess the error was because of reserved keyword "first" , this query worked with a sample DB to test...... sql="UPDATE members SET "sql = sql & "[First]='Granville' WHERE ID=1"

It Works!!!What is weird, I was able to use the same syntax (w/o brackets) to insert new records into the Access DB and query from. I just used the same syntax to update a 2nd field called 'Middle' and did not need brackets.For Some reason Updating a record has different rules.Like you mentioned this must be because 'First' is a reserved word.Thanks For Your Help!I appreciate it!

[pulpfiction]

Yeah its kinda wierd, "first" is not listed in the reserved keywords too....MS Access 2000: http://support.microsoft.com/default.aspx?...kb;en-us;209187Source: http://www.databasedev.co.uk/ms-access-reserved-words.html

[treybason]

Access will give some strange problems like this that are completely unexplainable when using access as backend for a website. I had a website once using access as a backend. I had a user table (tbl_User) with a column named "Password". One day my insert statements into this table that were trying to insert the "Password" data started failing. I had to change this field name to "Passwd" for it to work again. Those insert statements were working using "Password" for a while, but one day Access decided it did not like that field name.Strange...