Skip to main content
C
CFmonger
Inspiring
March 4, 2009
Answered

Problem with my innerjoin. please help

  • March 4, 2009
  • 2 replies
  • 1045 views
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
This topic has been closed for replies.
Correct answer The_ScareCrow
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

2 replies

T
The_ScareCrowCorrect answer
Inspiring
March 4, 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
C
CFmongerAuthor
Inspiring
March 4, 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:
<!--- 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
T
The_ScareCrow
Inspiring
March 5, 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
E
emmim44
Inspiring
March 4, 2009
Your between if statement is wrong....look at your code without if statement it is scramled....
C
CFmongerAuthor
Inspiring
March 4, 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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices