Skip to main content
April 23, 2013
Answered

Data type mismatch in criteria expression

  • April 23, 2013
  • 1 reply
  • 1507 views

Hey Folks. Having an issue with updating the database. here is the code plus error

--- UPDATE FUNCTION ---

<cfquery datasource="#APPLICATION.dataSource#">

UPDATE Company

SET CompanyName='#FORM.CompanyName#', Address='#FORM.Address#', City='#FORM.City#', Province='#FORM.Province#', Phone1='#FORM.Phone1#', Website='#FORM.Website#'

WHERE UserID=<cfqueryparam value="#SESSION.UserID#" cfsqltype="cf_sql_numeric">

AND CompanyID=<cfqueryparam value="#FORM.CompanyID#" cfsqltype="cf_sql_numeric">

</cfquery>

--- ERROR ---

Error Executing Database Query.
Data type mismatch in criteria expression.

The error occurred in C:\ColdFusion9\wwwroot\Companies\logged\companies\company_process.cfm: line 13

11 : Phone1='#FORM.Phone1#', Website='#FORM.Website#'
12 : WHERE UserID=<cfqueryparam value="#SESSION.UserID#" cfsqltype="cf_sql_numeric">
13 : AND CompanyID=<cfqueryparam value="#FORM.CompanyID#" cfsqltype="cf_sql_numeric">
14 : </cfquery>

VENDORERRORCODE 3079
SQLSTATE       
SQL UPDATE Company SET CompanyName='Paul's Place', Address='70 Nassau St', City='Toronto', Province='ON', Phone1='(876)-544-5768', Website=''
WHERE UserID= (param 1) AND CompanyID= (param 2)
DATASOURCE Business

I have used the cfqueryparam in another process page and it works fine, in fact the code is exact minus the table names and such. I ran a test to see what values comes up from the form and everythign is correct, even the userID and CompanyID come back with the correct numbers. So i'm not sure what it is. Hope someone can see something i don't.

Thanks

    This topic has been closed for replies.
    Correct answer WolfShade

    Just tried it and some result. What i don't understand is i'm using the same code (minus values) in another process page and it works perfectly.

    Datatype: UserID = Number and CompanyID is autoNumber (MySQL)


    I occasionally get issues like this, and it always turns out to be something really, really simple that I just overlooked the first thousand-and-a-half times.  Frustrating!

    I don't see it, either. 

    ^_^

    PS.  Are you absolutley positively sure that the value is a number, no spaces or other spurious stuff?

    1 reply

    WolfShade
    Legend
    April 23, 2013

    Have you tried changing the SQL type from numeric to integer?

    ^_^

    PS.  You really should be using CFQUERYPARAM on all the form inputs.

    April 23, 2013

    Tried changing it and same error.

    WolfShade
    Legend
    April 23, 2013

    On line 13, what about using the val() function?

    What is datatype in table?