sql/asp problem - resorting a recordset when 'recordset.sort' is not possible.

Lobsang · January 9, 2009, 9:02pm

I have three dropdown boxes (account number, forename, surname) each of which contain all the records, sorted by whichever field is shown.

In other words. I have a dropdown showing account numbers, sorted by account number.

I have a dropdown of forenames sorted by forename.

And I have a dropdown of surnames sorted by surname.

Currently I have 3 queries to feed these dropdowns. This slows things down considerably (returning all records three times, that’s over ten thousand records three times)

It would seem more sensible to get the records once (select accountnumber,forename,surname) and then change the order of the recordset for each dropdown.

I tried using “rs.sort =” but am told that “Current provider does not support the necessary interfaces for sorting or filtering.”

So is there a way round this? Is there a way to show three dropdowns, each containing the different field and sorted by that field, without having to use three seperate query calls??

ZipperJJ · January 9, 2009, 9:40pm

Get the record once and drop it into an array using GetRows().



Dim arrData
Set objRS = [your conn string and sql]

If not objRS.EOF Then
    arrData = objRS.GetRows()
Else
   arrData = ""
End If

objRS.Close
Set objRS = Nothing

Then, use this article on how to sort multi-dimensional arrays.

Don’t forget to make sure you have an array first before you start manipulating the array.



If IsArray(arrData) Then
...go for it
End If

ZipperJJ · January 9, 2009, 9:49pm

Reading a bit more about ASP and sorting arrays, I think what I posted is bad advice.

Lemme ask you this, tho - are you taking each result set (sorted using SQL) and dumping into an array and then writing out the array elements? Or are you opening the RS, paging through, writing and closing?

If you’re not using arrays, then try the first bit of code I posted for each record set (account, first name, last name). You’ll end up with 3 arrays and 3 closed and discarded recordsets. Then you can write out the DDLs from there without having the DB in play at all.

Lobsang · January 9, 2009, 9:56pm

I’m taking each result set and constructing a select directly from it like follows…

While RS.EOF = false
Response.Write “<option value=”""& RS.Fields(“clientid”).Value & “”">" & RS.Fields(“forename”) & “</option>” & vbCrLf
RS.movenext
Wend

<snip code below>
Before you replied to my OP I was trying out some javascript I’d found on the internet which sorts the dropdown afterwards. It seems to work but it raises an important question…
Would it be quicker to just do the 3 sql queries as in the original method? Because the queries are run server-side on a fast sql db, and any sorting to ‘save duplicating queries’ would be done client side on a much slower machine, and therefore defeat the object.

ZipperJJ · January 9, 2009, 10:09pm

Yeah exactly. I was reading that ASP is shitty about memory management. So doing the sorting pre-process is stupid and doing it in JS is even more stupid.

Trust me about the arrays. Don’t do it like you’re doing it.

Create 3 arrays (one for each list) using the method I showed.

For each array, do this…



Dim iRow, strVal, strText
If IsArray(arrData) Then
   Response.Write "<select name=SelectBox>"
   For iRow = 0 to UBOUND(arrData,2) 'this sets the loop from 0 to number of rows in arrData
       'This assumes your record set returns 2 values per row. If not use less or more of the below.
       strVal = arrData(0, iRow)
       strText = arrData(1, iRow)
       Response.Write "<option value="& strVal &">"& strText &"</option>"
   Next
   Response.Write "</select>"
End If

See if that doesn’t speed you up.

Lobsang · January 9, 2009, 10:15pm

ZipperJJ:

Yeah exactly. I was reading that ASP is shitty about memory management. So doing the sorting pre-process is stupid and doing it in JS is even more stupid.

Trust me about the arrays. Don’t do it like you’re doing it.

Create 3 arrays (one for each list) using the method I showed.

For each array, do this…
Dim iRow, strVal, strText
If IsArray(arrData) Then
   Response.Write "<select name=SelectBox>"
   For iRow = 0 to UBOUND(arrData,2) 'this sets the loop from 0 to number of rows in arrData
       'This assumes your record set returns 2 values per row. If not use less or more of the below.
       strVal = arrData(0, iRow)
       strText = arrData(1, iRow)
       Response.Write "<option value="& strVal &">"& strText &"</option>"
   Next
   Response.Write "</select>"
