I haven't looked at your code in detail, but as a first step, I would turn
on debugging, see exactly what query is being generated by your code and
what form values are being returned, and then try running that query
directly in your db tool.
Actually one issue on my quick scan is:
<cfoutput query="qGetDept">
<option value="2">#qGetDept.DepartmentName#</option>
</cfoutput>
This form field will only return 1 or 2, which seems like it probably won't
match on department name in this code:
<CFIF FORM.DepartmentName IS NOT " ">
AND DepartmentName = '#FORM.DepartmentName#'
</CFIF>
-----Original Message-----
From: Gary Hayden-Sofio [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 09, 2001 8:50 AM
To: CF-Talk
Subject: dynamic query construction
I am building an application that will allow an end-viewer to search a staff
directory based upon either partial or full last name (a text field), or
department (a dynamically populated select menu).
I am trying to initiate and post the results of this dynamic query using
end-viewer input from a form that exists on the same page as the query.
Though new to ColdFusion, I believe I am close to having the query
constructed properly. In fact, I don't get an error message when running the
page (http://134.29.247.12/facultyStaffDirectory/index.cfm) AND performing
a SEARCH.
Unfortunately, I get no results from the SEARCH either.
I am not sure how to re-initiate a query with data from a form that exists
on the same page as the query. And I am stumped... all help is very much
appreciated.
Gary H-S
<!--- query that dynamically fills select menu with department names --->
<CFQUERY NAME="qGetDept" DATASOURCE="facultyStaffDirectory">
SELECT DepartmentName
FROM tblRefDepartments
ORDER BY DepartmentName
</CFQUERY>
<!---form that allows for search based on partial or full input of lastname,
and builds dynamic select menu from results of qGetDept query --->
<form method="post" action="index.cfm" name="search">
<table width="640" border="0" cellspacing="0" cellpadding="2">
<!--- text field that allows for partial or full input of lastname --->
<tr>
<td width="64"><img src="transparent.gif" width="1" height="1"><img
src="transparent.gif" width="64" height="1" alt=" "></td>
<td width="576">
<div align="right"><font face="Arial, Helvetica, sans-serif" size="2">Search
by <b>last name</b>:</font>
<input type="text" name="Lastname" size="25" maxlength="130">
<input type="submit" name="submit1" value="search">
</div>
</td>
</tr>
<!--- text field that builds dynamic select menu from results of qGetDept
query --->
<tr>
<td width="64"><img src="transparent.gif" width="1" height="1"><img
src="transparent.gif" width="64" height="1" alt=" "></td>
<td width="576">
<div align="right"><font face="Arial, Helvetica, sans-serif" size="2">Search
by <b>department/program</b>:</font>
<select name="DepartmentName">
<option value="1" SELECTED>choose...</option>
<cfoutput query="qGetDept">
<option value="2">#qGetDept.DepartmentName#</option>
</cfoutput>
</select>
<input type="submit" name="submit2" value="search">
</div>
</td>
</tr>
</table>
</form>
<br>
<table width="640" border="0" cellspacing="0" cellpadding="1">
<tr>
<td width="64"><img src="transparent.gif" width="64" height="1" alt=" ">
</td>
<td width="576">
<table cellpadding="0" cellspacing="0" border="0" width="576">
<tr bgcolor="#432684">
<td width="205" height="24"><b><font face="Arial" size="2"
color="#FFFFFF">Name</font></b></td>
<td width="240" height="24"><b><font face="Arial" size="2"
color="#FFFFFF">Title</font></b></td>
<td width="70" height="24"><b><font face="Arial" size="2"
color="#FFFFFF">Phone</font></b></td>
<td width="61" height="24"><b><font face="Arial" size="2"
color="#FFFFFF">Office</font></b></td>
</tr>
</table>
</td>
</tr>
</table>
<!--- the query that makes this whole page go... in theory, anyway --->
<CFQUERY NAME="qGetListing" DATASOURCE="facultyStaffDirectory">
SELECT Lastname, Firstname, Title, Phone, Location, DepartmentName
FROM tblNames
WHERE 0 = 0
<CFIF IsDefined("FORM.Lastname")>
<CFIF FORM.Lastname IS NOT " ">
AND Lastname LIKE '#FORM.Lastname#%'
</CFIF>
</CFIF>
<CFIF IsDefined("FORM.DepartmentName")>
<CFIF FORM.DepartmentName IS NOT " ">
AND DepartmentName = '#FORM.DepartmentName#'
</CFIF>
</CFIF>
ORDER BY Lastname, Firstname
</CFQUERY>
<table width="640" border="0" cellspacing="0" cellpadding="1">
<tr>
<td width="64"><img src="transparent.gif" width="64" height="1" alt=" ">
</td>
<td width="576">
<table cellpadding="0" cellspacing="0" border="0" width="576">
<cfoutput query="qGetListing">
<tr
<cfif qGetListing.currentrow mod 2 eq 0>
bgcolor="DFDFDF"
<cfelse>
bgcolor="FFFFFF"
</cfif>
>
<td width="205" height="20"><font face="Arial"
size="1">#qGetListing.Lastname#, #qGetListing.Firstname#</font></td>
<td width="240" height="20"><font face="Arial"
size="1">#qGetListing.Title#</font></td>
<td width="70" height="20"><font face="Arial"
size="1">#qGetListing..Phone#</font></td>
<td width="61" height="20"><font face="Arial"
size="1">#qGetListing..Location#</font></td>
</tr>
</cfoutput>
</table>
</td>
</tr>
</table>
<br>
<table width="640" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="64"><img src="transparent.gif" width="1" height="1"><img
src="transparent.gif" width="64" height="1" alt=" "></td>
<td width="576">
<font face="Arial, Helvetica, sans-serif" size="1"># of names:
<cfoutput>#qGetListing.RecordCount#</cfoutput></font>
</td>
</tr>
</table>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
