Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bubble-sort on a single Recordset row

    The thing I am trying to do is basic the Left to Right sort option in Excel.

    Here's the overall problem/goal:

    I have a large table from which I extract a range of information in a query and run a sum across the data to get a single row of summed information (a query). I then want to pull that out of the table via .ASP/ADO and use the data to support Graphics Server charts on-the-fly. Currently I can get a chart but it isn't in any order. Graphics Server is a COM add-in that allows you to map data points to various boilerplate charts and generally it's expected that you know the number of data points, the name of the data fields and the values for the fields. I have tried to implement a 'bubble sort' on the row information in ASP/VBScript but, it seems, the algorithm I am using works down a column, not across a singe recordset. (it pages thru a recordset and swaps values if need be.) I can pull the recordset information out and do things like create a nifty table. A nifty, UNSORTED table. and, my brain is getting a little soft after pounding away on this for hours.

    any suggestions? TIA.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bubble-sort on a single Recordset row

    Perhaps something along the following lines might work:
    <UL><LI>Open the recordset.
    <LI>Get the field count (rst.Fields.Count)
    <LI>Declare (ReDim) an array with as many elements as the field count.
    <LI>Loop through the fields of the recordset an pull the field values into the array.
    <LI>Sort the array by your favorite sort method. If the number of fields is not too large, a bubble sort is as good as any.
    <LI>Feed the sorted array elements to Graphics Server (since I don't know Graphics Server, I have no idea about this step).[/list]HTH

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bubble-sort on a single Recordset row

    I'm trying to get to this approach, actually but it's going in baby steps. I'm having trouble just being able to walk thru the fields in the 1row recordset - I keep EOF/BOF True errors all the time, even if I test for EOF and explicitly call RecordSet.MoveFirst. I can grab the data using code like this:

    <font color=red>
    If Not Result.EOF then

    Response.Write "<table border = ""1"" width = ""600"">" & _
    "<tr><td width=""0"" colspan = ""3"">" & _
    "<p align = ""center"">Referring Agencies </td></tr>"

    i=0
    for each fldF in Result.Fields
    Item = fldF.Name

    ItemLength = len(Item)
    ItemLengthTrim = ItemLength - 11
    ItemName = Left(Item, ItemLengthTrim)

    j = i mod 3
    if j = 0 then
    response.write "<tr><td>" & ItemName & " " & fldF.Value & "</td>"
    else
    response.write "<td>" & ItemName & " " & fldF.Value & "</td>"
    end if
    i = i + 1

    next
    response.write "</table>"
    response.write "<hr>"
    end if
    </font color=red>

    But when I try to load the array using very similar code I only get the first field and then BOF - EOF error. Here's the array loading code:

    <font color=red>
    Query = "SELECT * from SumReferringAgencies;"
    Set Result = DataConnection.Execute(Query)

    If Not Result.EOF then
    Result.MoveFirst
    end if

    Dim Referring(35)
    Dim ReferringName(35)


    Count = 1
    For Each fldF in Result.Fields
    Referring(Count) = fldF.Value
    Response.Write Referring(Count) & "<b r>""
    ReferringName(Count) = fldF.Name
    Response.Write ReferringName(Count) & "<b r>""
    Result.MoveNext
    Count = Count + 1
    next
    </font color=red>

    I don't know what exactly is causing the problem but part of it has to do with using the correct method for calling the Field NAME or the Field VALUE. If I try Do While not Result.EOF the fldF.Name/Value variables don't function at all. Here's similar, working array-loading code for a SQL Server:
    <font color=red>
    Count = 0
    Do While Not Result.EOF
    Referring(Count) = Result.Fields(1)
    Referring_Name(Count) = Result.Fields(0)
    Result.MoveNext
    Count=Count+1
    LOOP
    </font color=red>
    The bold sections seem to be where you can reference the value or name of a recordset field in SQL Server. Perhaps there is a different method for Access?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bubble-sort on a single Recordset row

    The errors you get are caused by the use of MoveNext. MoveNext moves to the next record of the recordset, not to the next field. Since your recordset has only one record, you immediately get an error after the first field.

    So remove Result.MoveNext from

    Count = 1
    For Each fldF in Result.Fields
    Referring(Count) = fldF.Value
    Response.Write Referring(Count) & "<b r>""
    ReferringName(Count) = fldF.Name
    Response.Write ReferringName(Count) & "<b r>""
    Result.MoveNext '<--- Remove this line
    Count = Count + 1
    Next fld

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bubble-sort on a single Recordset row

    quite so. silly of me to overlook that. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    Well now it seems to be working in that I can load arrays with the field name and field values from the recordset. Now on to implementing a bubble sort!

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bubble-sort on a single Recordset row

    Why not use the Recordset.GetRows method?

    No looping required.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bubble-sort on a single Recordset row

    I have managed to accomplish everything I basically set out to do with regard to pulling data from Access, sorting it by field value and popping it into Graphics Server to make a chart that displays well and is accurate. Once again, you have been a great help!

    I'd give you a beer but my BTP stack doesn't work and the port's too small and the whole thing's just too messy. I'll have to settle for this: <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bubble-sort on a single Recordset row

    I've never messed with GetRows. Are there specific advantages to it? I'm given to understand that it allows you to disconnect from the db earlier as the data is put into memory or something. However, since I am only getting one row I am involved with paging thru a recordset, etc. -- not that that necessarily makes a difference.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bubble-sort on a single Recordset row

    Thanks, I'll have one on you <IMG SRC=http://www.wopr.com/S/cheers.gif title="good onya, mate">

  10. #10
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bubble-sort on a single Recordset row

    A few of advantages:
    * it's already written and works (probably the major advantage)
    * you can get a specific row by specifying the start
    * you can specify how many rows to get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •