Problem building <form> from recordset

[Wayfarer]

Hi,

I have a database of quotes on my website that uses an Access 97
database (I'm cheap...I have an old copy of MS Office). I have one page
that builds a form <select> from a SQL statement that looks like this:
SELECT DISTINCT last, first FROM quotes ORDER BY last, first;

The results are 1,250 records from a 3,700 record table.

I loop through the records, using each to build an <option> for the
<select>. When loading the page, it gets to the <select> and then it's
like someone issued a response.end, because it just dies after about 3
seconds. and I get an incomplete page; looking in view source it is
literally truncated right before the form <select>.

Of course it works at home, but not on my "production" site.

I've tried all kinds of things like executing a stored Access query as
adCmdStoredProc, but it didn't help. I'm not even sure if that method
was even any faster when I ran it at home.

Is there anything else that you folks know of that I could try, or am I
just going to have to bite the bullet and come up with an alternative
way to let my users select the source of the quote?

I'm using ADO, Microsoft.Jet.OLEDB.4.0, response.buffer=true.

I'd appreciate any help you could give me.

Thanks.
--
Wayfarer
Journeys: http://wayfarer.brinkster.net/contact.asp

[dlbjr]

1. Make sure you have an index on both fields last and first. This will
speed the query up.

2. Do not use any string concatenation in the loop. This will be slow!!!!
with over 1000 items.

3. Try this:

If Not rs.EOF Then
With Response
.Write "<SELECT ID='QUOTE' NAME='QUOTE'>"
.Write VbCrLf
Do While Not rs.EOF
dtmItem = rs("last") & ", " & rs("first")
.Write "<OPTION VALUE='"
.Write dtmItem
.Write "'"
if cstr(Request("QUOTE") = cstr(dtmItem) then
.Write " SELECTED"
end if
.Write ">"
.Write dtmItem
.Write "</OPTION>"
.Write VbCrLf
rs.MoveNext
Loop
.Write "</SELECT>"
.Write VbCrLf
End With
End If
-dlbjr

Discerning resolutions for the alms