ODBC Error Code 37000 ... Please help, I'm going crazy with this!!!

[josel]

The Code:

<!--- Debugging Stuff --->

<cfparam name=&quot;MethodFound&quot; default=&quot;No&quot;>
<cfparam name=&quot;SessionUser&quot; default=&quot;No&quot;>
<cfparam name=&quot;URLID&quot; default=&quot;No&quot;>

<cfif isdefined(&quot;url.Method&quot>

<cfset MethodFound=&quot;Yes&quot;>

<cfif isdefined(&quot;session.UserName&quot>

<cfset SessionUser=&quot;Yes&quot;>

<cfif isdefined(&quot;URL.ID&quot>

<cfset URLID=&quot;Yes&quot;>

<cfset Share=&quot;N&quot;>

<cfset MailList=&quot;N&quot;>

<cfif isdefined(&quot;form.MailList&quot>
<cfset MailList=&quot;Y&quot;>
</cfif>

<cfif isdefined(&quot;form.Share&quot>
<cfset Share=&quot;Y&quot;>
</cfif>

<cfquery name=&quot;updtUser&quot; datasource=&quot;deco&quot; dbtype=&quot;odbc&quot;>
update sysAccounts
set UserCode='#form.email#',
MailList='#MailList#',
Share='#Share#',
Company='#form.Company#',
FirstName='#form.FirstName#',
LastName='#form.LastName#',
Address1='#form.Address1#',
Address2='#form.Address2#',
City='#form.City#',
State='#form.State#',
ZipCode='#form.ZipCode#',
HomePhone='#form.HomePhone#',
WorkPhone='#form.WorkPhone#',
CellPhone='#form.CellPhone#',
FaxPhone='#form.FaxPhone#',
LastUpdated='#dateformat(now(), &quot;DDD, MMM D, YYYY&quot#',
where UserID=#session.UserID#
</cfquery>
<cfquery name=&quot;updtID&quot; datasource=&quot;deco&quot; dbtype=&quot;odbc&quot;>
update sysUserID
set UserCode='#form.email#',
UserPswd='#form.password1#',
where UserID=#session.UserID#
</cfquery>
</cfif>
</cfif>
</cfif>


<cfoutput>

<!--- Debugging stuff ... --->

MethodFound: #MethodFound#<br>
SessionUser: #SessionUser#<br>
URLID......: #URLID#<br>
<p>&nbsp;
<a href=&quot;myProfile.cfm?ID=#session.UserID#&quot;>Continue!</a>

</cfoutput>

----------------------------------------------------------------------------
The error:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


SQL = &quot;update sysAccounts set UserCode='demo@decodemo.com', MailList='N', Share='Y', Company='My Company', FirstName='Jose', LastName='Lerebours', Address1='951 NW 185th Terrace', Address2='', City='Pembroke PInes', State='FL', ZipCode='33029', HomePhone='954-431-2468', WorkPhone='', CellPhone='', FaxPhone='954-538-9815', LastUpdated=Wed, Feb 6, 2002, where UserID=20&quot;

Data Source = &quot;DECO&quot;


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (29:3) to (29:43).

-------------------------------------------------------------------------------------------

Every field in table is defined as text and they allow zero length ... I am using MS Access CF 5.0. I have read many other posts in this forum with same error but none of the suggestions seem to cure my problem. All fields in form are also text fields.

I have tried dateformat(now(), ...) with and without quotes and NADA ...


Thank you all for you help;

josel If you have the knowledge, consult and educate those who need it! - Jose Lerebours

 

[sshhz]

Instead of remove the quotes or not, i suggest you use CreateODBCDate() function for your date at LastUpdated field.

Example:

<cfset todaydt = #DateFormat(now(), &quot;DDD, MMM D, YYYY&quot

LastUpdated = #CreateODBCDate(todaydt)#

it's a best practise to do the formatting in a local variable instead of merging that in the sql updates.

Good Luck.

-sshhz-

 

[sshhz]

Instead of remove the quotes or not, i suggest you use CreateODBCDate() function for your date at LastUpdated field.

Example:

<cfset todaydt = #DateFormat(now(), &quot;DDD, MMM D, YYYY&quot

LastUpdated = #CreateODBCDate(todaydt)#

it's a best practise to do the formatting in a local variable instead of merging that in the sql updates.

Good Luck.

-sshhz-

 

[josel]

sshhz,

Thank you, I will try that ...

LastUpdated is set as &quot;text&quot; field in the table. Would this cause a problem? Do I need to use a function to convert the value of DateFormat(now()...) or CreateODBCDate(todaydt) to string/text? If so, what's the function to use?

I programmed in DBIII and Clipper almost a century ago and recall something like this being the case ...

Thank you in advance for your much needed help!

josel If you have the knowledge, consult and educate those who need it! - Jose Lerebours

 

[sshhz]

Hi, Josel. Yes, it would be another problem if the data type in the table is text instead of a valid date type. I suggest you set this field as the date type so that you don't ve any trouble saving the 'date value' into your table.

CreateOBDCDate() function is to return a date in ODBC date format, so that it is a valid date formatting. As for DateFormat() function, is to return a formatted date or time value, depending what formating you want.

Sorry for submitting my previous comment twice as the i encountered some problems during submitting my posting.

-sshhz-

 

[josel]

Hello sshhz,

I truly appreciate you help on this one - right now I need some one to hold me by the hand before I stop believing 8-(

I have removed the line of code that updates this field and still get the error.

You know, I am starting to wonder if the problem is with my session variable session.UserID. The table's UserID is a numeric field. Do variables assume the data type as they are set? session.UserID is used in other templates to retrieve records and it works OK ... so this may prove my suspecions wrong.

Oh, I also removed the comma from LAST field assignment.

-------------------- THIS IS THE LATEST -------------------

<cfquery datasource=&quot;deco&quot; dbtype=&quot;odbc&quot;>

update sysAccounts
set UserCode = '#form.email#',
MailList = '#MailList#',
Share = '#Share#',
Company = '#form.Company#',
FirstName = '#form.FirstName#',
LastName = '#form.LastName#',
Address1 = '#form.Address1#',
Address2 = '#form.Address2#',
City = '#form.City#',
State = '#form.State#',
ZipCode = '#form.ZipCode#',
HomePhone = '#form.HomePhone#',
WorkPhone = '#form.WorkPhone#',
CellPhone = '#form.CellPhone#',
FaxPhone = '#form.FaxPhone#',
where UserID = #session.UserID#

</cfquery>

====================================================

I get the following error:

Queries


(Records=0, Time=15ms)
SQL =
update sysAccounts
set UserCode = 'demo@decodemo.com',
MailList = 'N',
Share = 'Y',
Company = 'My Company',
FirstName = 'Jose',
LastName = 'Lerebours',
Address1 = '951 NW 185th Terrace',
Address2 = '',
City = 'Pembroke PInes',
State = 'FL',
ZipCode = '33029',
HomePhone = '954-431-2468',
WorkPhone = '',
CellPhone = '',
FaxPhone = '954-538-9815',
where UserID = 20

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

SQL = &quot;update sysUserID set UserCode='demo@decodemo.com', UserPswd='demo', where UserID=20&quot;

Data Source = &quot;DECO&quot;
======================================================

Regards;

josel
If you have the knowledge, consult and educate those who need it! - Jose Lerebours

 

[sshhz]

Josel,

Looking at your codes, most probably you are updating two tables. It could be the relationship of the tables that you defined.

Two tables are &quot;sysAccount Table&quot; and &quot;sysUserID Table&quot;, if both tables as the relationship, you must carefully do your update statments, as you can't update the 'child' table without updating to the 'mother' table.

Try to run the update sysUserID table first, then into sysAccount table. I should work in that way. Good Luck.

FYI, if the field in the database is numeric, when you do the updating or inserting, don't include the comma for these variables userID = #userid#, if that is text or string type, then it should be like useremail = '#mymail#'

-sshhz-

 

[josel]

Dear sshhz,

I can't begin to tell you the reliefe I feld when I submitted the form and BAAANG, IT WORKED!!!

I turns out that, as you mentioned, updating two tables in two different queries. We focused on first query, maybe because second query was only updating two fields.

Notice that sysUserID update statement has a comma on last field assignment ... I removed the comma and it worked OK!

For whatever it's worth, you made my heroes' list !!!!

Much thanks for your time.

Regards;

josel

PS: Stick around, I'm not going any where; there'll be much more to come!!!
If you have the knowledge, consult and educate those who need it! - Jose Lerebours

 

[sshhz]

Actually, i didn't notice that comma was put on your second sql query to update the sysUserID table, that's my mistake, i'm sorry.

I'm glad you managed to get your solutions! Cheers! I'll be having holiday next week. I ll be back after next week. Don't worry, i won't run away ... i'll be in from time to time in the future.

-sshhz-

 

[Guest_imported]

I too used to program in dBase III and Clipper! Anyway, my two cents: if you are ever going to use the date field in a where clause (to filter the records), then you absolutely must make it a date type. That way us can use all the normal SQL operators. If ever that is not an option, then you would have to retrieve records by filtering as best you can with SQL, then further filter using ColdFusion's Date functions by looping through the record set.

 

[josel]

Thanks for the advise guys, I truly appreciate them all !!!

This forum sure beats all of my books


Regards;

josel If you have the knowledge, consult and educate those who need it! - Jose Lerebours