End If
See if that doesn’t speed you up.

So let me get this straight - I should still run three sql queries, then put each one into an array, then do the above?

I will try. I just wanted to clarify that 3 sql record sets should be used

ZipperJJ · January 9, 2009, 10:19pm

Yup. But instead of paging through them while open and writing your crap out, drop each full result set into an array and close out the recordset.

It’ll cut down on your overhead, I’m sure.

And don’t return shit you don’t need. Don’t just select *, select the fields you want.

Lobsang · January 9, 2009, 11:24pm

Right I’ve finished it now, and it certainly is a lot faster than what I was trying before!

There are actually four dropdowns (accountnumber,forename,surname,email) so I just wrote four lots of code.

The resulting html is one line long, and about a million characters wide (I didn’t include any line breaks!) but it runs well even on slow machines.

Thanks very much for the help ZipperJJ!

Let me know if you want to see the final code.

DMC · January 9, 2009, 11:41pm

Is there a reason you don’t write queries specifically for each dropdown, returning a two column recordset for each, a hidden “key” value as well as the displayed “data” value?

Then, using the key value(s), pull the rest of the data once the dropdowns have had their values selected? I guess I"m asking why you’d pull the entire set of records at all, assuming that the dropdowns are used as some sort of filter.

Lobsang · January 10, 2009, 2:57am

the dropdowns are not filters. they are four seperate options to search the database.

If surname is known the surname sorted dropdown can be used to find the record. If account number is known then the account number sorted dropdown can be used to find the record, and so on.

For that reason all four dropdowns must contain all records. (just the relevant fields)

it could have been done another way, but this is designed to mimic a system which it is replacing - where dropdown menus contained different fields that could be used to return a whole record for editing.

DMC · January 10, 2009, 4:02am

You can still do it the way I was suggesting, based on what you’ve written here. Simply have a stored proc with all four parameters, using a default value for “not searched on this parameter” cases, and return the records that meet the one or more parameters that are passed in. If you give me a narrow dummy table structure that sort of mimics your situation, I could show you what I mean.

Lobsang · January 11, 2009, 7:29pm

I do do part of what you’re suggesting…

I have a query for each dropdown (on the advice of ZipperJJ) each query contains a hidden value which is used to pull of all of one record when the item is selected. only when an item is selected are all the fields of one record are queries on the database. this is the instant bit.

the four dropdowns are basically ways of finding a record. four different ways of finding a record depending on what is already known about the record you’re looking for.

here is the code I have at this point (long)




<!-- #include file="includes\db.asp" -->

<%

'***Forename************************************************************************************
Dim arrForename
Set objRS = conn.Execute("SELECT clientid,forename,surname from clients where requeststatusid in (1,2) order by forename")

If not objRS.EOF Then
    arrForename = objRS.GetRows()
Else
   arrForename = ""
End If
objRS.Close
Set objRS = Nothing
'***Surname************************************************************************************
Dim arrSurname
Set objRS = conn.Execute("SELECT clientid,surname,forename from clients where requeststatusid in (1,2) order by Surname")

If not objRS.EOF Then
    arrSurname = objRS.GetRows()
Else
   arrSurname = ""
End If
objRS.Close
Set objRS = Nothing
'***Email************************************************************************************
Dim arrEmail
Set objRS = conn.Execute("SELECT clientid,Email,forename,surname from clients where requeststatusid in (1,2) order by Email")

If not objRS.EOF Then
    arrEmail = objRS.GetRows()
Else
   arrEmail = ""
End If
objRS.Close
Set objRS = Nothing
'***Accountnumber************************************************************************************
Dim arrAccountnumber
Set objRS = conn.Execute("SELECT clientid,Accountnumber,forename,surname from clients where requeststatusid in (1,2) order by Accountnumber")

If not objRS.EOF Then
    arrAccountnumber = objRS.GetRows()
Else
   arrAccountnumber = ""
