Recordset Browsing

[DrSeussFreak]

I have this ASP page that has 3 drop down boxes. #1 influences what is in #2, and #2 influences #3.

That works fine. When I have the values set on the page, I try to use next or previous buttons and when I do, they clear all the settings in the drop downs and just start to browse all records instead of my selected records.

Anyone have a good idea as how to code the browse buttons so it keeps the values and browses the rest of the record set.

Below is the code that does the drop downs.

Thanks!

<table width="800" border="1" align="center" bordercolor="#000000">
    <tr bgcolor="#CCCCCC">
      <td colspan="5" align="center" valign="middle" bordercolor="#D4D0C8" bgcolor="#FFFFFF" class="PageHeader">Product Information</td>
    </tr>
    <tr bgcolor="#CCCCCC">
      <td colspan="2" bgcolor="#CCCCCC"><span class="RowTitles"><strong>Please Select From List Boxes: </strong></span></td>
      <td width="34" rowspan="4" bgcolor="#8EAAC0">&nbsp;</td>
      <td bgcolor="#CCCCCC" colspan="2"><span class="RowTitles"><strong>Other Options: </strong></span></td>
    </tr>
    <tr>
      <td width="112" align="left" valign="middle" class="Titles"><strong>Products's</strong>:</td>
      <td width="171" align="left" valign="middle" class="Titles">
        <select name="slProducts" size="1" onChange="document.forms['frmSelect'].action = 'ViewProductInfo.asp?pID=<%= Request.Form("slProducts") %>'; submit();">
          <option value="" <%If (Not isNull(Request.Form("slProducts"))) Then If ("" = CStr(Request.Form("slProducts"))) Then Response.Write("SELECTED") : Response.Write("")%>>Please Select a Product</option>
          <%
While (NOT rcdsetProducts.EOF)
%>
          <option value="<%=(rcdsetProducts.Fields.Item("Product").Value)%>" <%If (Not isNull(Request.Form("slProducts"))) Then If (CStr(rcdsetProducts.Fields.Item("Product").Value) = CStr(Request.Form("slProducts"))) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(rcdsetProducts.Fields.Item("Product").Value)%></option>
          <%
  rcdsetProducts.MoveNext()
Wend
If (rcdsetProducts.CursorType > 0) Then
  rcdsetProducts.MoveFirst
Else
  rcdsetProducts.Requery
End If
%>
        </select>      </td>
      <td width="168" align="center" valign="middle" class="Links"><a href="#" onclick="window.open('Search.asp','_blank','width=750,height=310,scrollbars=yes')">Search in a new window </a></td>
      <td width="281" align="center" valign="middle" class="DateTime"><SCRIPT LANGUAGE="JavaScript1.2">

<!-- This script and many more are available free online at -->
<!-- The JavaScript Source!! [URL unfurl="true"]http://javascript.internet.com[/URL] -->

<!-- Begin
var months=new Array(13);
months[1]="January";
months[2]="February";
months[3]="March";
months[4]="April";
months[5]="May";
months[6]="June";
months[7]="July";
months[8]="August";
months[9]="September";
months[10]="October";
months[11]="November";
months[12]="December";
var time=new Date();
var lmonth=months[time.getMonth() + 1];
var date=time.getDate();
var year=time.getYear();
if (year < 2000)
year = year + 1900;
document.write("<center><b>" + lmonth + " ");
document.write(date + ", " + year + "</b></center>");
// End -->
</SCRIPT></td>
    </tr>
    <tr>
      <td align="left" valign="middle" class="Titles"><strong>Item's:</strong></td>
      <td align="left" valign="middle" class="Titles">
        <select name="slItems" onChange="document.forms['frmSelect'].action = 'ViewProductInfo.asp?pID=<%= Request.Form("slProducts") %>&iID=<%= Request.Form("slItems") %>'; submit();">
          <option value=" " <%If (Not isNull(Request.Form("slItems"))) Then If (" " = CStr(Request.Form("slItems"))) Then Response.Write("selected=""selected""") : Response.Write("")%>></option>
          <%
While (NOT rcdsetItems.EOF)
%>
          <option value="<%=(rcdsetItems.Fields.Item("Item").Value)%>" <%If (Not isNull(Request.Form("slItems"))) Then If (CStr(rcdsetItems.Fields.Item("Item").Value) = CStr(Request.Form("slItems"))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(rcdsetItems.Fields.Item("Item").Value)%></option>
          <%
  rcdsetItems.MoveNext()
Wend
If (rcdsetItems.CursorType > 0) Then
  rcdsetItems.MoveFirst
Else
  rcdsetItems.Requery
End If
%>
        </select>      </td>
      <td align="center" valign="middle" class="Links"><a href="Index.htm">Home</a></td>
      <td align="center" valign="middle" class="DateTime"><span id=clock style="position:relative;"></span></td>
    </tr>
    <tr>
      <td height="26" align="left" valign="middle" class="Titles"><strong>Manufacturer's:</strong></td>
      <td align="left" valign="middle" class="Titles">
        <select name="slManufacturers" onChange="document.forms['frmSelect'].action = 'ViewProductInfo.asp?pID=<%= Request.Form("slProducts") %>&iID=<%= Request.Form("slItems") %>&mID=<%= Request.Form("slManufacturers") %>'; submit();">
          <option value=" " <%If (Not isNull(Request.Form("slManufacturers"))) Then If (" " = CStr(Request.Form("slManufacturers"))) Then Response.Write("selected=""selected""") : Response.Write("")%>></option>
          <%
While (NOT rcdsetManufacturers.EOF)
%>
          <option value="<%=(rcdsetManufacturers.Fields.Item("Manufacturer").Value)%>" <%If (Not isNull(Request.Form("slManufacturers"))) Then If (CStr(rcdsetManufacturers.Fields.Item("Manufacturer").Value) = CStr(Request.Form("slManufacturers"))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(rcdsetManufacturers.Fields.Item("Manufacturer").Value)%></option>
          <%
  rcdsetManufacturers.MoveNext()
Wend
If (rcdsetManufacturers.CursorType > 0) Then
  rcdsetManufacturers.MoveFirst
Else
  rcdsetManufacturers.Requery
End If
%>
        </select>      </td>
      <td align="center" class="Titles">
	  Quick Search:</td>
      <td colspan="1" align="center" class="Titles">
	  <input name="txtQuickSearch" type="text" >
	  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      <input type="button" name="btnSearch" value="Search" onclick="window.location='ViewProductInfoQSResults.asp'"></td>
    </tr>
  </table>

 

[Tarwn]

You didn't post the code for your next and previous buttons. Unless these buttons are submitting the form, then your select dropdowns will resent because the values they are using to indicate selected records come from the Request.Form collection, which is only populated with submitted form POST information.

The simplest fix would be to switch to the generic Request colleciton. This will be inefficient since it checks .Form, .QueryString, and .Cookies for a value, but based on the fact that your already looping through the same recordset three times I'm going to assume the inefficiency of using Request() will go unnoticed.

Additionally you have some extraneous code that may cause problems, namely the extra Response.Write's in your option value attributes and the formulation of the action that you do in each onChange in your selects.

Basically the logic your following right now in your select onChange attributes will never correctly pass values. Your outputting the id's into the querystring of the address for the values that were selected on the previous page. The only way for those onChange events to fire would be if the select changes, thus invalidating the contents you are putting in the querystring.

Example:
1) Page loads for first time, no querystring addition in the onChange
1) you select an option from the first dropdown with a value of "bob"
2) Form submits to ViewProductInfo.asp?pID=
3) Page is rebuilt, with the value "bob" hardcoded into all ofthe dropdowns.
4) you decide to change the first dropdown to "george"
5) form submits to ViewProductInfo.asp?pID=bob

Here are some suggestions before you move forward:
1) Instead of re-looping through the recordset use the GetRows method to pull the data into an array and get rid of the recordset. This will increase performance.
2) Un-used variables in VBScript are of type Empty, never Null. So the null checks, while quick, are actually unnecessary
2.1) Only Response.Write When you need to. Even Response.Writing an empty string uses resources:

[s] <option value="" <%If (Not isNull(Request.Form("slProducts"))) Then If ("" = CStr(Request.Form("slProducts"))) Then Response.Write("SELECTED") : Response.Write("")%>>Please Select a Product</option>[/s]
   <option value=""<% If Request.Form("slProducts") = "" Then Response.Write " selected" %>>Please Select a Product</option>

3) Escaping into and out of ASP blocks is in many cases less efficient then simply staying inside the ASP block and outputting the test you need in a Response.Write

---

My suggestion for getting your paging working would be to capture the values into variables at the top of your page. Basically do an if check to see if each Request.Form value was posted, if not the check Request.QueryString, if that is empty then default to an empty string.
Then throughout the rest of your code reference these variables rather than trying to get the values directly out of the Request collections.
Set the selected values of your dropdowns as your doing now, but get rid of the portion that changes the form action attribute and just do the submit. Since your setting each dropdown value then they will be resubmitted as part of the form anyways. One thing you may consider doing is adding hidden fields for each of the three variables so you can easily do a comparison to see which of the three changed (at which point you can set the variables holding later dropdown values to an empty string and let processing continue as usual).
As far as the next and previous links are concerned all you have to do at this point is us standard anchor tags and set the querystring for each URL based on the 3 variables you set at the top.

So basically your logic would look like:
1) Define 3 variables (Products, Items, Titles)
2) Assign values from Request.Form, QueryString, or Empty for the three vars
3) If items has changed, set titles variable to empty string
4) if Products has changed, set items and titles to empty strings
5) SQL Query
6) Dump data to an array with RecordsetObject.GetRows()
7) Output Select tag with onChange="submit();" for Products
8) Output options with limited switching between ASP and HTML
9) Set selected based on matching value against Products variables
10) repeat 7-9 for Items and Titles
11) Output current values for Products, Items, Titles into hidden inputs (for example, CurProduct, CurItem, CurTitle)
12) Output search box

whenever) Output anchor links for Prev/Next using the Products, Items, and Titles vars to build the URL Querystring

-T