Copy link to clipboard
Copied
Hello;
I'm trying to innerjoin these 2 tables in my query for a page that will allow you to add / edit records. Right now, it's telling me I have a data mismatch. I don't see where I went wrong. Can someone help me out?
This is my code:
<cfparam name="url.CategoryID" type="integer" default="0">
<cfparam name="subID" type="integer" default="#url.CategoryID#">
<cfparam name="subName" default="">
<cfparam name="CategoryID" default="">
<cfparam name="Name" default="">
<cfif url.CategoryID GT 0>
<cfquery name="categRec" dataSource="#APPLICATION.dataSource#">
SELECT merchSubCat.subName, merchSubCat.subID, Categories.CategoryID, Categories.Name
FROM merchSubCat
INNER JOIN Categories
ON merchSubCat.CategoryID = Categories.CategoryID
WHERE merchSubCat.subID = <cfqueryparam value="#url.CategoryID#" cfsqltype="cf_sql_integer">
</cfquery>
<!--- if the record was found, store the values --->
<cfif categRec.RecordCount EQ 1>
<cfset CategoryID = categRec.subID>
<cfset subName = categRec.subName>
<cfset CategoryID = categRec.CategoryID>
<cfset Name = categRec.Name>
</cfif>
</cfif>
this is my error:
Error Executing Database Query. | |
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Type mismatch in expression. | |
The error occurred in C:\Websites\187914kg3\admin\cms\merchant\merchSub-edit.cfm: line 16 | |
14 : INNER JOIN Categories 15 : ON merchSubCat.CategoryID = Categories.CategoryID 16 : WHERE merchSubCat.subID = <cfqueryparam value="#url.CategoryID#" cfsqltype="cf_sql_integer"> 17 : </cfquery> 18 : |
I don't see what I did wrong, can another pair of eyes see where I missed something?
Thank you
I think you just want to use the URL.CategoryID parameter:
<cfselect enabled="No" name="CategoryID" size="1" class="smallText" multiple="no" query="catList" value="CategoryID" display="cat_name" queryPosition="below" selected="#URL.CategoryID #">
<option value="">--Select a Category--</option>
</cfselect>
Ken Ford
Copy link to clipboard
Copied
FROM merchSubCat
INNER JOIN Categories
ON merchSubCat.CategoryID = Categories.CategoryID
WHERE merchSubCat.subID = <cfqueryparam
value="#url.CategoryID#" cfsqltype="cf_sql_integer">
What are the data types of the columns:
merchSubCat.CategoryID
Categories.CategoryID
merchSubCat.subID
Copy link to clipboard
Copied
it's an access database..
merchSubCat.CategoryID - text
Categories.CategoryID - auto number
merchSubCat.subID - auto number
should I make merchSubCat.CategoryID a number field?
Copy link to clipboard
Copied
I changed it to number, and it works now.
I have one other problem.. I'm trying to use a drop down menu in this form to edit a record. I want the drop down to show what is being used in the database when you go to edit a record. It runs on one query, to populate the select, but it pulls info from another and this isn't working. Actually, it's the innerjoin I just made.. can you help me?
<!--- query that runs the select --->
<cfquery name="catList" datasource="#APPLICATION.dataSource#">
SELECT DISTINCT merchCategory.CategoryID, merchCategory.CatName AS cat_name
FROM merchCategory
ORDER BY CatName
</cfquery>
<cfselect enabled="No" name="CategoryID" size="1" class="smallText" multiple="no" query="catList" value="CategoryID" display="cat_name" queryPosition="below" selected="#categRec.Name#">
<option value="">--Select a Category--</option>
</cfselect>
As you see, in the selected part of the tag, I'm trying to pull info from the other query we just fixed. I know this is wrong, how do I do this?
Copy link to clipboard
Copied
I think you just want to use the URL.CategoryID parameter:
<cfselect enabled="No" name="CategoryID" size="1" class="smallText" multiple="no" query="catList" value="CategoryID" display="cat_name" queryPosition="below" selected="#URL.CategoryID #">
<option value="">--Select a Category--</option>
</cfselect>
Ken Ford
Copy link to clipboard
Copied
Thank you so much! That worked! I was over thinking it.
Copy link to clipboard
Copied
You probably have the wrong type of CFSQLTYPE here:
WHERE merchSubCat.subID = <cfqueryparam value="#url.CategoryID#" cfsqltype="cf_sql_integer">
Try cf_sql_numeric instead:
WHERE merchSubCat.subID = <cfqueryparam value="#url.CategoryID#" cfsqltype="cf_sql_numeric">
Ken Ford