ASP & SQL Question... - 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)

ASP & SQL Question...

Author

Topic

kwilliams

194 Posts

Posted - 2004-07-30 : 11:19:12

I have a search & results page that uses a simple form to gather up to 3 form elements (Search Type, Category, and Keywords) from users to search 3 DB tables in SQL Server 2k.

PROBLEM:
The results section (5) is only displaying one of two entries located in dbo.BulletinBoard, and none of the records from dbo.Replies when a generic entry is made with no Search Type, Category, or Keywords. So the Select statement comes through as:

SELECT *  FROM dbo.BulletinBoard AS BB JOIN dbo.Replies AS RE ON BB.Post_ID = RE.Post_ID WHERE (BB.Topic LIKE '' OR BB.Post LIKE '') OR (RE.Topic LIKE '' OR RE.Post LIKE '') ORDER BY BB.Post_ID ASC, BB.DateTime DESC

If I split up rsPosts into two Recordsets, and add another Repeat statment, all of the results do come through. So I think that the problem lies somewhere in the Joined Select Statement. If anyone has any ideas of what I'm doing wrong, it would be greatly appreciated. I've included all of the referenced code below. Thanks.

KWilliams

This is how I set it up:

1) This section assigns variable names to the 3 form elements. The 4th form element is simply equal to true when the form is submitted to activate the select statement:

<%
//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"));
%>

2) This section checks for an entry for the "Category", and assigns the appropriate additional code for rsPosts:

<%
//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 = "(BB.Cat_ID = '" + Cat_ID + "') OR (RE.Cat_ID = '" + Cat_ID + "') AND ";}
%>

3) This section contains the actual Select statement for rsPosts (THIS IS WHERE THE PROBLEM LIES):

<%
//If SearchType = Posts
if (Search == "true" && SearchType == "Posts") {
var rsPosts = Server.CreateObject("ADODB.Recordset");
rsPosts.ActiveConnection = MM_strConn_Bulletin_STRING;
rsPosts.Source = "SELECT *  FROM dbo.BulletinBoard AS BB JOIN dbo.Replies AS RE ON BB.Post_ID = RE.Post_ID WHERE " + Cat_Choice + "(BB.Topic LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR BB.Post LIKE '%"+ Keywords.replace(/'/g, "''") + "%') OR (RE.Topic LIKE '%"+ Keywords.replace(/'/g, "''") + "%' OR RE.Post LIKE '%"+ Keywords.replace(/'/g, "''") + "%') ORDER BY BB.Post_ID ASC, BB.DateTime DESC";
rsPosts.CursorType = 0;
rsPosts.CursorLocation = 2;
rsPosts.LockType = 1;
rsPosts.Open();
var rsPosts_numRows = 0;
}
%>

4) This section is the Repeat statement:

<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
rsPosts_numRows += Repeat1__numRows;
%>

5) This section is where the results are written in the body of the page:

<%
//If SearchType = Posts
if (Search == "true" && SearchType == "Posts") {
Response.Write("<strong>Posts</strong><br>");
while ((Repeat1__numRows-- != 0) && (!rsPosts.EOF)) {
Response.Write("<strong>Topic: </strong>" + rsPosts.Fields.Item("Topic").Value + "<br>");
Response.Write("<strong>Post: </strong>" + rsPosts.Fields.Item("Post").Value + "<br><br>");
  Repeat1__index++;
  rsPosts.MoveNext();
}
}
%>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources