Question:

Here is a snippet of my code:

set conn = Server.CreateObject("ADODB.Connection")conn.open "gorillaclasses"set rs = Server.CreateObject("ADODB.Recordset")stmt = "SELECT class,time,instructor FROM " & clubtable    rs.open clubtable, conn, adOpenStatic,,adCmdTable %>rs.filter = "dayslot = '7' and timeslot = '3'"

I want to loop the filter and set the dayslot number to a variable so that with each loop, the number increases by one. I’ve set up a “do while” deal and the variable, but I cannot get the filter to work if I use a variable. Is this possible? Can I use a variable in the rs.filter statement? If so, how would I construct it?

Answer:

Instead of using a filter, try a different approach. When you use a filter, you are accessing all the records first and generating a recordset. Then you are filtering the data to only view what is needed. This approach is very wasteful. Instead, bring back only the records you need with a Where clause in your SQL Statement. So, rewrite your code as follows (assuming you want to set the value of the dayslot variable from 1 to 7):

set conn = Server.CreateObject("ADODB.Connection")conn.open "gorillaclasses"set rs = Server.CreateObject("ADODB.Recordset")' -- (NEW CODE FROM HERE ON) -----Dim iFor i = 1 to 7   stmt = "SELECT class,time,instructor FROM " & _     "clubtable WHERE dayslot = '" & i & "' " & _     " and timeslot = '3'"   ' -- generate the fastest kind of recordset,    ' -- a forward only recordset   set rs = conn.Execute (stmt)   ' --- Process the recordset   do while not rs.eof      ... your other code here   loop' -- proceed to the next dayslotNext' -- release resourcesset rs = nothingset conn = nothing

For each value of “i”, you are obtaining a very small recordset to loop through. This approach will be much faster than obtaining all the records and then applying a filter to it.