Can not execute update query (ASP--> Jet SQL --> Access 1

[timbooher]

Hello -- I have vetted my MS Access database -- no fields are required, in all fields zero length strings are required. I can't tell of any relationship ('referential integrity') problems.

But with the following query:
update Member set FName = 'John',MName = '',LName = 'Doe',children = 0,email = '',hideAddress = 0,LastUpdate = '6/26/2005',local = 0,MakeEmailPrivate = 0,Married = 0,MobilePhone = NULL,Occupation = '',UName = '',Password = '',PersonalURL = '',AreaCode = 937,Phone = '999-9999',QuickMsg = '',SignificantOtherName = '',StreetAddress = '575 Bellaire Drive',City = 'Los Angles',State = 'CA',ZipCode = '02139',Status = -1,Visits = 0 where ID = 65

I get the following error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.

And it references this line (denoted by !!!) in my code:
Set MyCmd = Server.CreateObject("ADODB.Command")
MyCmd.ActiveConnection = strCorrectConnection
MyCmd.CommandText = myQuery
!!! MyCmd.Execute !!!
MyCmd.ActiveConnection.Close

Now the databse connects correctly runs other updates fine and this update works fine in the access database when I run it as a query.

Here are the fileds in my table (with relevant datatype):
ID (AutoNum)
FName (Text)
LName (Text)
MName (Text)
StreetAddress (Text)
hideAddress Yes/No
City (Text)
State (Text)
ZipCode (Text)
email (Text)
MakeEmailPrivate Yes/No
local Yes/No
LastUpdate Date/Time
Married Yes/No
SignificantOtherName (Text)
Phone (Text)
AreaCode (Number
MobilePhone (Number
PersonalURL (Text)
Status Number
UName (Text)
Password (Text)
children Number
QuickMsg (Text)
Occupation (Text)
Visits (Number)

Never had a problem this hard to track down, I suspect a check-box error or null/empty field issue. Please help, I have troubleshot this to my max capability.

Regards,

Tim

 

[DotNetGnat]

ID is a keyword...try

where [ID] = 65

-DNG

 

[DotNetGnat]

and also this looks suspicious

MobilePhone = NULL

-DNG

 

[Sheco]

Sometimes adding Response.Write myQuery immdediately before executing SQL helps me find the syntax error.

 

[Sheco]

ah, keyword, good eye

 

[DotNetGnat]

even Password is a keyword...

-NG

 

[timbooher]

Well, a great hint and good move forward, but after fixing this, I still have the same problem. Here is my new update SQL command:

update Member set FName = 'Curtis',MName = '',LName = 'Griffin',children = 0,email = '',hideAddress = False,LastUpdate = '6/26/2005',local = False,MakeEmailPrivate = False,Married = False,MobilePhone = NULL,Occupation = '',UName = '',[Password] = '',PersonalURL = '',AreaCode = 937,Phone = '696-9999',QuickMsg = '',SignificantOtherName = '',StreetAddress = '575 Bellaire Drive',City = 'Tipp City',State = 'OH',ZipCode = '45371',Status = -1,Visits = 0 where [ID] = 65

Any thoughts? reall appreciate your help.

Tim

 

[DotNetGnat]

my next doubt is on

Status = -1

what error do you actually get?

did you try this query directly in the access database?

-DNG

 

[timbooher]

Yes, I tried this in the access database, no problems whatsoever.

As for the exact error -- when running locally on IIS and on my ISP's IIS, I get the same error:

# Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/admin/MemberUpdateFromAdmin.asp, line 133

# Browser Type:
Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.8) Gecko/20050511 Firefox/1.0.4

# Page:
POST 287 bytes to /admin/MemberUpdateFromAdmin.asp

# POST Data:
FName=John&MName=&LName=Akins&children=0 . . .etc

Thanks again for your help,

Tim

 

[timbooher]

Oh -- and the Status=-1 is fine -- that is a number field of type integer, not a Boolean field.

Regards,

Tim

 

[DotNetGnat]

show us your relevant code on the ASP page...

Thanks

-DNG

 

[timbooher]

Should have that from above, as I said the connection works fine,

myQuery = "update Member set FName = 'Curtis',MName = '',LName = 'Griffin',children = 0,email = '',hideAddress = False,LastUpdate = '6/26/2005',local = False,MakeEmailPrivate = False,Married = False,MobilePhone = NULL,Occupation = '',UName = '',[Password] = '',PersonalURL = '',AreaCode = 937,Phone = '696-9999',QuickMsg = '',SignificantOtherName = '',StreetAddress = '575 Bellaire Drive',City = 'Tipp City',State = 'OH',ZipCode = '45371',Status = -1,Visits = 0 where [ID] = 65"
Set MyCmd = Server.CreateObject("ADODB.Command")
MyCmd.ActiveConnection = strCorrectConnection
MyCmd.CommandText = myQuery
!!! MyCmd.Execute !!!
MyCmd.ActiveConnection.Close

MyQuery is actually set up by a form, but with the result displayed above, the error is clearly in the SQL statement, somewhere.

Regards,

Tim

 

[DotNetGnat]

is the whole query on one single line??

-DNG

 

[DotNetGnat]

myQuery = "update Member set FName = 'Curtis',"&_
" MName = '',LName = 'Griffin',children = 0,"&_
" email = '',hideAddress = False,"&_
" LastUpdate = '6/26/2005',local = False,"&_
" MakeEmailPrivate = False,Married = False,"&_
" MobilePhone = NULL,Occupation = '',"&_
" UName = '',[Password] = '',PersonalURL = '',"&_
" AreaCode = 937,Phone = '696-9999',"&_
" QuickMsg = '',SignificantOtherName = '',"&_
" StreetAddress = '575 Bellaire Drive',"&_
" City = 'Tipp City',State = 'OH',"&_
" ZipCode = '45371',Status = -1,"&_
" Visits = 0 where [b][ID] = "&request("myid")[/b]

try that...observe the where condition

-DNG

 

[timbooher]

O.K. new twist, yes all on one line: I think the error is the local word -- it is a keyword.

will let you know soon . ..

Tim

 

[DotNetGnat]

yes i think...local is a keyword...take a look at this page of all the keywords

http://www.aspfaq.com/show.asp?id=2080

-DNG

 

[timbooher]

DNG,

yep -- you got it right -- the keyword local fixed the problem when changed to [local]. what a pain.

Thanks,

Tim

 

[DotNetGnat]

No problem...i have also gone through this kind of pain in the past...

Glad its working...

-DNG