Problem with my innerjoin. please help

Report · Mar 04, 2009

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:

<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>


<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>

<head>
</head>
<body>

<cfif isDefined("URL.RecordID")>
<cfoutput query="zlnbbXX">
Updates Have Successfully Been Applied to: #PName# </cfoutput></cfif>


<cfoutput>
Displaying #URL.startRow# to #endRow# of #totalRows# Records
<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>


<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

Report · Mar 04, 2009

Your between if statement is wrong....look at your code without if statement it is scramled....

Report · Mar 04, 2009

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

Report · Mar 04, 2009

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

Report · Mar 04, 2009

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:

<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>


<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>

<head>
</head>
<body>

<cfif isDefined("URL.RecordID")>
<cfoutput query="zlnbbXX">
Updates Have Successfully Been Applied to: #PName# </cfoutput></cfif>


<cfoutput>
Displaying #URL.startRow# to #endRow# of #totalRows# Records
<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>


<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

Report · Mar 05, 2009

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

Report · Mar 05, 2009

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

Adobe Community

Problem with my innerjoin. please help

1 Correct answer