Problem Solved!!!
The field password is a reserved word with MDAC and Access. I guess MDAC 2.5
Sp1 now treats the word password as reserved. The fix is in an Insert or
Update statement surround the field name with square brackets. Then the SQL
command is processed as a field name rather than a reserved word.
In fact this is the major solution to most ODBC error 37000 Syntax when
using Access. Surround the suspect field in square brackets and then test
it. I found this by opening Access 2000 and doing the exact same Update as
is in UserMan/index.cfm and Access cleans up the syntax to make the query
work. When I saw Access use square brackets I said, "son of a gun, where did
that come from?"
Sure enough I tried it and it worked great.
Here is the new query
UPDATE Users
SET Username='#txtUsername#',
[Password]='#txtPassword1#', <--- Field name in sq. brackets --->
Description='#txtDescription#',
Email='#txtEmail#'
WHERE User_ID=#SelectedUser_ID#
- Steve
-----Original Message-----
From: Steve Pierce [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 14, 2000 10:19 PM
To: [EMAIL PROTECTED]
Subject: Syntax Error in Forums
I am getting the following error on a new installation of Forums 2.06. I
have tested this with MDAC 2.1 SP1 and MDAC 2.5 Sp1 and get the same
problem. This is the error when I try to edit or add a user in the system.
Any suggestions.
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
Data Source = "CFRealm"
SQL = "UPDATE Users SET Username = 'Administrator', Password = 'itsasecret',
Description = 'Administrator', Email = '[EMAIL PROTECTED]' WHERE User_ID =
1"
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (178:3) to (178:58) in the template
file d:\mysite\forums\Admin\UserMan\Index.cfm
Here is the query code in in index.cfm
<CFQUERY name="UpdateUserRecord" datasource="#RealmDS#">
UPDATE Users
SET Username = '#txtUsername#',
Password = '#txtPassword1#',
Description = '#txtDescription#',
Email = '#txtEmail#'
WHERE User_ID = #SelectedUser_ID#
</CFQUERY>
- Steve
Steve Pierce, HDL
Direct: (734) 482-9682
mailto:[EMAIL PROTECTED] | http://HDL.com
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
