How To Sort and Report from an Array ?

Discussion in 'ASP General' started by Les Juby, Feb 15, 2006.

  1. Les Juby

    Les Juby Guest

    I need to extract records from a database subject to conditions and
    only thereafter give the users the choice of which fields to sort the
    results on.

    In this situation I can't write back to a temporary file and then
    select all from that file on the different sort orderings.

    I figured that the only way out would be to write to an array from the
    first recordset set creation. Then write the report to the screen
    from the array and users can choose different sort orders and it would
    extract again and report from that same array.

    ( 1 )
    Only problem is that I havn't the faintest idea of how to create and
    write to the array. Or how to query it. Is it similar to the first
    database report.?

    Would anyone perhaps know of a simple tutorial resource that
    demonstrates this method.?

    ( 2 )
    Or do I need to think right outside the box and take the first report
    in a more efficient way. Problem is that I need to take only one
    record whenever the value changes in a particular field. Could my SQL
    statement use ordering on one field to take the recordset but order on
    another field for the output. I wouldn't have tought so.

    thanks

    .les.
     
    Les Juby, Feb 15, 2006
    #1
    1. Advertising

  2. Les Juby

    Paxton Guest

    Les Juby wrote:
    > I need to extract records from a database subject to conditions and
    > only thereafter give the users the choice of which fields to sort the
    > results on.
    >
    > In this situation I can't write back to a temporary file and then
    > select all from that file on the different sort orderings.
    >
    > I figured that the only way out would be to write to an array from the
    > first recordset set creation. Then write the report to the screen
    > from the array and users can choose different sort orders and it would
    > extract again and report from that same array.
    >
    > ( 1 )
    > Only problem is that I havn't the faintest idea of how to create and
    > write to the array. Or how to query it. Is it similar to the first
    > database report.?
    >
    > Would anyone perhaps know of a simple tutorial resource that
    > demonstrates this method.?
    >
    > ( 2 )
    > Or do I need to think right outside the box and take the first report
    > in a more efficient way. Problem is that I need to take only one
    > record whenever the value changes in a particular field. Could my SQL
    > statement use ordering on one field to take the recordset but order on
    > another field for the output. I wouldn't have tought so.
    >
    > thanks
    >
    > .les.



    A quick google threw up this result:
    http://www.4guysfromrolla.com/webtech/072199-1.shtml

    which may give you some ideas about how to accomplish the task.
    Basically you can make the column heads hyperlinks passing the 'Order
    By' variable in the querystring, then dynamically create your SQL query
    using the variable passed in the querystring.

    /P.
     
    Paxton, Feb 15, 2006
    #2
    1. Advertising

  3. Les Juby wrote:
    > I need to extract records from a database subject to conditions and
    > only thereafter give the users the choice of which fields to sort the
    > results on.
    >
    > In this situation I can't write back to a temporary file and then
    > select all from that file on the different sort orderings.


    We do this commonly. A generic example, with ADODB.Recordset RS:

    for (var Rows=[]; !RS.EOF; RS.MoveNext()) {
    for (var Cells=[],i=0; i<RS.Fields.Count; i++)
    Cells.push(RS.Fields(i).Value)
    Rows.push(Cells)
    }
    function ResultSort(x,y) {
    var C = Request.Form("SortBy").Item || 0,
    O = Request.Form("Order").Item == "Desc" ? -1 : 1
    return x[C]>y[C] ? O : x[C]
    }
    Rows.sort(ResultSort)

    In the above example, the default behavior is to sort in ascending order on
    column 0, but deferring to a passed sort column "SortBy" and ordering
    "Order".




    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, Feb 15, 2006
    #3
  4. Dave Anderson wrote:
    > function ResultSort(x,y) {
    > var C = Request.Form("SortBy").Item || 0,
    > O = Request.Form("Order").Item == "Desc" ? -1 : 1
    > return x[C]>y[C] ? O : x[C]
    > }
    > Rows.sort(ResultSort)


    I hate when OE tries to think for me. The return line should read:

    return x[C]>y[C] ? O : x[C]<y[C] ? -O : 0


    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, Feb 15, 2006
    #4
  5. Les Juby

    Les Juby Guest

    Les Juby wrote:

    >> I need to extract records from a database subject to conditions and
    >> only thereafter give the users the choice of which fields to sort the
    >> results on.
    >>
    >> In this situation I can't write back to a temporary file and then
    >> select all from that file on the different sort orderings.
    >>
    >> I figured that the only way out would be to write to an array from the
    >> first recordset set creation. Then write the report to the screen
    >> from the array and users can choose different sort orders and it would
    >> extract again and report from that same array.
    >>
    >> ( 1 )
    >> Only problem is that I havn't the faintest idea of how to create and
    >> write to the array. Or how to query it. Is it similar to the first
    >> database report.?
    >>
    >> Would anyone perhaps know of a simple tutorial resource that
    >> demonstrates this method.?
    >>
    >> ( 2 )
    >> Or do I need to think right outside the box and take the first report
    >> in a more efficient way. Problem is that I need to take only one
    >> record whenever the value changes in a particular field. Could my SQL
    >> statement use ordering on one field to take the recordset but order on
    >> another field for the output. I wouldn't have tought so.
    >>
    >> thanks
    >>
    >> .les.


    On 15 Feb 2006 13:16:28 -0800, "Paxton" <> wrote:

    >
    >
    >A quick google threw up this result:
    >http://www.4guysfromrolla.com/webtech/072199-1.shtml
    >
    >which may give you some ideas about how to accomplish the task.
    >Basically you can make the column heads hyperlinks passing the 'Order
    >By' variable in the querystring, then dynamically create your SQL query
    >using the variable passed in the querystring.
    >
    >/P.


    Thanks for that. It is pretty much what I would normally do, but I
    think maybe I havn't explained the problem properly here.

    My initial recordset is maybe only 10% of the database, and a whole
    lot of complex ordering and filtering has happened to get the
    recordset for display.

    The user now needs to be able to view that recordset in different
    orderings, and usually (as you've suggested) I'd be able to just
    generate the search again with querystrings passed from hyperlinks in
    the column titles. But I can't do that as I'd have to filter from the
    database first on special orderings to get the primary recordset.
    Catch 22.

    The other method is to do the first filter and feed the results into a
    temporary table. Then use that table for taking the different sorts
    from. Problem here is that a second user could put results in there
    while the first user is still looking at his different sorts. And
    that's quite likely with this many users. To put a random ID on
    records with a session variable to keep to your results only is the
    worst case solution. This table could then get huge, would need
    maintenance, and could corrupt. How do you clear it out without
    wiping out someone else's results-in-progress.?

    That's why I thought an array would be the solution. It's private (in
    memory) for each user, fast, and disappears when they do.? Wouldn't
    that work.? But can you use it like a normal table.?

    .les.
     
    Les Juby, Feb 16, 2006
    #5
  6. Les Juby

    Les Juby Guest

    On Wed, 15 Feb 2006 16:18:43 -0600, "Dave Anderson"
    <> wrote:

    >Les Juby wrote:
    >> I need to extract records from a database subject to conditions and
    >> only thereafter give the users the choice of which fields to sort the
    >> results on.
    >>
    >> In this situation I can't write back to a temporary file and then
    >> select all from that file on the different sort orderings.

    >
    >We do this commonly. A generic example, with ADODB.Recordset RS:
    >
    > for (var Rows=[]; !RS.EOF; RS.MoveNext()) {
    > for (var Cells=[],i=0; i<RS.Fields.Count; i++)
    > Cells.push(RS.Fields(i).Value)
    > Rows.push(Cells)
    > }
    > function ResultSort(x,y) {
    > var C = Request.Form("SortBy").Item || 0,
    > O = Request.Form("Order").Item == "Desc" ? -1 : 1
    > return x[C]>y[C] ? O : x[C]
    > }
    > Rows.sort(ResultSort)
    >
    >In the above example, the default behavior is to sort in ascending order on
    >column 0, but deferring to a passed sort column "SortBy" and ordering
    >"Order".


    Sorry Dave, I'm just a simple Classic ASP child ...... I think you're
    presuming here that I have created an array and am now happily
    reporting from it. If so, not so.!

    Is that the presumption .... and if so how would I get the first
    results into the array to get out the second level reports...?

    This is why I was hoping there might be a simple (but detailed)
    explanation somewhere....

    thanks for the help

    .les.
     
    Les Juby, Feb 16, 2006
    #6
  7. "Les Juby" <> wrote in message
    news:...
    >I need to extract records from a database subject to conditions and
    > only thereafter give the users the choice of which fields to sort the
    > results on.
    >
    > In this situation I can't write back to a temporary file and then
    > select all from that file on the different sort orderings.
    >
    > I figured that the only way out would be to write to an array from the
    > first recordset set creation. Then write the report to the screen
    > from the array and users can choose different sort orders and it would
    > extract again and report from that same array.
    >
    > ( 1 )
    > Only problem is that I havn't the faintest idea of how to create and
    > write to the array. Or how to query it. Is it similar to the first
    > database report.?
    >
    > Would anyone perhaps know of a simple tutorial resource that
    > demonstrates this method.?
    >
    > ( 2 )
    > Or do I need to think right outside the box and take the first report
    > in a more efficient way. Problem is that I need to take only one
    > record whenever the value changes in a particular field. Could my SQL
    > statement use ordering on one field to take the recordset but order on
    > another field for the output. I wouldn't have tought so.
    >
    > thanks
    >
    > .les.
    >


    Use the Sort property of the Recordset object:
    http://msdn.microsoft.com/library/en-us/ado270/htm/mdprosortpropertyado.asp


    You can continue to sort/filter the data as you are currently doing, then
    once you've retrieved the filtered data into a Recordset object, set the
    Sort property for that object based on the sort column indicated by the
    user.
     
    Chris Hohmann, Feb 22, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. James Wallace
    Replies:
    0
    Views:
    1,089
    James Wallace
    Oct 17, 2003
  2. rkk
    Replies:
    9
    Views:
    847
    CBFalconer
    Sep 24, 2006
  3. Navin
    Replies:
    1
    Views:
    762
    Ken Schaefer
    Sep 9, 2003
  4. GIMME
    Replies:
    5
    Views:
    200
    Thomas 'PointedEars' Lahn
    Jul 26, 2004
  5. Domenico Discepola

    multi-field array sort using Sort::Fields method

    Domenico Discepola, Apr 27, 2004, in forum: Perl Misc
    Replies:
    6
    Views:
    318
    Uri Guttman
    Apr 28, 2004
Loading...

Share This Page