PDA

Click to See Complete Forum and Search --> : ASP Query Question...

kwilliams
08-03-2004, 02:42 PM
Using a SQL Server 2k backend with full-index searching, I have created a search page with ASP that needs to pull results from 3 DB tables with the following 3 optional factors: Search Type, Category, and Keywords. But even though it's pulling the Category (Cat_ID) properly, the Select statement is not filtering the results properly for some reason. I've tested this query in the query analyzer, and it works fine. Does anyone see what I could be doing incorrectly? I've included the entire page's code at the bottom of this post as a reference. Thanks for any & all help.

<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/strConn_Bulletin.asp" -->
<%
//Assign search form variables
var SearchType = String(Request.Form("rbSearchType"));
var Cat_ID = String(Request.Form("selectCat_ID"));
var Keywords = String(Request.Form("txtKeywords"));
var Search = String(Request.Form("hfSearch"));
%>
<%
//If Cat_ID is empty...
if (Search == "true" && (Cat_ID == "" || Cat_ID == "undefined")) {
var Cat_Choice = "";}
//Else if Cat_ID is not empty...
else if (Search == "true" && Cat_ID != "") {
var Cat_Choice = "Cat_ID = '" + Cat_ID + "' AND ";}
%>
<%
//Select category dynamic list properties
var rsCategories = Server.CreateObject("ADODB.Recordset");
rsCategories.ActiveConnection = MM_strConn_Bulletin_STRING;
rsCategories.Source = "SELECT * FROM dbo.Categories ORDER BY Category";
rsCategories.CursorType = 0;
rsCategories.CursorLocation = 2;
rsCategories.LockType = 1;
rsCategories.Open();
var rsCategories_numRows = 0;
%>
<%
//If SearchType = Posts
if (Search == "true" && (SearchType == "Posts" || SearchType == "All")) {
var rsBulletinBoard = Server.CreateObject("ADODB.Recordset");
rsBulletinBoard.ActiveConnection = MM_strConn_Bulletin_STRING;
rsBulletinBoard.Source = "SELECT * FROM dbo.BulletinBoard WHERE " + Cat_Choice + "Topic LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR Post LIKE '%"+ Keywords.replace(/'/g, "''") + "%' ORDER BY DateTime DESC";
rsBulletinBoard.CursorType = 0;
rsBulletinBoard.CursorLocation = 2;
rsBulletinBoard.LockType = 1;
rsBulletinBoard.Open();
var rsBulletinBoard_numRows = 0;
}
%>
<%
//If SearchType = Replies
if (Search == "true" && (SearchType == "Replies" || SearchType == "All")) {
var rsReplies = Server.CreateObject("ADODB.Recordset");
rsReplies.ActiveConnection = MM_strConn_Bulletin_STRING;
rsReplies.Source = "SELECT * FROM dbo.Replies WHERE " + Cat_Choice + "Topic LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR Post LIKE '%"+ Keywords.replace(/'/g, "''") + "%' ORDER BY DateTime DESC";
rsReplies.CursorType = 0;
rsReplies.CursorLocation = 2;
rsReplies.LockType = 1;
rsReplies.Open();
var rsReplies_numRows = 0;
}
%>
<%
//If SearchType = Members
if (Search == "true" && (SearchType == "Members" || SearchType == "All")) {
var rsMembers = Server.CreateObject("ADODB.Recordset");
rsMembers.ActiveConnection = MM_strConn_Bulletin_STRING;
rsMembers.Source = "SELECT * FROM dbo.Members WHERE Username LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR LName LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR FName LIKE '%"+ Keywords.replace(/'/g, "''") + "%' ORDER BY LName ASC";
rsMembers.CursorType = 0;
rsMembers.CursorLocation = 2;
rsMembers.LockType = 1;
rsMembers.Open();
var rsMembers_numRows = 0;
}
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
rsBulletinBoard_numRows += Repeat1__numRows;
%>
<%
var Repeat2__numRows = -1;
var Repeat2__index = 0;
rsReplies_numRows += Repeat2__numRows;
%>
<%
var Repeat3__numRows = -1;
var Repeat3__index = 0;
rsMembers_numRows += Repeat3__numRows;
%>
<html>
<head>
<style type="text/css">
<!--
.style1 {color: #FF0000}
-->
</style>
</head>
<body>
<table width="75%" border="0">
<tr>
<td><h1>Bulletin Board - Search</h1></td>
<td valign="top"><div align="right"><a href="javascript:history.back()"><img src="Images/back_icon.gif" width="40" height="22" alt="Back to Previous Page" border="0"></a></div></td>
</tr>
<tr valign="top">
<td colspan="2" class="small"><p class="small"><a class="small" href="bulletinboard.asp">Home</a> | <a class="small" href="profile.asp">Profile</a> | <a class="small" href="account.asp">Account</a> | <a class="small" href="members.asp">Members</a> | <span class="style1">Search</span> | <a class="small" href="faqs.asp">FAQ's</a></p>
<p>&nbsp;</p></td>
</tr>
<tr valign="top">
<td colspan="2">
<form name="form1" method="post" action="search.asp">
<table width="100%" border="0">
<tr>
<td width="32%" bgcolor="#660000"><div align="right">
<h3>Search type:</h3>
</div></td>
<td colspan="2"><input name="rbSearchType" type="radio" value="All" checked>
All&nbsp;&nbsp;
<input name="rbSearchType" type="radio" value="Posts">
Posts&nbsp;&nbsp;
<input name="rbSearchType" type="radio" value="Replies">
Replies&nbsp;&nbsp;
<input name="rbSearchType" type="radio" value="Members">
Members</td>
</tr>
<tr>
<td bgcolor="#660000"><div align="right">
<h3>Categories:</h3>
</div></td>
<td width="21%"><select name="selectCat_ID" title="<%=(rsCategories.Fields.Item("Category").Value)%>">
<option selected value="">- All Categories -</option>
<%
while (!rsCategories.EOF) {
%>
<option value="<%=(rsCategories.Fields.Item("Cat_ID").Value)%>"><%=(rsCategories.Fields.Item("Category").Value)%></option>
<%
rsCategories.MoveNext();
}
if (rsCategories.CursorType > 0) {
if (!rsCategories.BOF) rsCategories.MoveFirst();
} else {
rsCategories.Requery();
}
%>
</select></td>
<td width="47%">&nbsp;</td>
</tr>
<tr>
<td bgcolor="#660000"><div align="right">
<h3>Keyword(s):</h3>
</div></td>
<td><p>
<input name="txtKeywords" type="text" id="txtKeywords" size="30" maxlength="30">
</p></td>
<td><input type="hidden" name="hfSearch" value="true">
<input type="submit" name="Submit" value="Search" id="formButton"></td>
</tr>
</table>
</form>
<h1>Search Results</h1>
<p>Search Type: <%=(SearchType)%><br>
Category Selected: <%=(Cat_ID)%><br>
Keyword(s) Entered: <%=(Keywords)%><br>
Cat_Choice: FF<%=(Cat_Choice)%>FF</p>
<p>
<%
//If SearchType = Posts or All
if (Search == "true" && (SearchType == "Posts" || SearchType == "All")) {
Response.Write("<h2>Posts</strong></h2><br>");
while ((Repeat1__numRows-- != 0) && (!rsBulletinBoard.EOF)) {
Response.Write("<strong>Topic: </strong>" + rsBulletinBoard.Fields.Item("Topic").Value + "<br>");
Response.Write("<strong>Cat_ID: </strong>" + rsBulletinBoard.Fields.Item("Cat_ID").Value + "<br>");
Response.Write("<strong>Post: </strong>" + rsBulletinBoard.Fields.Item("Post").Value + "<br><br>");
Repeat1__index++;
rsBulletinBoard.MoveNext();
}
}
%>
</p>
<p>
<%
//If SearchType = Replies or All
if (Search == "true" && (SearchType == "Replies" || SearchType == "All")) {
Response.Write("<h2>Replies</h2><br>");
while ((Repeat2__numRows-- != 0) && (!rsReplies.EOF)) {
Response.Write("<strong>Topic: </strong>" + rsReplies.Fields.Item("Topic").Value + "<br>");
Response.Write("<strong>Cat_ID: </strong>" + rsReplies.Fields.Item("Cat_ID").Value + "<br>");
Response.Write("<strong>Post: </strong>" + rsReplies.Fields.Item("Post").Value + "<br><br>");
Repeat2__index++;
rsReplies.MoveNext();
}
}
%>
</p>
<p>
<%
//If SearchType = Members
if (Search == "true" && (SearchType == "Members" || SearchType == "All")) {
Response.Write("<h2>Members</h2><br>");
while ((Repeat3__numRows-- != 0) && (!rsMembers.EOF)) {
Response.Write("<strong>Username: </strong>" + rsMembers.Fields.Item("Username").Value + "<br>");
Response.Write("<strong>LName: </strong>" + rsMembers.Fields.Item("LName").Value + "<br>");
Response.Write("<strong>FName: </strong>" + rsMembers.Fields.Item("FName").Value + "<br><br>");
Repeat3__index++;
rsMembers.MoveNext();
}
}
%>
</p>
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td width="200%" colspan="2"><div align="center">
<script language="JavaScript" src="http://dgintrasrv1/footer.js">
</script>
</div></td>
</tr>
</table>
</body>
</html>
<%Session.Abandon()%>
<%
rsCategories.Close();
%>
<%
rsBulletinBoard.Close();
%>
<%
rsReplies.Close();
%>
<%
rsMembers.Close();
%>
kwilliams
08-03-2004, 02:52 PM
Hello again,

Ofcourse, I figured out the answer on my own after further testing. I simply needed to add opening & closing parenthesis on the "OR" sections of the Select statement. So it went from:

rsReplies.Source = "SELECT * FROM dbo.Replies WHERE " + Cat_Choice + "Topic LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR Post LIKE '%"+ Keywords.replace(/'/g, "''") + "%' ORDER BY DateTime DESC";

..to:
rsReplies.Source = "SELECT * FROM dbo.Replies WHERE " + Cat_Choice + "(Topic LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR Post LIKE '%"+ Keywords.replace(/'/g, "''") + "%') ORDER BY DateTime DESC";

Thanks anyway:)