Sorting Data - AndreaVB Visual Basic and VB.NET source code discussion board

AndreaVB Forum : Database : Sorting Data Solved Topic

Poster

Message

niyati
Level: Scholar

Registered: 01-02-2006
Posts: 35

Sorting Data

Hello
I am doing a project in VB 6.0 with microsoft access as the backend

Here i have a database including a table named:Table_Of_Weightage in which i have two fields
Rem_Abbr       Rem_Weight
Ars                  0
Act-r                10
Gels                 2
Bov                  1

Rem_Abbr=medicines abbreviations
Rem_Weight=weightage given to each remedy

everytime the weightage of the medicines changes
Through my coding of one form i am modifying the data of Rem_Weight field,like shown above.
Now the main thing which i am supposed to do is:
After updation of the weightages of the medicines i am simultaneously supposed to display the fields
Rem_Abbr along with their corresponding Rem_Weight values in a separate form.
Before displaying tthe data i want to sort all the fields in the table based on The Rem_Weight fields in a descending order as in the above data should be of the form
Rem_Abbr       Rem_Weight
Act-r                10
Gels                 2
Bov                  1
Ars                  0
Here the data is sorted based on the Rem_Weight field.
I don't know how to do this kind of sorting on a recordset.

OR
Infact i would prefer that after the updation is performed i get all the data of the table in a two dimensional array.
so that i can sort it on the array itself and display the results in a listbox.
But again for that my Rem_Abbr fields may have the additions of other medicines later on.
so i cannot use a static array.
If i get all the data first in an array and then sort an array how should i do it
Here i have to first get the total number of records present in my recordset.
and that also i don't know how to do...
please help me

20-02-2006 at 12:52 PM

Dave Green
Level: Professor

Registered: 20-10-2005
Posts: 90

Re: Sorting Data

Hi
Use the ORDER BY and DESC clauses in your SQL.

Something like:

"SELECT * FROM Table_Of_Weightage ORDER BY Rem_Weight DESC"

DESC tells it to order in a descending fashion (i.e 9 to 0, Z to A)

Hope this helps

Regards
Dave

____________________________
While Breath.Count>0
Live(gbRelax)
Wend

21-02-2006 at 10:04 AM

niyati
Level: Scholar

Registered: 01-02-2006
Posts: 35

Re: Sorting Data

quote:
niyati wrote:
Hello
I am doing a project in VB 6.0 with microsoft access as the backend

Here i have a database including a table named:Table_Of_Weightage in which i have two fields
Rem_Abbr       Rem_Weight
Ars                  0
Act-r                10
Gels                 2
Bov                  1

Rem_Abbr=medicines abbreviations
Rem_Weight=weightage given to each remedy

everytime the weightage of the medicines changes
Through my coding of one form i am modifying the data of Rem_Weight field,like shown above.
Now the main thing which i am supposed to do is:
After updation of the weightages of the medicines i am simultaneously supposed to display the fields
Rem_Abbr along with their corresponding Rem_Weight values in a separate form.
Before displaying tthe data i want to sort all the fields in the table based on The Rem_Weight fields in a descending order as in the above data should be of the form
Rem_Abbr       Rem_Weight
Act-r                10
Gels                 2
Bov                  1
Ars                  0
Here the data is sorted based on the Rem_Weight field.
I don't know how to do this kind of sorting on a recordset.

OR
Infact i would prefer that after the updation is performed i get all the data of the table in a two dimensional array.
so that i can sort it on the array itself and display the results in a listbox.
But again for that my Rem_Abbr fields may have the additions of other medicines later on.
so i cannot use a static array.
If i get all the data first in an array and then sort an array how should i do it
Here i have to first get the total number of records present in my recordset.
and that also i don't know how to do...
please help me

Hi Thank for the reply
Actually i did try it out my coding was like this

adoSortWeight.Open "SELECT * FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo
The sorting was performed.then what i did was i tried to get all this data into an array like this

Dim myarray() As Variant

ReDim Preserve myarray(Module1.totalrem, 1)

For i = 0 To Module1.totalrem - 1 Step 1
        adoSortWeight.Open "SELECT * FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo
        myarray(i, 0) = adoSortWeight!Rem_Abbr

        myarray(i, 1) = adoSortWeight!Rem_Weight
        frmremedies.lstunsort.AddItem myarray(i, 0) & "----" & myarray(i, 1)
        adoSortWeight.Close
    Next i
after sorting i require to get all this data into an array named myarray.
With that statement the data is getting sorted at that instance but when i am first putting the elements in the array then displaying it in the list box only the first Rem_Abbr field along with is weightage is being displayed till the for loop continues..

Am i going wrong in the declaration of my array somewhere??

