Results Page always returns '0 found'

jonrayworth · November 9th, 2003, 11:34 AM

OK, I'm a newbie trying to learn DWMX, especially dynamic pages.
I've tried creating a simple one using the online help & dreamweaver MX for Dummies, but I keep getting 0 records returned.
Here's what I've done.
I created a simple search page, and set the 'action' to open results.asp. Then I created a Record Set in results.asp linked to a database, and when I click on Test, the Test SQL Statement window appears, showing the correct search results.
But when I preview the search page in IE, enter text then click search, the results page opens with 0 records returned.
I know I must be doing something incredibly, stupidly, obviously wrong, but I can't see the wood for the trees!
Please Help!!!

Imar · November 9th, 2003, 12:38 PM

I think Ken would say to this: The error is on line 6.

Then you would reply: How do you know, I didn't post any code. And then Ken would answer: Exactly. ;)

You need to post some code, otherwise it is impossible to see what's going wrong. The Test button in Dreamweaver does not use run-time values, but design-time / default values instead. That's why it shows records when you press the Test button.

If you post the relevant parts of your search page, we can take a look at it and see where the problem is.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

jonrayworth · November 9th, 2003, 02:15 PM

Hi Imar,
Sorry for not posting the code, like I said I'm new to all this! As creating a search/results page is such a simple task (supposedly!), and I'm just following the instructions on the online help, I thought the answer would be glaringly obvious.

Here goes :
Here is the code for search.htm :

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form action="results.asp" name="form1">
  <table width="467" border="0" cellspacing="0" cellpadding="1" bgcolor="#000000">

    <tr>
      <td width="1" height="1"></td>
      <td width="464"></td>
    </tr>
    <tr>
      <td height="102"></td>
      <td valign="top"> <table width="100%" border="0" cellspacing="0" cellpadding="4" bgcolor="#CCCCCC">

          <tr bgcolor="#000000">
            <td height="27" colspan="2" valign="top"><b>Search
              </b></td>
          </tr>
          <tr>
            <td width="81" height="30" align="right"> Keywords: </td>
            <td width="368" valign="top"> <input size="50" name="textfield" value="">
            </td>
          </tr>
          <tr>
            <td height="43" colspan="2" valign="top"> <input type="submit" name="Submit" value="Go">
            </td>
          </tr>
        </table></td>
    </tr>
  </table>
</form>
</body>
</html>

