Solved

VB-Ordering an array largest to smallest- DB problem -

Posted on 2004-08-08
6
1,057 Views
Last Modified: 2012-06-21
This question is a nasty bugger which has bothered for some time.
I have worked on it [in VB]so long the code has become a maze of patches, failed fixes, dead ends and ignorant blunderings.
I've been working onthis program for some time and the prupose of this segment of the code is two fold.
1)Put the data in an area onto an Access database(note i'm using VB6 and the database must be in 2000 (I'm using a newer version of Access and converting back)for it to be compatible)
2)Arrange the data in the array[called BestKeeper(x,y)] in order from greatest to smallest based upon the data in spot 25[BestKeeper(25,y)]
    -To accomplish task 2, I decided to first move the data to access then use access to arrange the data and spit it back into the array.
This has proved too difficult for my beggining programming experience.
Here is the code I'm using.

Settings for Data2.Recordset
-Using Dynaset
-BOF ACtion= move first
-EOF action=move last

Private Sub Order()
Dim selector As Integer
Dim rotater As Integer
'This first part was designed to erase the old database before updating
Data2.Recordset.AddNew
Data2.Recordset.MoveLast
Do While Data2.Recordset.BOF = False
Data2.Recordset.MovePrevious
If Data2.Recordset.BOF = False Then
Data2.Recordset.Delete
End If
Loop
'THis Part copies the data from BestKeeper to the database(each selector entry has 27 corresponding rotater entries) THere are 50 selector values
Data2.Recordset.AddNew
selector = 0
rotater = 0
Data2.Recordset.MoveFirst

Do While selector <> 50
    Do While rotater <> 27
    Data2.Recordset.MoveLast
    Data2.Recordset.Edit

    Data2.Recordset(rotater).Value = BestKeeper(rotater, selector)
    Data2.Recordset.Update
    rotater = 1 + rotater
    Clock = Clock + 1 'TEST
    Loop
rotater = 0
Data2.Recordset.MoveLast
Data2.Recordset.MoveNext
Data2.Recordset.AddNew
Data2.Recordset.Update
selector = 1 + selector
Loop

'I thought this part would be the command to order the access database
Data2.Recordset.Sort = "25"
Data2.Recordset.AbsolutePosition = 1
Data2.Recordset.Edit
Data2.Recordset.Update
selector = 0
rotater = 0
Data2.Recordset.MoveFirst
Data2.Recordset.MoveNext
'This part copies the data back to the array
Do While selector <> 50
Do While rotater <> 27

Text27.DataField = rotater
BestKeeper(rotater, selector) = Text27.Text
rotater = rotater + 1
Loop
selector = selector + 1
Data2.Recordset.MoveNext
rotater = 0
Loop
DoEvents'This is here so i can view the changes to the DBgrid on the form
End Sub


So Either tell me I'm all wrong and give me a much easier way to do this
OR
Point out the stupid mistakes I made

Questions Welcomed- No Rush- I just want the best and most ellagant sollution-
0
Comment
Question by:migit03
  • 3
  • 3
6 Comments
 
LVL 18

Accepted Solution

by:
JR2003 earned 500 total points
ID: 11748799
You could sort the data in a list view instead.
Just load the data from the array into the listview in Report viewing mode.
Then just sort the list view on column 25

To use the sample code below do the following
1. start a new project
2 Add 3 buttons and a ListView control
3 Paste the code below into the form

To run the code start the proect
Press button Command 1 to populate the listView from the array
Press button Command 2 to sort the ListView
Press Button 3 to store the data back in the array in order


Option Explicit

Private BestKeeper(1 To 27, 1 To 50) As String

Private Sub Command1_Click()

    'Populate the ListView from the array
    Dim i As Long
    Dim j As Long
    Dim itmX As ListItem
    Dim subItmX As ListSubItem
   
    With ListView1.ListItems
        For i = 1 To 50
            Set itmX = .Add(, , BestKeeper(1, i))
            For j = 2 To 27
                If j = 25 Then
                    'Format this column so a text sort will work:
                    Set subItmX = itmX.ListSubItems.Add(, , Format(BestKeeper(j, i), "000000"))
                Else
                    Set subItmX = itmX.ListSubItems.Add(, , BestKeeper(j, i))
                End If
            Next j
        Next i
    End With
   
   
End Sub

Private Sub Command2_Click()

    'Sort the ListView by column 25
    With ListView1
        .Sorted = True
        .SortKey = 24 'goes 0 to n-1 so column 24 is really column 25
    End With
   

End Sub

Private Sub Command3_Click()

    'Sore the data back in the BestKeeper array in order overwriting what was already there.
    Dim i As Long
    Dim j As Long
    Dim itmX As ListItem
    With ListView1.ListItems
        For i = 1 To 50
            Set itmX = .Item(i)
            BestKeeper(1, i) = itmX.Text
            For j = 2 To 27
                BestKeeper(j, i) = itmX.ListSubItems.Item(j - 1).Text
            Next j
        Next i
    End With
End Sub

Private Sub Form_Load()

    'Initialise the columns of the listview
    Dim i As Long
    Dim j As Long
   
    With ListView1
        .View = lvwReport
        With .ColumnHeaders
            For i = 1 To 27
                .Add , , "Column " & i
            Next i
        End With
    End With
   
    'Add some random data to the BestKeeper Array
    For i = 1 To 50
        For j = 1 To 27
            BestKeeper(j, i) = Fix(Rnd() * 10000)
        Next j
    Next i
   
End Sub

0
 
LVL 1

Author Comment

by:migit03
ID: 11749596
It's a good answer but when u said
"SortKey = 24 'goes 0 to n-1 so column 24 is really column 25"
isn't that wrong because u also said
"Private BestKeeper(1 To 27, 1 To 50) As String"

-What exactly is a list view used for?
-What way does "Format(BestKeeper(j, i), "000000"))" format it as?

This solution seems good and if no one else interjects I think I'll use this.
Thanks for responding.
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11750994
migit03,

The column headers on the LISTVIEW are indexed 0 to n-1. i.e. 0 to 26. It has nothing to do with the arrays upper and lower bounds.

The ListView is a control used to display lists of items with columns. It's a very similar control to the file view control on the right-hand side of a Windows Explorer screen. The reason I used a ListView for sorting rather than just a Qucksort on the array is that the results as displayed.

Format(BestKeeper(j, i), "000000"))" would format 345 to "000345".
The reason this is used is this:
When a ListView is used to sort items it will only perform a text sort, not a numeric sort. So for example if you are sorting the numbers 1, 2, 5, 10 and 11
The sorted version would then look like this: 1, 10, 11, 2, 5.
All the format statement does is to fill blanks with the character zero so you would be sorting:
001, 002, 005, 010, 011 - which will sort correctly with a text sort.

When you are putting this value back into the array you can convert it back to a number with the "Val" function so it stays as original.

JR2003
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:migit03
ID: 11752989
I tested out this code in my version of VB and it crashes. I tried to see if I copied it wrong but it seems like those commands are not supported.
IT shows this line ".View = lvwReport" and says object required. I don't know....
You mentioned Qucksort that seems like what i might want... i really don't need hte data "displayed"
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11753123
lvwReport has a value of 3
so the line could just be rewritten ".View = 3"
lvwReport is just one of the ListViewConstants Enums

Have you got the listview control on your form?  You have to put a listview control on the form.

I think it might be available only on the Professional and Enterprise editions of VB.
You can find the listview control in the project/components dialog under "Microsoft Windows Common Controls"


0
 
LVL 1

Author Comment

by:migit03
ID: 11753496
THat was the problem now it works.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question