?
Solved

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

Posted on 2004-08-08
6
Medium Priority
?
1,074 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 18

Accepted Solution

by:
JR2003 earned 2000 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Six Sigma Control Plans
Progress
Suggested Courses

752 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