Looping!

I have an ASP application that retrieves records from a SQL Server DB
table & displays them to the user. The records will be displayed to the
user in a HTML table with the column names as the first row. After this
row, there should be a drop-down list for each & every column. Each of
the drop-down list will list the records of that particular column.

For eg. assume that 3 columns in this table are Book, Category & Author
& all the columns have 100 records. The ASP page will have 3 drop-down
lists & each of the drop-down list will list the 100 records as options
i.e. the first drop-down will list the 100 records of the Book column,
the second drop-down will list the 100 records of the Category column &
the 3rd drop-down will list the 100 records of the Author column. Note
that after this row of drop-down lists only, the records get displayed
in the HTML table.

The main intention of providing drop-down lists is to give users the
option to select any record/s & retrieve data based on that criteria.
Here's an example:

When the page loads for the first time, all the records are shown. Now
a user wants to view the list of only those books that have been penned
by, say, Author1. He will select Author1 from the 3rd drop-down list.
The page will be submitted & he will be shown the list of books written
by Author1.Next he wants to view the list of only those books that have
been written by Author1 but which belong to the, say, Sci-Fi category.
He will then select Sci-Fi from the 2nd drop-down. Again the page will
be submitted & he will be shown the list of books written by Author1 &
which belong to the Sci-Fi category.

Now since the records get populated in the drop-down lists & then again
displayed to the users, ASP has to make more than 1 round to the DB. To
populate each of the drop-downs, what I am doing is opening a Recordset
object,iterating through the records, retrieving them & then populating
that drop-down. To populate the next drop-down,I am closing the already
open Recordset object that was used to populate the previous drop-down
& again opening it to populate the next drop-down. Here's what I have
done:

<%
Dim strSQL
Dim objRS

'this is for populating the "Book" drop-down list
%>

Select
<%
strSQL="SELECT DISTINCT(Book) FROM tblBooks
Set objRS=objConn.Execute(strSQL)

Do Until(objRS.EOF)
%>
"><%=
objRS("Book") %>
<%
objRS.MoveNext
Loop
%>

<%
objRS.Close
Set objRS=Nothing

'next populate the "Category" drop-down
%>

Select
<%
strSQL="SELECT DISTINCT(Category) FROM tblBooks
Set objRS=objConn.Execute(strSQL)

Do Until(objRS.EOF)
%>
%>"><%= objRS("Category") %>
<%
objRS.MoveNext
Loop
%>

<%
objRS.Close
Set objRS=Nothing

'populate all the drop-downs in this way & finally display the records
to the user
%>

Now what I would like to ask is is this an efficient way to cater to
this application i.e. repeatedly using the same Recordset object
(though I am closing & getting rid of it after populating each & every
drop-down) & then again going back to the DB to fetch the records so
that they can be displayed to the users? If not, can someone suggest a
more efficient workaround?

I had thought of storing all the records of each & every column in
Session variables while creating the drop-downs & then after populating
the drop-downs, using the Session variables to display the records but
I believe this will make the application more inefficient; the reason
being if the DB table has, say, 15 columns & each column has 1000
records, it means creating 15 Session variables for each & every user &
storing each Session variable with 1000 records! This would eat up a
lot of memory.

Thanks,

Arpan

Re: Looping!

ADDENDUM TO THE ABOVE POST:
--------------------------

The DB table has 21 columns & there are 800+ records but there are
chances that in future, not only the records but even new columns can
get added to the table i.e. while coding, I am not aware of how many
columns are there in the table. Neither am I aware of the column names
while coding.

What I did is first framed the SQL query depending upon user input &
then using the "Count" & "Name" properties of the Fields collection,
displayed the column headers. This is what I did (literally):

<%
'frame the SQL query first depending upon user input
strSQL1=SELECT * FROM tblRecords WHERE......"
Set objRS1=objConn.Execute(strSQL1)

Response.Write("