Database Query Error

[BigNicko7]

Hello..I have a small CF library app setup using a MSACCESS database. All
depts can be searched no problem except one which give me this error:
Processing Request Error Executing Database Query. [MERANT][SequeLink JDBC
Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression '1=1 and PDFNumber in
('6100007','6100006','6100005','6100004','6100003' ,'6100002','6072006','60720039
','60720038','60720036 ','60720033') and Department = 'Women &
Children's''. The error occurred in E:\Results.cfm: line 61 59 : </cfif>
60 : 61 : <cfquery name='SearchList' datasource='#ODBCdsn#' dbtype='ODBC'> 62
: select * 63 : from PDFFiles
--------------------------------------------------------------------------------
SQL select * from PDFFiles where 1=1 and PDFNumber in
(&amp;apos;6100007&amp;apos;,&amp;apos;6100006&amp ;apos;,&amp;apos;6100005&amp;a
pos;,&amp;apos;6100004&amp;apos;,&amp;apos;6100003 &amp;apos;,&amp;
apos;6100002&amp;apos;,&amp;apos;6072006&amp;apos; ,&amp;apos;60720039&amp;apos;,
&amp;apos;60720038&amp;apos;,&amp;apos;60720036&am p;apos;,
&amp;apos;60720033&amp;apos;) and Department = &amp;apos;Women &amp;
Children&amp;apos;s&amp;apos; DATASOURCE pdflibrary VENDORERRORCODE
-3100 SQLSTATE 42000 Text whats the dealio? any ideas?

[byutennismenace]

Hello all. I am working through a tutorial in a book (Dreamweaver MX 2004 and
Databases, by Sean Nicholson) and I am making an intranet website using
Dreamweaver MX (I don't have 2004) and Coldfusion MX. I've followed the
instructions exactly and gone through it twice, but I getting the same error. I
have a page that sucessfully allows me to add an employee to a database. The
page that I made to allow me search the database for that employee's record
works properly. The page that returns the query works fine. The page that let's
me update the database record is what gives me the error. When I try to change
something in the record and select "update," I get an error from Coldfusion
that tells me that it wasn't able to execute the database query. Here is the
message for you guys to look at:

The Error Occurred in C:\Inetpub\wwwroot\KrystalClear-CF\edit_employee.cfm:
line 78

76 : </cfif>
77 : WHERE EmployeeID='#FORM.EmployeeID#'
78 : </cfquery>
79 : <cflocation url="employee_updated.cfm">
80 : </cfif>

Now, line 78 just seems to be the end of the coldfusion query to my untrained
eyes, but there has to be something else. Can anyone tell me what could
possibly be wrong? Thanks a bunch.
Ben

[Tulsa]

You'll need to post the complete error message and the entire query along with any relevant code.

[byutennismenace]

Sorry about that. Here's the whole error message:

Error Occurred While Processing Request
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access
Driver] Data type mismatch in criteria expression.

The Error Occurred in C:\Inetpub\wwwroot\KrystalClear-CF\edit_employee.cfm:
line 78

76 : </cfif>
77 : WHERE EmployeeID='#FORM.EmployeeID#'
78 : </cfquery>
79 : <cflocation url="employee_updated.cfm">
80 : </cfif>




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

SQL UPDATE tbHR SET LastName= 'Grierson' , FirstName= 'William' , HomeAdd=
'XXXXXXXXX' , City= 'XXXX' , HomeState= 'Id' , Zip= 'XXXXX' , HomePhone=
'XXXXXXXXXX' , Department= 'IS' , Title= NULL , Extension= 'x12' , HireDate=
'2005-01-01 00:00:00.0' , EditPages= 0 WHERE EmployeeID='000-001-100'
DATASOURCE connKrystalClearCF
VENDORERRORCODE -3030
SQLSTATE 22018

As for relevant code, I believe that most of the code is okay up until that
point, but I'll give you what there is in a summarized form by cutting out the
redundant part:

<cfparam name="URL.EmployeeID" default="1">
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
<cfquery datasource="connKrystalClearCF">
UPDATE tbHR SET LastName=
<cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ "">
'#FORM.LastName#'
<cfelse>
NULL
</cfif>
, FirstName=
<cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ "">
'#FORM.FirstName#'
<cfelse>
NULL
</cfif>

[Tulsa]

That error is usually generated when quotes are used around a variable that is
going into a table field that is type number/integer, date, or autoNumber. Say
you have a date field then it would be set date = #form.date#, NOT set date =
'#form.date#' Same goes for number/integer fields and autonumbers. You should
look at the all variables and the respective datatypes they are going into.
Also I noticed you are entering a 1 or 0 into a field, if this is a yes/no
field, you might try field_name = yes

[SafariTECH]

since it showed you that the line number was teh one following employeeID, my
guess is that emplyeeID is actually a number going into a numerical field - but
you put quotes around it which turns it into a string.

In SQL server this doesn't matter, but in MS Access it does.

[byutennismenace]

That was it, SafariTECH. Thanks a bunch. It was actually dreamweaver that made it a string for some reason.

Anyway, thanks everyone for your help. It is appreciated.
Ben