Link to home
Start Free TrialLog in
Avatar of migit03
migit03

asked on

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

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-
ASKER CERTIFIED SOLUTION
Avatar of JR2003
JR2003

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of migit03
migit03

ASKER

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.
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
Avatar of migit03

ASKER

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"
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"


Avatar of migit03

ASKER

THat was the problem now it works.