SQL Update problem

[TomD]

The following SQL statement is resulting in the error after it:

(pasted from IE window)

UPDATE Clients SET number = 300, name = 'Ace Lock', address1 = 'add1',
address2 = 'add2', city = 'city', state = 'pa', zip = '15222' WHERE id = 1;


Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft
JET Database Engine Description: Syntax error in UPDATE statement. in
c:\inetpub\wwwroot\kdaclients.php on line 40


When I take out the number and name fields, it works fine

UPDATE Clients SET address1 = 'add1', address2 = 'add2', city = 'city',
state = 'pa', zip = '15222' WHERE id = 1;

The number field is a number, the rest are text fields. Any input
appreciated.

[Nicole Calinoiu]

Tom,

When you use reserved words (e.g.: "name") as table or column names, you
need to wrap them in square brackets when using them in a Jet SQL statement.
e.g.:

UPDATE Clients SET [number] = 300, [name] = 'Ace Lock', address1 = ...

This allows the Jet SQL engine to recognize the names as identifiers rather
than mis-use of the reserved words. Of course, an even better approach is
to not use reserved words as table/column names in the first place...

HTH,
Nicole

"TomD" <dcbcadmgr.no@spam.yahoo.com> wrote in message
news:uwKThJj8DHA.2404@TK2MSFTNGP11.phx.gbl...

> The following SQL statement is resulting in the error after it:
>
> (pasted from IE window)
>
> UPDATE Clients SET number = 300, name = 'Ace Lock', address1 = 'add1',
> address2 = 'add2', city = 'city', state = 'pa', zip = '15222' WHERE id =
> 1;
>
>
> Warning: (null)(): Invoke() failed: Exception occurred. Source: Microsoft
> JET Database Engine Description: Syntax error in UPDATE statement. in
> c:\inetpub\wwwroot\kdaclients.php on line 40
>
>
> When I take out the number and name fields, it works fine
>
> UPDATE Clients SET address1 = 'add1', address2 = 'add2', city = 'city',
> state = 'pa', zip = '15222' WHERE id = 1;
>
> The number field is a number, the rest are text fields. Any input
> appreciated.
>
>

[Bob Barrows [MVP]]

Nicole Calinoiu wrote:

> Tom,
>
> When you use reserved words (e.g.: "name") as table or column names,

Just to add:
[url]http://www.aspfaq.com/show.asp?id=2080[/url]


HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.