6 Replies Latest reply on May 5, 2010 9:32 AM by cfsetNewbie

    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

        • 1. Re: problem with a query inner-join
          -==cfSearching==- Level 4

          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

          • 2. Re: problem with a query inner-join
            cfsetNewbie Level 1

            it's an access database..

             

            merchSubCat.CategoryID - text

            Categories.CategoryID - auto number

            merchSubCat.subID - auto number

             

            should I make merchSubCat.CategoryID a number field?

            • 3. Re: problem with a query inner-join
              Ken Ford - Fordwebs, LLC Level 2

              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

              • 4. Re: problem with a query inner-join
                cfsetNewbie Level 1

                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?

                • 5. Re: problem with a query inner-join
                  Ken Ford - Fordwebs, LLC Level 2

                  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

                  • 6. Re: problem with a query inner-join
                    cfsetNewbie Level 1

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