totalrem is a module level variable holding the total count Rem_Abbr from the table
Currently it's value is 654 which means 654 rows and 2 cols but as i have told before this no 654 is not fixed...
Where am i going wrong??
I hope i have been ok with explaining my problem to you..
If still any confusions please let me know...
Waitin for your reply
Niyati

21-02-2006 at 12:28 PM

admin
Level: Administrator

Registered: 04-04-2002
Posts: 530

Re: Sorting Data

you don't have to open the query inside the For...Next loop or it will restart from the first row of the table...

try something like this:

Dim myarray() As Variant

ReDim Preserve myarray(Module1.totalrem, 1)

adoSortWeight.Open "SELECT * FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo

For i = 0 To Module1.totalrem - 1 Step 1
        myarray(i, 0) = adoSortWeight!Rem_Abbr
        myarray(i, 1) = adoSortWeight!Rem_Weight
        frmremedies.lstunsort.AddItem myarray(i, 0) & "----" & myarray(i, 1)
        adoSortWeight.MoveNext
    Next i
adoSortWeight.Close

you can also increment totalrem while you browse your recordset...the code changes this way:

Dim myarray() As Variant

adoSortWeight.Open "SELECT * FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo
Module1.totalrem=0
do while not adoSortWeight.EOF
        ReDim Preserve myarray(Module1.totalrem, 1)
        myarray(Module1.totalrem, 0) = adoSortWeight!Rem_Abbr
        myarray(Module1.totalrem, 1) = adoSortWeight!Rem_Weight
        frmremedies.lstunsort.AddItem myarray(Module1.totalrem, 0) & "----" & myarray(Module1.totalrem, 1)
        Module1.totalrem=Module1.totalrem+1
        adoSortWeight.MoveNext
Next i
adoSortWeight.Close

[Edited by admin on 21-02-2006 at 05:08 PM GMT]

____________________________
AndreaVB

21-02-2006 at 04:04 PM

niyati
Level: Scholar

Registered: 01-02-2006
Posts: 35

Re: Sorting Data

quote:
admin wrote:
you don't have to open the query inside the For...Next loop or it will restart from the first row of the table...

try something like this:

Dim myarray() As Variant

ReDim Preserve myarray(Module1.totalrem, 1)

adoSortWeight.Open "SELECT * FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo

For i = 0 To Module1.totalrem - 1 Step 1
        myarray(i, 0) = adoSortWeight!Rem_Abbr
        myarray(i, 1) = adoSortWeight!Rem_Weight
        frmremedies.lstunsort.AddItem myarray(i, 0) & "----" & myarray(i, 1)
        adoSortWeight.MoveNext
    Next i
adoSortWeight.Close

you can also increment totalrem while you browse your recordset...the code changes this way:

Dim myarray() As Variant

adoSortWeight.Open "SELECT * FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo
Module1.totalrem=0
do while not adoSortWeight.EOF
        ReDim Preserve myarray(Module1.totalrem, 1)
        myarray(Module1.totalrem, 0) = adoSortWeight!Rem_Abbr
        myarray(Module1.totalrem, 1) = adoSortWeight!Rem_Weight
        frmremedies.lstunsort.AddItem myarray(Module1.totalrem, 0) & "----" & myarray(Module1.totalrem, 1)
        Module1.totalrem=Module1.totalrem+1
        adoSortWeight.MoveNext
Next i
adoSortWeight.Close

[Edited by admin on 21-02-2006 at 05:08 PM GMT]

Hye
Thankyou so much for you r reply
Yes it is working but now there is another problem which is
in the first time when someone agrees for a symptom to be present... the medicine database is updated and according to your code displayed in the descending order in the listbox i.e 654 medicines.
But that happens the first time.
When once again a new symptom is said yes for then the count of the medicines is getting doubled up i.e 1308 which should not be happening.
That means i have to blank out the array at the end when it is used fully.
Here i wish to get only the latest updations or updated table not the additions of previous ones.
Here is my code now

Dim myarray() As Variant
With adoSortWeight
.CursorLocation = adUseClient
.Open "SELECT Rem_Abbr,Rem_Weight FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo, adOpenKeyset, adLockReadOnly
MsgBox .RecordCount
End With

ReDim Preserve myarray(adoSortWeight.RecordCount, 1)
    For i = 0 To adoSortWeight.RecordCount - 1 Step 1
        myarray(i, 0) = adoSortWeight!Rem_Abbr
        myarray(i, 1) = adoSortWeight!Rem_Weight
        frmremedies.lstunsort.AddItem myarray(i, 0) & "----" & myarray(i, 1)
        adoSortWeight.MoveNext
    Next i
    MsgBox (frmremedies.lstunsort.ListCount)
adoSortWeight.Close

I should be getting the new updated table only ...
What can i do