End If
objRS.Close
Set objRS = Nothing


response.write "<table border = 0 ><tr height = 10 ><td height = 10 align = right>"

Dim iRow, strVal, strText
If IsArray(arrForename) Then
   
   Response.Write "<form style=""display: inline;""  method= get action = ews_edit_account.asp target=main> Forename <select onchange=this.form.submit() name=clientid STYLE=""color: #FFFFFF; border: none; background: #999999; width: 200px; "">"
   For iRow = 0 to UBOUND(arrForename,2) 'this sets the loop from 0 to number of rows in arrData
       'This assumes your record set returns 2 values per row. If not use less or more of the below.
       strVal = arrForename(0, iRow)
       strText = arrForename(1, iRow) & " " & arrForename(2, iRow)
       Response.Write "<option value="& strVal &">"& strText &"</option>" & vbcrlf
   Next
   Response.Write "</select></form>"
End If

response.write "</td><td height = 10 align = right>"

If IsArray(arrSurname) Then
   
   Response.Write "<form style=""display: inline;"" method= get action = ews_edit_account.asp target=main> Surname <select onchange=this.form.submit() name=clientid STYLE=""color: #FFFFFF; border: none; background: #999999; width: 200px; "">"
   For iRow = 0 to UBOUND(arrSurname,2) 'this sets the loop from 0 to number of rows in arrData
       'This assumes your record set returns 2 values per row. If not use less or more of the below.
       strVal = arrSurname(0, iRow)
       strText = arrSurname(1, iRow) & "," & arrSurname(2, iRow)
       Response.Write "<option value="& strVal &">"& strText &"</option>" & vbcrlf
   Next
   Response.Write "</select></form>"
End If

response.write "</td></tr><tr><td align = right>"

If IsArray(arrAccountnumber) Then
   
   Response.Write "<form style=""display: inline;"" method= get action = ews_edit_account.asp target=main>Account Number <select onchange=this.form.submit() name=clientid STYLE=""color: #FFFFFF; border: none; background: #999999; width: 200px; "">"
   For iRow = 0 to UBOUND(arrAccountnumber,2) 'this sets the loop from 0 to number of rows in arrData
       'This assumes your record set returns 2 values per row. If not use less or more of the below.
       strVal = arrAccountnumber(0, iRow)
       strText = arrAccountnumber(1, iRow) & " (" & arrAccountnumber(2, iRow) & " " & arrAccountnumber(3, iRow) &")"
       Response.Write "<option value="& strVal &">"& strText &"</option>" & vbcrlf
   Next
   Response.Write "</select></form>"
End If

response.write "</td><td align = right>"

If IsArray(arrEmail) Then
   
   Response.Write "<form style=""display: inline;"" method= get action = ews_edit_account.asp target=main>Email <select onchange=this.form.submit() name=clientid STYLE=""color: #FFFFFF; border: none; background: #999999; width: 200px; "">"
   For iRow = 0 to UBOUND(arrEmail,2) 'this sets the loop from 0 to number of rows in arrData
       'This assumes your record set returns 2 values per row. If not use less or more of the below.
       strVal = arrEmail(0, iRow)
       strText = arrEmail(1, iRow) & " (" & arrEmail(2, iRow) & " " & arrEmail(3, iRow) &")"
       Response.Write "<option value="& strVal &">"& strText &"</option>" & vbcrlf
   Next
   Response.Write "</select></form>"
End If

response.write "</td></tr></table>"

%>

the above code sits in an iframe, and loads the results of clicking on one of the dropdown items in a page (not shown here) in another iframe.

Topic		Replies	Views
ASP Question: Retrieving records into a table Factual Questions	7	898	November 2, 2005
Can't understand why this PHP code works. Factual Questions	13	1713	January 19, 2009
Oracle & SQL+ Question Factual Questions	4	744	September 28, 2000
ASP.NET : Linqing to a drop down box Factual Questions	2	1051	November 7, 2009
Help me understand this sql query Factual Questions	1	693	February 10, 2006

sql/asp problem - resorting a recordset when 'recordset.sort' is not possible.

Related topics