I have a simple (and crude, I know) query that had been working for many years on my webpage that recently "broke". It searches my database based on country, state, and county. Can someone lend their thoughts and suggestions? I've listed the form and result html with the coldfusion pages below. The error I'm getting when I submit the countrysearch.cfm form with a valid country is:
Error:
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC dBase Driver] Too few parameters. Expected 1.
Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (3:48).
Date/Time: 03/02/11 15:22:45
Browser: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.13 (KHTML, like Gecko) Chrome/9.0.597.107 Safari/534.13
countrysearch.cfm
----
<!DOCTYPE HTML PUBLIC>
<html>
<head>
<title>Research Database Query</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>
<body bgcolor = #ffefdb background="../Images/backgroundsand.gif">
<h2>Search for research items</h2>
<p>
<blockquote>
Enter your search criteria in the fields below.<br>
Country is required; state and county will help to restrict the search.<br>
</p>
<form action="countryresults.cfm" method="get">
<table border = "0">
<tr>
<P>
<td>Country</td>
<td><input name="findcountry"></td>
</tr>
<tr>
<td>State</td>
<td><input name="findstate">
</tr>
<tr>
<td>County</td>
<td><input name="findcounty">
</tr>
<p>
</table>
<p>
<input type="submit" value="Search">
<INPUT type=reset value=Reset>
</blockquote>
</P>
</form>
</body>
</html>
---
countryresults.cfm
<!DOCTYPE HTML PUBLIC>
<cfset findcountry=UCASE(findcountry)>
<cfset findstate=UCASE(findstate)>
<cfset findcounty=UCASE(findcounty)>
<CFIF "#findcountry#" IS "USA">
<CFSET findcountry = "UNITED STATES">
</CFIF>
<CFQUERY NAME="GetRecords" datasource="research">
select catnum, firstname, lastname, country, state, county, colldate, tissues, IDnum
from research
where country = "#findcountry#"
<CFIF "#findstate#" IS NOT "">
and state = "#findstate#"
</CFIF>
<CFIF "#findcounty#" IS NOT "">
and county like "#findcounty#%"
</CFIF>
order by state, county, lastname, firstname, catnum
</cfquery>
<html>
<head>
<title>Query Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>
<body bgcolor=#ffefdb background="../Images/backgroundsand.gif">
<h2>Results Page</h2>
<CFIF 0 is #GetRecords.RecordCount#>
<blockquote>
<cfoutput>
<cfif "#findcountry#" is "">
Please enter a value in the country field.
<cfelse>
We're sorry, there are no records in the database that match "#findcountry#, #findstate#, #findcounty#"
</cfif>
</cfoutput>
</blockquote>
</table>
<p>
<blockquote>
<table border = "0" cellpadding = 7>
<tr>
<td><A href="countrysearch.cfm">Try a New Search</a></td>
<td><A href="INDEX.HTM">Return to Search Page</a></td>
</tr>
</table>
</blockquote>
</P>
<CFELSE>
<p>
<blockquote>
<CFOUTPUT>Your search took #CFQuery.ExecutionTime# milliseconds<br>
and returned
#GetRecords.RecordCount# specimen records.
</CFOUTPUT>
</blockquote>
<P></P>
<table bgcolor = #ffffff BORDER = "0">
<tr>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>Country</FONT></P></th>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>State</FONT></P></th>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>County</FONT></P></th>
<th WIDTH = 115><P align=left><FONT face=Arial color=#0000cc size=1>Last Name</FONT></P></th>
<th WIDTH = 125><P align=left><FONT face=Arial color=#0000cc size=1>First Name</FONT></P></th>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>Collection Date</FONT></P></th>
<th WIDTH = 100><P align=left><FONT face=Arial color=#0000cc size=1>Catalog Number</FONT></P></th>
<th WIDTH = 50><P align=left><FONT face=Arial color=#0000cc size=1>Tissues</FONT></P></th>
</tr>
<cfoutput query="GetRecords">
<tr>
<td><font Face=Arial size="1">#country#</font></td>
<td><font Face=Arial size="1">#state#</font></td>
<td><font Face=Arial size="1">#county#</font></td>
<td><EM><font Face=Arial size="1">#lastname#</font></EM></td>
<td><EM><font Face=Arial size="1">#firstname#</font></EM></td>
<cfif #colldate# is not "1899/12/30">
<td><font Face=Arial size="1">#colldate#</font></td>
<cfelse>
<td> </td>
</cfif>
<td><font Face=Arial size="1">Cat. #catnum#</font></FONT></td>
<cfif #tissues# is 1>
<td><font Face=Arial size="1">ID #IDnum#</font></td>
<cfelse>
</CFIF>
</tr>
</CFOUTPUT>
</table>
<p>
<blockquote>
<table border = "0" cellpadding = 7>
<tr>
<td>Export Records</td>
<td>Print</td>
<td><A href="countrysearch.cfm">New Locality Search</a></td>
<td><A href="INDEX.HTM">Return to Search Page</a></td>
</tr>
</table>
</blockquote>
</P>
</CFIF>
</body>
</html>
Error:
Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC dBase Driver] Too few parameters. Expected 1.
Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (3:48).
Date/Time: 03/02/11 15:22:45
Browser: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.13 (KHTML, like Gecko) Chrome/9.0.597.107 Safari/534.13
countrysearch.cfm
----
<!DOCTYPE HTML PUBLIC>
<html>
<head>
<title>Research Database Query</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>
<body bgcolor = #ffefdb background="../Images/backgroundsand.gif">
<h2>Search for research items</h2>
<p>
<blockquote>
Enter your search criteria in the fields below.<br>
Country is required; state and county will help to restrict the search.<br>
</p>
<form action="countryresults.cfm" method="get">
<table border = "0">
<tr>
<P>
<td>Country</td>
<td><input name="findcountry"></td>
</tr>
<tr>
<td>State</td>
<td><input name="findstate">
</tr>
<tr>
<td>County</td>
<td><input name="findcounty">
</tr>
<p>
</table>
<p>
<input type="submit" value="Search">
<INPUT type=reset value=Reset>
</blockquote>
</P>
</form>
</body>
</html>
---
countryresults.cfm
<!DOCTYPE HTML PUBLIC>
<cfset findcountry=UCASE(findcountry)>
<cfset findstate=UCASE(findstate)>
<cfset findcounty=UCASE(findcounty)>
<CFIF "#findcountry#" IS "USA">
<CFSET findcountry = "UNITED STATES">
</CFIF>
<CFQUERY NAME="GetRecords" datasource="research">
select catnum, firstname, lastname, country, state, county, colldate, tissues, IDnum
from research
where country = "#findcountry#"
<CFIF "#findstate#" IS NOT "">
and state = "#findstate#"
</CFIF>
<CFIF "#findcounty#" IS NOT "">
and county like "#findcounty#%"
</CFIF>
order by state, county, lastname, firstname, catnum
</cfquery>
<html>
<head>
<title>Query Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>
<body bgcolor=#ffefdb background="../Images/backgroundsand.gif">
<h2>Results Page</h2>
<CFIF 0 is #GetRecords.RecordCount#>
<blockquote>
<cfoutput>
<cfif "#findcountry#" is "">
Please enter a value in the country field.
<cfelse>
We're sorry, there are no records in the database that match "#findcountry#, #findstate#, #findcounty#"
</cfif>
</cfoutput>
</blockquote>
</table>
<p>
<blockquote>
<table border = "0" cellpadding = 7>
<tr>
<td><A href="countrysearch.cfm">Try a New Search</a></td>
<td><A href="INDEX.HTM">Return to Search Page</a></td>
</tr>
</table>
</blockquote>
</P>
<CFELSE>
<p>
<blockquote>
<CFOUTPUT>Your search took #CFQuery.ExecutionTime# milliseconds<br>
and returned
#GetRecords.RecordCount# specimen records.
</CFOUTPUT>
</blockquote>
<P></P>
<table bgcolor = #ffffff BORDER = "0">
<tr>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>Country</FONT></P></th>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>State</FONT></P></th>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>County</FONT></P></th>
<th WIDTH = 115><P align=left><FONT face=Arial color=#0000cc size=1>Last Name</FONT></P></th>
<th WIDTH = 125><P align=left><FONT face=Arial color=#0000cc size=1>First Name</FONT></P></th>
<th WIDTH = 150><P align=left><FONT face=Arial color=#0000cc size=1>Collection Date</FONT></P></th>
<th WIDTH = 100><P align=left><FONT face=Arial color=#0000cc size=1>Catalog Number</FONT></P></th>
<th WIDTH = 50><P align=left><FONT face=Arial color=#0000cc size=1>Tissues</FONT></P></th>
</tr>
<cfoutput query="GetRecords">
<tr>
<td><font Face=Arial size="1">#country#</font></td>
<td><font Face=Arial size="1">#state#</font></td>
<td><font Face=Arial size="1">#county#</font></td>
<td><EM><font Face=Arial size="1">#lastname#</font></EM></td>
<td><EM><font Face=Arial size="1">#firstname#</font></EM></td>
<cfif #colldate# is not "1899/12/30">
<td><font Face=Arial size="1">#colldate#</font></td>
<cfelse>
<td> </td>
</cfif>
<td><font Face=Arial size="1">Cat. #catnum#</font></FONT></td>
<cfif #tissues# is 1>
<td><font Face=Arial size="1">ID #IDnum#</font></td>
<cfelse>
</CFIF>
</tr>
</CFOUTPUT>
</table>
<p>
<blockquote>
<table border = "0" cellpadding = 7>
<tr>
<td>Export Records</td>
<td>Print</td>
<td><A href="countrysearch.cfm">New Locality Search</a></td>
<td><A href="INDEX.HTM">Return to Search Page</a></td>
</tr>
</table>
</blockquote>
</P>
</CFIF>
</body>
</html>
