6 Replies Latest reply on Mar 5, 2009 3:49 PM by CFmonger

    Problem with my innerjoin. please help

    CFmonger
      Hello;
      I am trying to write kind of a complicated page and I am getting an error in my query. I do have another question for this query that I can't get to work, but lets do this one problem at a time.

      This is the error I get when going directly to this page:
      Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Projects.CategoryID Categories.Name'.
      The error occurred on line 12.

      This is my code:
      <!--- This is the query with the error, It runs the project records and either brings them all up or just those in the category chosen from another page. --->
      <cflock timeout="10" type="exclusive" scope="application">
      <cfquery name="getProjects" datasource="#APPLICATION.dataSource#">
      SELECT Projects.Name AS PName, Projects.ProjectID AS ID, Projects.CategoryID
      Categories.Name AS CName, Categories.CategoryID
      FROM Projects INNER JOIN Categories ON projects.CategoryID = Categories.CategoryID
      <cfif StructKeyExists(url, 'CategoryID')>
      WHERE Categories.CategoryID = <cfqueryparam CFSQLType="CF_SQL_INTEGER" value="#URL.CategoryID#" />
      AND Categories.CategoryID = Projects.CategoryID
      ORDER BY Projects.Name
      <cfelse>
      ORDER BY Categories.Name
      </cfif>
      </cfquery>
      <cfset rowsPerPage = 6>
      <cfparam name="URL.startRow" default="1" type="numeric">
      <cfset totalRows = getProjects.recordCount>
      <cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)>
      <cfset startRowNext = endRow + 1>
      <cfset startRowBack = URL.startRow - rowsPerPage>
      </cflock>
      <!--- End project file code --->
      <!--- This code runs the success in updating your #record# --->
      <cfif isDefined("URL.ID")>
      <cfquery name="zlnbbXX" datasource="#APPLICATION.dataSource#" maxrows="1">
      SELECT Projects.Name AS PName, Projects.ProjectID AS ID, Projects.CategoryID
      WHERE ProjectID = #URL.ID#
      </cfquery>
      </cfif>
      <!--- end code--->
      <head>
      </head>
      <body>
      <!--- code for Success update record --->
      <cfif isDefined("URL.RecordID")>
      <cfoutput query="zlnbbXX">
      <font color="##990000" face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Updates Have Successfully Been Applied to:</b><br><u>#PName#</u></font><br></cfoutput></cfif>
      <!--- end success --->
      <!-- records and next / prev nav --->
      <cfoutput>
      Displaying <b>#URL.startRow#</b> to <b>#endRow#</b> of <b>#totalRows#</b> Records</font>
      <cfset urlVars = "">
      <cfif startRowBack GT 0>
      <cfset urlVars = "startRow=#startRowBack#">
      </cfif>
      <cfif structKeyExists(url, 'categoryID')>
      <cfset urlVars = urlVars & "categoryID=#categoryID#">
      </cfif>
      <a href="#CGI.script_name#?#urlVars#" class="nav">< Previous Records</a>

      <cfset urlVars = "" >
      <cfif startRowNext lte totalRows>
      <cfset urlVars = "startRow=#startRowNext#">
      </cfif>
      <cfif structKeyExists(url, 'categoryID')>
      <cfset urlVars = urlVars & "categoryID=#categoryID#">
      </cfif>
      <a href="#CGI.script_name#?#urlVars#" class="nav">Next ></a>
      </cfoutput>
      <!--- end next / prev code --->
      <!-- records to edit --->
      <cfloop query="getProjects" startRow="#URL.startRow#" endrow="#endRow#"><cfset class = iif(getProjects.currentRow mod 2 eq 0, " 'DataA' ", " 'DataB' ")>
      <cfoutput>
      #PName#
      #CName#
      <form name="myform" action="Action.cfm" method="post">
      <input type="hidden" name="ID" value="#ID#">
      <input type="submit" ... More code here>
      </cfoutput>
      </cfloop>


      This query does a few things.
      1. if you go to this page directly, it shows all the projects title so you can next / prev through the records and edit the record you want.
      2. I am trying to make it so that if you come from another page that edits all the categories for this section, it will only bring up the projects under that category. It did that, but when you next / prev through it, it brings up all the records.
      3. After you add or update a record, when you are sent back to this page, it tells you, "Your updates were successful for #title of project#"

      I commented out the code, I know there is a couple issues here and I need help figuring them out.

      CFmonger
        • 1. Re: Problem with my innerjoin. please help
          emmim44 Level 1
          Your between if statement is wrong....look at your code without if statement it is scramled....
          • 2. Re: Problem with my innerjoin. please help
            The ScareCrow Level 1
            In the query "getProjects"

            You are missing the comma between , Projects.CategoryID Categories.Name AS CName, (no comma after CategoryID)
            The AND condition in the query is redundant, this is done in the join condition.
            To fix problem 2 without seeing the actual url I can't help. But I would suggest that the catergoyid is not there

            In query "zlnbbXX" there is no from clause

            Ken
            • 3. Re: Problem with my innerjoin. please help
              CFmonger Level 1
              I don't get how I scrambled it. (I have been looking at this code for a long time) How would I put it right so it can perform both functions? next previous for either all the records, or next previous for just the records in the catregory you chose from another page?

              CFmonger
              • 4. Problem with my innerjoin. please help
                CFmonger Level 1
                I fixed it so it isn't throwing errors now. BUt it also isn't doing what I need it to do. Now I am down to 2 errors left.

                1. when you update, or add a record, the code on this page doesn't catch it and put up the "you have successfully updated ..."

                2. If you go to this page and just want to see the records from just the category you chose, lets say I am looking at all the projects in the category NYS made widgets, when you hit next, it throws an error, because there are more then 5 records for the page.

                This is how the code looks now:
                <!--- This is the query with the error, It runs the project records and either brings them all up or just those in the category chosen from another page. --->
                <cflock timeout="10" type="exclusive" scope="application">
                <cfquery name="getProjects" datasource="#APPLICATION.dataSource#">
                SELECT Projects.Name AS PName, Projects.ProjectID AS ID, Projects.CategoryID,
                Categories.Name AS CName, Categories.CategoryID
                FROM Projects INNER JOIN Categories ON Projects.CategoryID = Categories.CategoryID
                <cfif StructKeyExists(url, 'CategoryID')>
                WHERE Categories.CategoryID = <cfqueryparam CFSQLType="CF_SQL_INTEGER" value="#URL.CategoryID#" />
                AND Categories.CategoryID = Projects.CategoryID
                ORDER BY Projects.Name
                <cfelse>
                ORDER BY Categories.Name
                </cfif>
                </cfquery>
                <cfset rowsPerPage = 6>
                <cfparam name="URL.startRow" default="1" type="numeric">
                <cfset totalRows = getProjects.recordCount>
                <cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)>
                <cfset startRowNext = endRow + 1>
                <cfset startRowBack = URL.startRow - rowsPerPage>
                </cflock>
                <!--- End project file code --->
                <!--- This code runs the success in updating your #record# --->
                <cfif isDefined("URL.ProjectID")>
                <cfquery name="zlnbbXX" datasource="#APPLICATION.dataSource#" maxrows="1">
                SELECT Projects.Name AS PName, Projects.ProjectID AS ID, Projects.CategoryID
                FROM Projects
                WHERE ProjectID = #URL.ID#
                </cfquery>
                </cfif>
                <!--- end code--->
                <head>
                </head>
                <body>
                <!--- code for Success update record --->
                <cfif isDefined("URL.RecordID")>
                <cfoutput query="zlnbbXX">
                <font color="##990000" face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Updates Have Successfully Been Applied to:</b><br><u>#PName#</u></font><br></cfoutput></cfif>
                <!--- end success --->
                <!-- records and next / prev nav --->
                <cfoutput>
                Displaying <b>#URL.startRow#</b> to <b>#endRow#</b> of <b>#totalRows#</b> Records</font>
                <cfset urlVars = "">
                <cfif startRowBack GT 0>
                <cfset urlVars = "startRow=#startRowBack#">
                </cfif>
                <cfif structKeyExists(url, 'categoryID')>
                <cfset urlVars = urlVars & "categoryID=#categoryID#">
                </cfif>
                <a href="#CGI.script_name#?#urlVars#" class="nav">< Previous Records</a>

                <cfset urlVars = "" >
                <cfif startRowNext lte totalRows>
                <cfset urlVars = "startRow=#startRowNext#">
                </cfif>
                <cfif structKeyExists(url, 'categoryID')>
                <cfset urlVars = urlVars & "categoryID=#categoryID#">
                </cfif>
                <a href="#CGI.script_name#?#urlVars#" class="nav">Next ></a>
                </cfoutput>
                <!--- end next / prev code --->
                <!-- records to edit --->
                <cfloop query="getProjects" startRow="#URL.startRow#" endrow="#endRow#"><cfset class = iif(getProjects.currentRow mod 2 eq 0, " 'DataA' ", " 'DataB' ")>
                <cfoutput>
                #PName#
                #CName#
                <form name="myform" action="Action.cfm" method="post">
                <input type="hidden" name="ID" value="#ID#">
                <input type="submit" ... More code here>
                </cfoutput>
                </cfloop>
                </body>

                This is my code for the update database. It is supposed to be passing an id if there is a new record addd to teh db so it will catch the code on the other page and post out the "success, you have updated your..."

                <cfif form.id eq 0>
                <cfquery datasource="#APPLICATION.dataSource#">
                INSERT INTO Projects
                (Name, Body, CategoryID)
                VALUES
                (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
                <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">,
                <cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">)
                </cfquery>
                <cflocation url="project-manager.cfm?ID=#Form.ID#">
                <cfelse>
                <cfquery datasource="#APPLICATION.dataSource#">
                UPDATE Projects
                SET
                Projects.Name=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">,
                Projects.Body=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.PDSeditor#">,
                Projects.CategoryID=<cfqueryparam value="#form.CategoryID#" cfsqlType="CF_SQL_INTEGER">
                WHERE ProjectID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
                </cfquery>
                <cfquery name="ZFetchID" datasource="#APPLICATION.dataSource#">
                SELECT ProjectID
                FROM Projects
                WHERE Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.proj_name#">
                </cfquery>
                <cflocation url="project-manager.cfm?id=#ZFetchID.ProjectID#">
                </cfif>

                Maybe I missed something?
                Also, my previous button does not disappear when there are no records to go back to. It is always there.

                Thank you

                CFmonger
                • 5. Re: Problem with my innerjoin. please help
                  The ScareCrow Level 1
                  All I can suggest is to break your page down into sections and get each section working before moving onto the next.
                  That is

                  Display all records
                  Display all records for the passed category
                  Include paging
                  Include updating a record.

                  If your using CF8 may I suggest you look at the cfgrid tag.

                  Ken
                  • 6. Re: Problem with my innerjoin. please help
                    CFmonger Level 1
                    I did break it down. I fixed a lot of it. I am having problems with a next-n feature. I made a new post. I was going to try and address that issue first, then I think I have one more. I have been working on it today.

                    Thank you for the input. Much appreciated.

                    CFmonger