problem with a query inner-join

[cfsetNewbie]

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

[-__cfSearching__-]

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

[cfsetNewbie]

it's an access database..

merchSubCat.CategoryID - text

Categories.CategoryID - auto number

merchSubCat.subID - auto number

should I make merchSubCat.CategoryID a number field?

[cfsetNewbie]

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?

[Ken Ford · Accepted solution]

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

[cfsetNewbie]

Thank you so much! That worked! I was over thinking it.

[Ken Ford]

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