Problem with empty strings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with empty strings

Author

Topic

mel77
Starting Member

2 Posts

Posted - 2002-05-13 : 11:37:08

Hi, I'm new to SQL Server 2000 and I'm trying to create a search page using full-text indexing. I apologize in advance if my question is basic :-) I've searched the forums and other sites but I haven't been able to find out how to fix my problem.

My search form allows users to search by Last name (text box) or Gender (drop down list). Here's the code:

<form method="post" action="results.asp">
<table border="0" cellpadding="2" cellspacing="0" width="100%">
<tr>
<td width="16%"><font size="2" face="Arial"><b>Last Name</b></font></td>
<td width="84%"><input type="text" name="lastname" size="18"></td>
</tr>
<tr>
<td width="16%"><font size="2" face="Arial"><b>Gender</b></font></td>
<td width="84%"><select size="1" name="gender">
<option value="" selected>Select a Gender</option>
<option value="Male">Male</option>
<option value="Female">Female</option>
</select></td>
</tr>
<tr>
<td width="16%"></td>
<td width="84%"><input type="radio" name="method" value="1" checked> <font size="2" face="Arial">Match
ALL criteria above</font><br>
<input type="radio" name="method" value="2"> <font size="2" face="Arial">Match
ANY criteria above</font></td>
</tr>
</table>
<p align="center"><input type="submit" value="Search"> <input type="reset" value="Reset"></p>
</form>

When I fill in both fields, I'm able to get results. However if I want to search by last name only and leave Gender blank, I get this error:

Syntax error in search condition, or empty or null search condition ''. Here's the part of my code for the results.asp page:

<%
Error = false

'Collect form
LastName = TRIM(Request.Form("lastname"))
Gender = TRIM(Request.Form("gender"))
'Check
IF Len(LastName)<1 AND Len(Gender)<1 THEN Error = true
%>
<div align="center">
<TABLE BORDER=0 WIDTH="90%">
<TR>
<TD><FONT FACE="Arial,Helvetica" SIZE=2><CENTER>Search results found for: <b><% If Request.Form("lastname") <> "" Then Response.Write LastName & " " End If %><% If Request.Form("gender") <> "" Then Response.Write Gender & " " End If %></b></CENTER>
</font>
</TD>
</tr>
</TABLE>

<TABLE BORDER=0 WIDTH="90%">
<tr>
<td><br><p align="center"><font face="Arial" size="2"><b><A HREF="search.asp">New Search</A></b></font><br><br></p></td>
</tr>
<TR><TD>
<%
IF NOT Error THEN

Set RS = Server.CreateObject("ADODB.RecordSet")
query = "SELECT * FROM Form_Data WHERE "
if Request.Form("method") = "1" then
query = query & "CONTAINS(LastName, '" & LastName & "') AND CONTAINS(Gender, '" & Gender & "')"
else
query = query & "CONTAINS(LastName, '" & LastName & "') OR CONTAINS(Gender, '" & Gender & "')"
end if
rs.open query, db, 3

%>

I tried setting a default value, such as "%" to empty fields but that didn't work. Is there a default value I can use when my fields are blank? Or is there a way to ignore the field in the sql query when it's blank?

Thanks for any advice/help.
mel77

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-05-13 : 11:54:40

mel,
I had the same problem as you did. I worked around it by assigning a dummy value of "-1" to the "Select an Option" option of the drop down list and tested the value on post. If it was -1 then the criteria wasnt added to the where clause, if it was a valid option then it was added.

hth,
Justin

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-13 : 11:56:48

---- yours
if Request.Form("method") = "1" then
query = query & "CONTAINS(LastName, '" & LastName & "') AND CONTAINS(Gender, '" & Gender & "')"
else
query = query & "CONTAINS(LastName, '" & LastName & "') OR CONTAINS(Gender, '" & Gender & "')"
end if
------

Now I'm assuming Request.Form("method") = "1" means if you want to search by both.
the second part means you only want to search by one.
Lets change this a bit

if LastName <> '' then query = query & "CONTAINS(LastName, '" & LastName & "')"

if gender <> '' then query = query & "CONTAINS(Gender, '" & Gender & "')"
this way if your only searching by gender, the sql sent to the sever won't have the or statement containing last names.

I've done this before... email me if you need more.

Edited by - M.E. on 05/13/2002 11:58:19

mel77
Starting Member

2 Posts

Posted - 2002-05-13 : 15:59:16

Hi, M.E. helped me solve the problem (thank you!) Here's my final code for those who are also looking for help with this type of problem:

search.asp:
<form method="post" action="results.asp">
<table border="0" cellpadding="2" cellspacing="0" width="100%">
<tr>
<td width="16%"><font size="2" face="Arial"><b>Last Name</b></font></td>
<td width="84%"><input type="text" name="lastname" size="18"></td>
</tr>
<tr>
<td width="16%"><font size="2" face="Arial"><b>Gender</b></font></td>
<td width="84%"><select size="1" name="gender">
<option value="" selected>Select a Gender</option>
<option value="Male">Male</option>
<option value="Female">Female</option>
</select></td>
</tr>
</table>
<p align="center"><input type="submit" value="Search"> <input type="reset" value="Reset"></p>
</form>

results.asp:
<%
Error = false

'Collect form
LastName = TRIM(Request.Form("lastname"))
Gender = TRIM(Request.Form("gender"))
'Check
IF Len(LastName)<1 AND Len(Gender)<1 THEN Error = true
%>
<div align="center">
<TABLE BORDER=0 WIDTH="90%">
<TR>
<TD><FONT FACE="Arial,Helvetica" SIZE=2><CENTER>Search results found for: <b><% If Request.Form("lastname") <> "" Then Response.Write LastName & " " End If %><% If Request.Form("gender") <> "" Then Response.Write Gender & " " End If %></b></CENTER>
</font>
</TD>
</tr>
</TABLE>

<TABLE BORDER=0 WIDTH="90%">
<tr>
<td><br><p align="center"><font face="Arial" size="2"><b><A HREF="search.asp">New Search</A></b></font><br><br></p></td>
</tr>
<TR><TD>
<%
IF NOT Error THEN

Set RS = Server.CreateObject("ADODB.RecordSet")
query = "SELECT * FROM Form_Data WHERE "
var = " "
if Gender <> "" then
query = query & var & "CONTAINS(Gender, '" & Gender & "')"
var = " and "
end if
if LastName <> "" then
query = query & var & "CONTAINS(LastName, '" & LastName & "')"
var = " and "
end if
rs.open query, db, 3
%>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources