As a start,
let's build a page to pass a variable to our first recordset. We will create
a dropdown box form field and name it Key. Set the form to submit
to a new .asp page, and make sure the form method is set to POST.
Now at the page where the form is submitted to, set up our first recordset
to filter the variable Key:
We are using the value from the form field Key to filter our first
recordset. Since we are passing numeric values, var1 in the SQL
statement must not have any ' ' sign around them.
A note about Request.Form("variable") and Request("variable")
here. If you are not going to use recordset navigation on your page (as
in our case here), you can use Request.Form("variable") without
problem. If your recordset has a large number of records, and if that
is the case most probably you would want to navigate to other records
in the recordset using a recordset navigation scheme, then you must use
Request("variable"). If you use Request.Form("") in
this situation, your variable will be lost when you attempt to move to
the second set of records.
The reason it works this way is because when you first go to the page,
you are using a Form variable and that is why Request.form("variable")
works; whereas the second time you hit the page you are using a QueryString
variable. When you are using Request("variable"), it works with both Form
and QueryString variables.
* Using Request takes a slight performance hit because it will look through
all of the five collections in this order: QueryString, Form, Cookies,
ClientCertificate, and ServerVariable.
We will then create our second recordset, this time filtering a different
table, Table1 (whereas it was Tutorials in the first recordset). In the
SQL for the second recordset include a WHERE clause and set it equal to
a variable using the "+" button next to the "Variables" label. For reasons
that will be made clear later, use varSpecial as the run-time value:
Finally it is time to get our hands dirty by digging into the codes UltraDev
generated and do some code juggling:
Notice the peculiar order UltraDev inserts the various code sections.
First, the variables for Recordset is defined (Dim), followed by those
for Recordset2. Then comes the creation of the two recordsets, Recordset1
followed by Recordset2.
For our purpose we need to rearrange those code sections to avoid the
server from returning errors since ASP codes are processed from top to
bottom. And oh yes, we haven't defined our varSpecial yet.
Carefully rearrange the various code sections in this new order: 1) variable
declaration for Recordset1, 2) Recordset1 creation, 3) variable declaration
for Recordset2, and finally 4) Recordset2 creation.
Lastly, we need to define our varSpecial variable as the result
from the ID column in Recordset1. Add by keying in those two lines of
codes highlighted above and make sure it is sandwiched between the two
major code sections for Recordset1 and Recordset2.
Addendum
(January 7, 2001):
Apparently this method only works if your first recordset yielded a single
record. If there is more than one record in that recordset, the second
recordset will use the first record in that first recordset as
a filter.
After much testing here is a method that will work if your first recordset
returns multiple records. I have done it following the manner of the code
that UltraDev generates. To simplify things, the steps involved are the
same as outlined in this tutorial, with three exceptions:
Addendum (March 5, 2002):
After you are done this should be the sequence of code blocks above
the <html> tag (some manual rearrangement might be needed):
<%@LANGUAGE="VBSCRIPT"%>
<% Recordset1 code block %> <%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%> <%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%> <%
Dim varSpecial
varSpecial = varSpecial &_
(Recordset1.Fields.Item("ID").Value) & ","
%> <%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
<% Recordset2
variable declaration code block %>
<% Recordset2 code block %>
<html>
SELECT *
FROM Table1
WHERE TutorialID IN (var2)
As Kindler Chase has so kindly pointed out (see the user feedback section),
you can shorten the steps by entering the ID from Recordset1 directly into
the runtime value box of your second recordset, thereby bypassing the use
of an intermediate variable (varSpecial in our example) altogether.
You would still have to adjust the location of the MM created variable though.
Here is a screen-shot that hopefully will make it easier for you to visualize
what I was saying:
| Demo | Back |
Copyright © 2001, Swee Hoe Ong
All rights reserved.