Sorting Order

[Jagjit]

Hi

I have items with code as alphanumeric . For e.g I1,I2,I3,I4,I5,I6,I7,I8,I9,I10,I11,....I20,I21.

In listview i am displaying code & name. When i click on code it sorts like this I1,I10,I11,I2 whereas i want it should display like this I1,I2, & so on.

Thanks

[Niya]

Simple:-

'
        Dim values As String() = New String() {"I1", "I23", "I14", "I12", "I1", "I3", "I11"}
 
        values = values.OrderBy(Function(s) CInt(s.Substring(1))).ToArray

EDIT

Oh God...I just realized this was a VB6 question and you also implied that you have a sort already. Well the idea in the above code is that you sort on the numbers by extracting them from each of the strings and converting them into numbers.

How are you sorting ? Do you have some routine that sorts ? If so, post it up.

[Jagjit]

Hi Niya

Records can be in hundreds or thousands

Thanks

[Niya]

Did you see my post edit ?

[Magic Ink]

At the moment you are getting an alphabetical sort such as you would get in a dictionary of words.
To sort 'codes' like these numerically you should pad them out to equal length; this is typically done with leading zeroes as in;

0I1,0I2,0I3,0I4,0I5,0I6,0I7,0I8,0I9,I10,I11,....I20,I21

but you can use any character you wish.

Oooops!
Correction: I01,I02,I03,I04,I05,I06,I07,I08,I09,I10,I11,....I20,I21

[Jagjit]

Hi

This is the code

Dim itmX As ListItem
Dim intcount As Integer

lvwitem.ListItems.Clear
For intcount = 0 To rstitem.RecordCount - 1
Set itmX = lvwitem.ListItems.Add(, "A" & rstitem("icode"))
itmX.Text = rstitem("icode")
itmX.SubItems(1) = rstitem("idesc")
rstitem.MoveNext
Next intcount
If rstitem.RecordCount > 0 Then
rstitem.MoveFirst
End If

rsitem is Items table recordset

Thanks

[Niya]

Wait a minute, you're getting this from a database ? What SQL Select statement are you using to fill that RecordSet ? You might just be able to palm that sort off to database engine which is far better at sorting than any implementation you can provide.

[Jagjit]

Hi Niya

It is like this

rstitem.Open "select * from itemmaster", cnn1, adOpenStatic, adLockOptimistic

Thanks

[Niya]

Use this Select statement instead:-

select * from itemmaster order by Cast(substring(icode,2,len(icode) - 1) as int)

I'm assuming the column in question is icode and the database is SQL Server.

[Jagjit]

Hi

It is giving me error Undefined function cast in expression
rstitem.Open "SELECT * FROM itemmaster ORDER BY Cast(substring(icode,2,len(icode) - 1))", cnn1, adOpenStatic, adLockOptimistic

Thanks

[Niya]

What database are you using ?

[Jagjit]

Hi

I am using MS-Access

Thanks

[Niya]

Use this instead then:-

SELECT * FROM itemmaster ORDER BY CLng(Mid(icode,2,len(icode) - 1))

[Schmidt]

Simple:-

'
        Dim values As String() = New String() {"I1", "I23", "I14", "I12", "I1", "I3", "I11"}
 
        values = values.OrderBy(Function(s) CInt(s.Substring(1))).ToArray

Oh God...I just realized this was a VB6 question ...

Yeah - sure ...

Here's some similar thing in VB6, using vbRichClient5:

Dim Arr As cArrayList
Set Arr = New_c.ArrayList(vbString, "XZ 1", "XA 1", "I23", "I14", "I2", "I1", "I3", "X-11", "X-1")
    Arr.Sort cmpLogical

then followed by a Print-Out of contents:

Dim i As Long
For i = 0 To Arr.Count - 1
  Debug.Print Arr(i)
Next

gives the following "logically" sorted List:
I1
I2
I3
I14
I23
X-1
X-11
XA 1
XZ 1

Olaf

[Jagjit]

Hi Niya

Recordset is o.k

rstitem.Open "SELECT * FROM itemmaster ORDER BY val(Mid(icode,2,len(icode) - 1))", cnn1, adOpenStatic, adLockOptimistic

But in Listview it is not displaying as per recordset


Dim itmX As ListItem
Dim intcount As Integer

lvwitem.ListItems.Clear
For intcount = 0 To rstitem.RecordCount - 1
Set itmX = lvwitem.ListItems.Add(, "A" & rstitem("icode"))
itmX.Text = rstitem("icode")
itmX.SubItems(1) = rstitem("idesc")
rstitem.MoveNext
Next intcount
If rstitem.RecordCount > 0 Then
rstitem.MoveFirst
End If


Thanks

[Niya]

Yeah - sure ...

Here's some similar thing in VB6, using vbRichClient5:

Dim Arr As cArrayList
Set Arr = New_c.ArrayList(vbString, "XZ 1", "XA 1", "I23", "I14", "I2", "I1", "I3", "X-11", "X-1")
    Arr.Sort cmpLogical

then followed by a Print-Out of contents:

Dim i As Long
For i = 0 To Arr.Count - 1
  Debug.Print Arr(i)
Next

gives the following "logically" sorted List:
I1
I2
I3
I14
I23
X-1
X-11
XA 1
XZ 1

Olaf

I was wondering how long it would take you to bite

Hi Niya...Recordset is o.k.....But in Listview it is not displaying as per recordset

lol one problem at a time....Did my altered Select query sort according to your need ? You didn't confirm that.

[Jagjit]

Hi Niya

There is no problem in select statement . It gives me correct results when i see the results in below code using msgbox that is also correct but problem is when it displays in Listview it is not correct.

lvwitem.ListItems.Clear
For intcount = 0 To rstitem.RecordCount - 1
Set itmX = lvwitem.ListItems.Add(, "A" & rstitem("icode"))
itmX.Text = rstitem("icode")
itmX.SubItems(1) = rstitem("idesc")
rstitem.MoveNext
Next intcount
If rstitem.RecordCount > 0 Then
rstitem.MoveFirst
End If

Thanks

[Niya]

So this is another problem entirely ?

[firoz.raj]

Hi ,Can you post the screenshot of msgbox . any way can you try the following way .

rstitem.Open "SELECT * FROM itemmaster ORDER BY val(Mid(icode,2,len(icode) - 1))", cnn1, adOpenStatic, adLockOptimistic

If rsitem.state=adstateopen then
 if not (rsitem.eof and rsitem.bof) then 'if record found then

Dim itmX As ListItem
Dim intcount As Integer

lvwitem.ListItems.Clear
For intcount = 0 To rstitem.RecordCount - 1
Set itmX = lvwitem.ListItems.Add(, "A" & rstitem("icode"))
itmX.Text = rstitem("icode")
itmX.SubItems(1) = rstitem("idesc")
rstitem.MoveNext
Next intcount 
'If rstitem.RecordCount > 0 Then 'it does not seems to check something found then Moving pointer at very first record of the recordset
'rstitem.MoveFirst
'End If
end if
End if

[Kbre]

Since you are using a personalized sorting in the SQL Query, you must ensure that the Sorted Property of the ListView Control is False (other way the listview will re-sort in the normal text string mode and you will have the list incorrectly sorted again).

lvwItem.Sorted = False