Solved

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

Posted on 2004-08-08
6
1,052 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
This is an explanation of a simple data model to help parse a JSON feed
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 fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now