asp sort

[sharonc]

I have an asp page that calls a second asp page. The second asp page contains the oledb code to connect to the database and display the data sorted. On the first page I need to give the user the ability to select fields (engineer, originator, plan year, etc.) to sort on. They have 14 fields they can combine to do a sort. Can you tell me what would be the best way to display these 14 fields so the user can create the sort the way they want to. Also, how would I then pass this information to the second asp page where the sql select statment is created.

Thank you.

 

[MoLaker]

Working with ASP is not as simple as working in MS Access. ASP bascially makes a one time run to spew out the HTML to the browser. If you want something different (such as a different sort) you must rerun the ASP with the necessary changes to do the something different.

To re-sort, you must run another SQL query with the desired ORDER BY statement. With 14 sorting fields, this results in a horrendous number of ORDER BY possibilities.

Here's some ideas off the top of my head

I suggest you try to limit the number of choices that can be made - not the number of fields, necessarily, just the number of fields that can be chosen. For example, if you limited the sort to 3 fields, you could have three dropdown fields on a form with the 14 field names as the options for the fields. But, if all 14 fields are demanded, then you'll need at least 13 dropdown fields.

The 2nd page (SQL page) will have to interpret the form submission values to build the ORDER BY portion of the SQL. If you construct the first page so the submitted values are the precise names of the table fields, then you could do something like...

sFld1 = request.form("D1")
sFld2 = request.form("D2")
sFld3 = request.form("D3")
' need one for each field on first page

SQLstring = "select * from tblWhatever"
' this would be the unsorted SQL for the data to be viewed

If len(trim(sFld1)) > 0 then
' check to see if the 1st sort field has data
    SQLstring = SQL string & " Order By "
    SQLstring = SQLstring & sFld1
    If len(trim(sFld2)) > 0 then
    ' check to see if the 2nd sort field has data
        SQLstring = SQLstring & "," & sFld2
    end if
        If len(trim(sFld3)) > 0 then
        ' check to see if the 3rd sort field has data
            SQLstring = SQLstring & "," & sFld3
        end if
' continue for as many sort fields as first page has

end if

' proceed on with connection and recordset opening

Unless you come up with some method on the first page, you might need to trap duplicate field selections such as a case where the user selects the same field name more than once. This could be done with the Instr() function - if the field names do not contain the name of another field (like RegionMgr and Region, for example) and this would not work if the SELECT statement names each field instead of using the asterisk (*).

If len(trim(sFld2)) > 0 then
' check to see if the 1st sort field has data
    SQLstring = SQL string & " Order By "
    SQLstring = SQLstring & sFld1
    If len(trim(sFld2)) > 0 then
    ' check to see if the 2nd sort field has data
        If Instr(1,SQLstring,sFld2) = 0 then
            ' check to see if field is already in SQL
            SQLstring = SQLstring & "," & sFld2
        End If
    end if
        If len(trim(sFld3)) > 0 then
        ' check to see if the 3rd sort field has data
            If Instr(1,SQLstring,sFld3) = 0 then
                SQLstring = SQLstring & "," & sFld3
            End If
        end if
' continue for as many sort fields as first page has

end if

I have not addressed ascending and descending sorting. If this is desired, it could be included in the options list of each dropdown field ("SalesRegion" & "Sales Region Desc"). This would double the choices on each list and might make the Instr() duplicate choice function more tricky.

These are just ideas. I haven't tried to make this code work because I'm too lazy and besides, that's your job.

 

[sharonc]

Thank you so much for your help. I've decided they can only sort on 4 fields at a time. At least now I have some idea of where to start.

Thank you.

 

[shaddow]

You could take a look here to.
It shows how user can chose the fields they want displayed.

http://www.pubfiction.ro/shaddow/DinamicTable

display.zip

________
George, M
Searches(faq333-4906),Carts(faq333-4911)

 

[pixl8r]

Ummm... actually you do not have to alter the query to the database to change the sort. You can sort the recordset by adding the following to your recordset:

Set rst02 = Server.CreateObject("ADODB.Recordset")
Set rst02 = objServices.getDomicileAddress(cint(0))
rst02.Sort = "DBFieldName"

The example uses a stored procedure but the recordset.Sort = "fieldname" works very well on all recordsets. You would still have to refresh the page but you could save some bandwidth (and some code) this way. Essentially just place the sort line before the recordset loop to write the page.

Hope it helps.

Wow JT that almost looked like you knew what you were doing!

 

[sharonc]

Thank you both. I'll give these a try.