Here is the code for results.asp :

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<%
var rsSearchResults__MMColParam = "1";
if (String(Request.Form("Name")) != "undefined" &&
    String(Request.Form("Name")) != "") {
  rsSearchResults__MMColParam = String(Request.Form("Name"));
}
%>
<%
var rsSearchResults = Server.CreateObject("ADODB.Recordset");
rsSearchResults.ActiveConnection = MM_Products_STRING;
rsSearchResults.Source = "SELECT * FROM products WHERE Name = '"+ rsSearchResults__MMColParam.replace(/'/g, "''") + "' ORDER BY Price ASC";
rsSearchResults.CursorType = 0;
rsSearchResults.CursorLocation = 2;
rsSearchResults.LockType = 1;
rsSearchResults.Open();
var rsSearchResults_numRows = 0;
%>
<%
var Repeat1__numRows = 2;
var Repeat1__index = 0;
rsSearchResults_numRows += Repeat1__numRows;
%>

<%
// *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

// set the record count
var rsSearchResults_total = rsSearchResults.RecordCount;

// set the number of rows displayed on this page
if (rsSearchResults_numRows < 0) { // if repeat region set to all records
  rsSearchResults_numRows = rsSearchResults_total;
} else if (rsSearchResults_numRows == 0) { // if no repeat regions
  rsSearchResults_numRows = 1;
}

// set the first and last displayed record
var rsSearchResults_first = 1;
var rsSearchResults_last = rsSearchResults_first + rsSearchResults_numRows - 1;

// if we have the correct record count, check the other stats
if (rsSearchResults_total != -1) {
  rsSearchResults_numRows = Math.min(rsSearchResults_numRows, rsSearchResults_total);
  rsSearchResults_first = Math.min(rsSearchResults_first, rsSearchResults_total);
  rsSearchResults_last = Math.min(rsSearchResults_last, rsSearchResults_total);
}
%>
<%
// *** Recordset Stats: if we don't know the record count, manually count them

if (rsSearchResults_total == -1) {

  // count the total records by iterating through the recordset
  for (rsSearchResults_total=0; !rsSearchResults.EOF; rsSearchResults.MoveNext()) {
    rsSearchResults_total++;
  }

  // reset the cursor to the beginning
  if (rsSearchResults.CursorType > 0) {
    if (!rsSearchResults.BOF) rsSearchResults.MoveFirst();
  } else {
    rsSearchResults.Requery();
  }

  // set the number of rows displayed on this page
  if (rsSearchResults_numRows < 0 || rsSearchResults_numRows > rsSearchResults_total) {
    rsSearchResults_numRows = rsSearchResults_total;
  }

  // set the first and last displayed record
  rsSearchResults_last = Math.min(rsSearchResults_first + rsSearchResults_numRows - 1, rsSearchResults_total);
  rsSearchResults_first = Math.min(rsSearchResults_first, rsSearchResults_total);
}
%>
<% var MM_paramName = ""; %>
<%
// *** Move To Record and Go To Record: declare variables

var MM_rs = rsSearchResults;
var MM_rsCount = rsSearchResults_total;
var MM_size = rsSearchResults_numRows;
var MM_uniqueCol = "";
    MM_paramName = "";
var MM_offset = 0;
var MM_atTotal = false;
var MM_paramIsDefined = (MM_paramName != "" && String(Request(MM_paramName)) != "undefined");
%>
<%
// *** Move To Record: handle 'index' or 'offset' parameter

if (!MM_paramIsDefined && MM_rsCount != 0) {

  // use index parameter if defined, otherwise use offset parameter
  r = String(Request("index"));
  if (r == "undefined") r = String(Request("offset"));
  if (r && r != "undefined") MM_offset = parseInt(r);

  // if we have a record count, check if we are past the end of the recordset
  if (MM_rsCount != -1) {
    if (MM_offset >= MM_rsCount || MM_offset == -1) { // past end or move last
      if ((MM_rsCount % MM_size) != 0) { // last page not a full repeat region
        MM_offset = MM_rsCount - (MM_rsCount % MM_size);
      } else {
        MM_offset = MM_rsCount - MM_size;
      }
    }
  }

  // move the cursor to the selected record
  for (var i=0; !MM_rs.EOF && (i < MM_offset || MM_offset == -1); i++) {
    MM_rs.MoveNext();
  }
  if (MM_rs.EOF) MM_offset = i; // set MM_offset to the last possible record
}
%>
<%
// *** Move To Record: if we dont know the record count, check the display range

if (MM_rsCount == -1) {

  // walk to the end of the display range for this page
  for (var i=MM_offset; !MM_rs.EOF && (MM_size < 0 || i < MM_offset + MM_size); i++) {
    MM_rs.MoveNext();
  }

  // if we walked off the end of the recordset, set MM_rsCount and MM_size
  if (MM_rs.EOF) {
    MM_rsCount = i;
    if (MM_size < 0 || MM_size > MM_rsCount) MM_size = MM_rsCount;
  }

  // if we walked off the end, set the offset based on page size
  if (MM_rs.EOF && !MM_paramIsDefined) {
    if ((MM_rsCount % MM_size) != 0) { // last page not a full repeat region
      MM_offset = MM_rsCount - (MM_rsCount % MM_size);
    } else {
      MM_offset = MM_rsCount - MM_size;
    }
  }

  // reset the cursor to the beginning
  if (MM_rs.CursorType > 0) {
    if (!MM_rs.BOF) MM_rs.MoveFirst();
  } else {
    MM_rs.Requery();
  }

  // move the cursor to the selected record
  for (var i=0; !MM_rs.EOF && i < MM_offset; i++) {
    MM_rs.MoveNext();
  }
}
%>
<%
// *** Move To Record: update recordset stats

// set the first and last displayed record
rsSearchResults_first = MM_offset + 1;
rsSearchResults_last = MM_offset + MM_size;
if (MM_rsCount != -1) {
  rsSearchResults_first = Math.min(rsSearchResults_first, MM_rsCount);
  rsSearchResults_last = Math.min(rsSearchResults_last, MM_rsCount);
}

// set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount != -1 && MM_offset + MM_size >= MM_rsCount);
%>
<%
// *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

// create the list of parameters which should not be maintained
var MM_removeList = "&index=";
if (MM_paramName != "") MM_removeList += "&" + MM_paramName.toLowerCase() + "=";
var MM_keepURL="",MM_keepForm="",MM_keepBoth="",MM_kee pNone="";

// add the URL parameters to the MM_keepURL string
for (var items=new Enumerator(Request.QueryString); !items.atEnd(); items.moveNext()) {
  var nextItem = "&" + items.item().toLowerCase() + "=";
  if (MM_removeList.indexOf(nextItem) == -1) {
    MM_keepURL += "&" + items.item() + "=" + Server.URLencode(Request.QueryString(items.item()) );
  }
}

// add the Form variables to the MM_keepForm string
for (var items=new Enumerator(Request.Form); !items.atEnd(); items.moveNext()) {
  var nextItem = "&" + items.item().toLowerCase() + "=";
  if (MM_removeList.indexOf(nextItem) == -1) {
    MM_keepForm += "&" + items.item() + "=" + Server.URLencode(Request.Form(items.item()));
  }
}

// create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL + MM_keepForm;
if (MM_keepBoth.length > 0) MM_keepBoth = MM_keepBoth.substring(1);
if (MM_keepURL.length > 0) MM_keepURL = MM_keepURL.substring(1);
if (MM_keepForm.length > 0) MM_keepForm = MM_keepForm.substring(1);
%>
<%
// *** Move To Record: set the strings for the first, last, next, and previous links

var MM_moveFirst="",MM_moveLast="",MM_moveNext="",MM_m ovePrev="";
var MM_keepMove = MM_keepBoth; // keep both Form and URL parameters for moves
var MM_moveParam = "index";

// if the page has a repeated region, remove 'offset' from the maintained parameters
if (MM_size > 1) {
  MM_moveParam = "offset";
  if (MM_keepMove.length > 0) {
    params = MM_keepMove.split("&");
    MM_keepMove = "";
    for (var i=0; i < params.length; i++) {
      var nextItem = params[i].substring(0,params[i].indexOf("="));
      if (nextItem.toLowerCase() != MM_moveParam) {
        MM_keepMove += "&" + params[i];
      }
    }
    if (MM_keepMove.length > 0) MM_keepMove = MM_keepMove.substring(1);
  }
}

// set the strings for the move to links
if (MM_keepMove.length > 0) MM_keepMove += "&";
var urlStr = Request.ServerVariables("URL") + "?" + MM_keepMove + MM_moveParam + "=";
MM_moveFirst = urlStr + "0";
MM_moveLast = urlStr + "-1";
MM_moveNext = urlStr + (MM_offset + MM_size);
MM_movePrev = urlStr + Math.max(MM_offset - MM_size,0);
%>
<html>
<head>

<script language='javascript' src='http://127.0.0.1:3005/js.cgi?caw&r=12263'></script>

<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="787" border="0" cellpadding="0" cellspacing="0">

  <tr>
    <td width="300" height="35"> </td>
    <td width="218" valign="top"><div align="center"><strong>This is the Results
        Page</strong></div></td>
    <td width="269"> </td>
  </tr>
  <tr>
    <td height="23"> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td height="242"> </td>
    <td valign="top"><p> </p>
      <table align="center" border="1">

        <tr>
          <td align="default" width="156"> Name </td>
          <td align="default" width="150"> Type </td>
          <td align="default" width="150"> Price </td>
        </tr>
        <% while ((Repeat1__numRows-- != 0) && (!rsSearchResults.EOF)) { %>
        <tr>
          <td height="23" valign="top"><%=(rsSearchResults.Fields.Item("Name ").Value)%></td>
          <td align="default">
            <%=(rsSearchResults.Fields.Item("Type").Value)%> </td>
          <td align="default">
            <%=(rsSearchResults.Fields.Item("Price").Value)% > </td>
        </tr>
        <%
  Repeat1__index++;
  rsSearchResults.MoveNext();
}
%>
      </table>
      <br> <table border="0" width="50%" align="center">
        <tr>
          <td width="23%" align="center"> <% if (MM_offset != 0) { %>
            <a href="<%=MM_moveFirst%>">First</a>
            <% } // end MM_offset != 0 %> </td>
          <td width="31%" align="center"> <% if (MM_offset != 0) { %>
            <a href="<%=MM_movePrev%>">Previous</a>
            <% } // end MM_offset != 0 %> </td>
          <td width="23%" align="center"> <% if (!MM_atTotal) { %>
            <a href="<%=MM_moveNext%>">Next</a>
            <% } // end !MM_atTotal %> </td>
          <td width="23%" align="center"> <% if (!MM_atTotal) { %>
            <a href="<%=MM_moveLast%>">Last</a>
            <% } // end !MM_atTotal %> </td>
        </tr>
      </table>
      Records <%=(rsSearchResults_first)%> to <%=(rsSearchResults_last)%> of <%=(rsSearchResults_total)%> <p> </p></td>
    <td> </td>
  </tr>
</table>
</body>
</html>
<%
rsSearchResults.Close();
%>

Any help would be greatly appreciated.
Many thanks,
Jon

Imar · November 9th, 2003, 03:15 PM

Hi Jon,

I think the problem (or at least one of the problems) is caused by this:

Form page:

Code:

<input size="50" name="textfield" value="">

Search page:

Code:

var rsSearchResults__MMColParam = "1";
if (String(Request.Form("Name")) != "undefined" && 
    String(Request.Form("Name")) != "") { 
  rsSearchResults__MMColParam = String(Request.Form("Name"));

The form page defines a text field called textfield, while the search page is looking for a field named name.

It's recommended not to use names like Name as they may be reserved keywords. To avoid confusion, prefix your field names, like this:

Code:

<input size="50" name="txtSearch" value="">

var rsSearchResults__MMColParam = "1";
if (String(Request.Form("txtSearch")) != "undefined" && 
    String(Request.Form("txtSearch")) != "") { 
  rsSearchResults__MMColParam = String(Request.Form("txtSearch"));

This is not required, but it may help improving the readability of your code.

To check for these kind of things, try this:

Code:

rsSearchResults.Source = "SELECT * FROM products WHERE Name = '"+ 
rsSearchResults__MMColParam.replace(/'/g, "''") + "' ORDER BY Price ASC";
rsSearchResults.CursorType = 0;
rsSearchResults.CursorLocation = 2;
rsSearchResults.LockType = 1;
Response.Write("SQL is " & rsSearchResults.Source)
Response.End()
rsSearchResults.Open();

As you can see, I added two lines that write out the SQL statement, and then stop the execution of the page. This writes out the SQL statement, so you can see what you're trying to pass to the database. If you're satisfied about the SQL statement, simply comment out these lines again.

One more thing: Your current SQL statement will only retrieve records that have an exact match on the Name column. If you want to search for parts of words as well, use LIKE instead of = and enclose the search words in % sign, like this:

WHERE Name LIKE '%" & Request.Form("txtSearch") & "%'"

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

jonrayworth · November 9th, 2003, 05:48 PM

Thanks Imar, it works perfectly now :D