Syntax error in UPDATE statement.

[Ian Hills]

When I click OK in Database Editor after doing an Edit, I get this message -

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in
UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



And back in FP, I do a "verify" and get this -



Server error: Unable to retrieve schema information from the query:

UPDATE diary SET eventday = '1' , eventmonth = '2' , eventyear = '3' ,
caption = '4' , desc = '5' WHERE (id = 6)

against a database using the connection string

DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=URL=fpdb/db1.mdb;UID=Administrator;PWD=secret.

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

UPDATE diary SET eventday = '::eventday::' , eventmonth = '::eventmonth::' ,
eventyear = '::eventyear::' , caption = '::caption::' , desc = '::desc::'
WHERE (id = ::id:



Can anyone please tell me why this is happening? My Database Editor for
another table
(using the same connection) works fine. Am I only allowed 1 table per
connection?

 

[Thomas A. Rowe]

This is not an issue dealing with your database connection, it is your actual update SQL statement.
Which version of FP are you using?

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

 

[Kevin Spencer]

Usually, the "syntax error" indicates that there is something wrong with the
SQL Statement itself, rather than, for example, a data type mismatch. It
often occurs in SQL strings that are built from variable values, such as
yours. In your case, as the data in the query is enclosed in quotes, even
missing data there would not cause the query to fail. But I see that "id"
in the WHERE clause is not enclosed in quotes (probably an autonumber
field). If the id was missing when the statement was created, this would
result in the syntax error you mentioned, as it would invalidate the query
string.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Neither a follower
nor a lender be.

 

[Bob Lehmann]

desc is a reserved word. Name your column something else or enclose it in
square brackets [desc].

Bob Lehmann

 

[Kevin Spencer]

Man, you're right, Bob.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Neither a follower
nor a lender be.

desc is a reserved word. Name your column something else or enclose it in
square brackets [desc].

Bob Lehmann

When I click OK in Database Editor after doing an Edit, I get this message -

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in
UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



And back in FP, I do a "verify" and get this -



Server error: Unable to retrieve schema information from the query:

UPDATE diary SET eventday = '1' , eventmonth = '2' , eventyear = '3' ,
caption = '4' , desc = '5' WHERE (id = 6)

against a database using the connection string

DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=URL=fpdb/db1.mdb;UID=Administrator;PWD=secret.

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

UPDATE diary SET eventday = '::eventday::' , eventmonth =

'::eventmonth::'

,
eventyear = '::eventyear::' , caption = '::caption::' , desc = '::desc::'
WHERE (id = ::id:



Can anyone please tell me why this is happening? My Database Editor for
another table
(using the same connection) works fine. Am I only allowed 1 table per
connection?

 

[Ian Hills]

Thanks guys - "desc" a reserved word - why didn't I think of that. Doh!

desc is a reserved word. Name your column something else or enclose it in
square brackets [desc].

Bob Lehmann

When I click OK in Database Editor after doing an Edit, I get this message -

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in
UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



And back in FP, I do a "verify" and get this -



Server error: Unable to retrieve schema information from the query:

UPDATE diary SET eventday = '1' , eventmonth = '2' , eventyear = '3' ,
caption = '4' , desc = '5' WHERE (id = 6)

against a database using the connection string

DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=URL=fpdb/db1.mdb;UID=Administrator;PWD=secret.

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

UPDATE diary SET eventday = '::eventday::' , eventmonth =

'::eventmonth::'

,
eventyear = '::eventyear::' , caption = '::caption::' , desc = '::desc::'
WHERE (id = ::id:



Can anyone please tell me why this is happening? My Database Editor for
another table
(using the same connection) works fine. Am I only allowed 1 table per
connection?