Thanks
waiting for your reply
Niyati

22-02-2006 at 01:47 PM

steve_w
Level: Moderator

Registered: 18-04-2003
Posts: 1156

Re: Sorting Data

Insert the code below, above your for next loop

frmremedies.lstunsort.Clear

Steve

22-02-2006 at 02:03 PM

niyati
Level: Scholar

Registered: 01-02-2006
Posts: 35

Re: Sorting Data

quote:
steve_w wrote:
Insert the code below, above your for next loop

frmremedies.lstunsort.Clear

Steve

Hey That was a pretty good way eh...

But sorry that is not what i want everything has to be done in front only
The remedies should not be dissappearing at any point of time
Give me some other solution please
Niyati

22-02-2006 at 05:04 PM

steve_w
Level: Moderator

Registered: 18-04-2003
Posts: 1156

Re: Sorting Data

Hi

I said to clear it each time before you populate it. It appears to me that you are adding to your list box each time the function is run.

Steve

23-02-2006 at 09:38 AM

niyati
Level: Scholar

Registered: 01-02-2006
Posts: 35

Re: Sorting Data

quote:
steve_w wrote:
Hi

I said to clear it each time before you populate it. It appears to me that you are adding to your list box each time the function is run.

Steve

No No i exactly wrote the code you told me to that is clear the listbox....But the thing is that the new data should appear in front of the doctor everytime.There should be no empty listbox...The program flow goes like this

Question is asked to the doc->doc responds a Yes Or No->If yes then update all the medicines for the symptom->display all the medicines in the sorted form on basis of their weightage->display new question and it continuesssss....
even when the new question is displayed the doctor still wishes to see the previous results of updataion.He might even click no for a question so it is obvious that the previous details should be present in the listbox....Hence i can't clear it...
Please give me some solution ...i am going mad...

23-02-2006 at 01:26 PM

steve_w
Level: Moderator

Registered: 18-04-2003
Posts: 1156

Re: Sorting Data

quote:
...i am going mad..

No more than the rest of us.

Post your code up as it is now and i'll take a look.

Cheers

23-02-2006 at 01:36 PM

niyati
Level: Scholar

Registered: 01-02-2006
Posts: 35

Re: Sorting Data

quote:
steve_w wrote:

quote:
...i am going mad..

No more than the rest of us.

Post your code up as it is now and i'll take a look.

Cheers

here is a part of the code...I am sorry for the trouble but you see this is the only way i am trying to learn...

Private Sub cmdYes_Click()
Set adoSortWeight = New ADODB.Recordset

    frmremedies.lstunsort.clear
    Dim myarray() As Variant
    With adoSortWeight
        .CursorLocation = adUseClient
        .Open "SELECT Rem_Abbr,Rem_Weight FROM Table_Of_Weightage ORDER BY Rem_Weight DESC", adoConnHomeo, adOpenKeyset, adLockReadOnly
        MsgBox .RecordCount
    End With

    ReDim Preserve myarray(adoSortWeight.RecordCount, 1)
        For i = 0 To adoSortWeight.RecordCount - 1 Step 1
            myarray(i, 0) = adoSortWeight!Rem_Abbr
            myarray(i, 1) = adoSortWeight!Rem_Weight
            frmremedies.lstunsort.AddItem myarray(i, 0) & "       " & myarray(i, 1)



            adoSortWeight.MoveNext
        Next i

    MsgBox (frmremedies.lstunsort.ListCount)
    adoSortWeight.Close

'This code is for finding and displaying the new question
    QuesIdYes = adoRecQues!Rem_Yes
    MsgBox (QuesIdYes)
    adoRecQues.Find "Ques_Id_no='" & adoRecQues.Fields("Rem_Yes") & "'", adSearchForward
    lblquestions.Caption = adoRecQues!Questions
end sub


Hey i tried something .....new
what i did was that the statement frmremedies.lstunsort.clear i put it right at the start of the click of the yes button...
Howz that?
WIll this give me any kind of bugs later on in the program...

23-02-2006 at 02:16 PM

steve_w
Level: Moderator

Registered: 18-04-2003
Posts: 1156

Re: Sorting Data

quote:
what i did was that the statement frmremedies.lstunsort.clear i put it right at the start of the click of the yes button...

Thats where i told you to put it

You need to empty it out before you run your for next loop. Other wise next time you run the routine you are going to add the values to the list box again.

Steve

23-02-2006 at 04:07 PM

niyati
Level: Scholar

Registered: 01-02-2006
Posts: 35

Re: Sorting Data

Hye Again Thanks a ton.It is really working.....

24-02-2006 at 03:54 AM

AndreaVB Forum : Database : Sorting Data Solved Topic


	Surf To:



	AndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2007 Andrea Tincani