Combo box sort problems

[Kingkev83]

I have a problem, i use a combo box to display members details in the following format
display member: (concatinated field) Surname &' '& Forename &' '& MembershipNo & - & DateofBirth as `Memberdetails`
Valuemember : MembershipNo

I would like to sort the combo box by surname/MemberDetails but it currently only sorts by membershipNo. I have tried the sorted = true value which does sort by surname but does not sort the Valuemember: membershipNo also so i get a member with an incorrect membershipNo.

i use the following code to populate the combo box:

Common.vb
Public Function PopulateCombo(B yVal cboBox As ComboBox, ByVal psSQL As String, ByVal IsTrue As Boolean, ByVal ErrorText As String, ByVal Value As String, ByVal Display As String, Optional ByVal Othertext As String = "") As DataSet
Dim SelectCmd As Odbc.OdbcComman d
SelectCmd = New System.Data.Odb c.OdbcCommand
SelectCmd.Conne ction = gThisConn
If IsTrue Then
If Othertext = "" Then
SelectCmd.Comma ndText = "(Select -1 as `" & Value & "`, ' ' as `" & Display & "` from member) Union (" & psSQL & ")"
Else
SelectCmd.Comma ndText = "(Select -1 as `" & Value & "`, '--All-- ' as `" & Display & "`) Union All (" & psSQL & ")"
End If

Else
SelectCmd.Comma ndText = psSQL
End If


Dim thisDA As Odbc.OdbcDataAd apter
thisDA = New System.Data.Odb c.OdbcDataAdapt er
thisDA.SelectCo mmand = SelectCmd

Try
Dim thisDs As DataSet
thisDs = New DataSet

thisDA.Fill(thi sDs)
cboBox.DataSour ce = thisDs.Tables(0 )
cboBox.ValueMem ber = thisDs.Tables(0 ).Columns(0).To String
cboBox.DisplayM ember = thisDs.Tables(0 ).Columns(1).To String
Catch Ex As Exception
MessageBox.Show ("Error Populating " & ErrorText & " - " & Ex.Message)
Finally
SelectCmd.Dispo se()
thisDA.Dispose( )
End Try
Return Nothing
End Function

I use a global string for PsSQL:
'Public Const gpopcboMemberSe arch As String = "Select MembershipNo, ([Surname] &' '&[forename] &' - '& [MembershipNo] & ' - ' & [DateOfBirth]) as `MemberDetails` from [Member] Where [Active] = True Order by [Surname], [Forename] "

this code calls the populate combo code above to populate the combobox

PopulateCombo(c boMemberSearch, gpopcboMemberSe arch & " Order By Surname", True, "MemberSele ct", "Membership No", "Surname")


i need to sort ASC by surname then forename and the relevent Valuemember: memberhsipNo needs to correspond with the member selected.

can anyone help solve my Problem?

[Dököll]

I have a problem, i use a combo box to display members details in the following format
display member: (concatinated field) Surname &' '& Forename &' '& MembershipNo & - & DateofBirth as `Memberdetails`
Valuemember : MembershipNo

I would like to sort the combo box by surname/MemberDetails but it currently only sorts by membershipNo. I have tried the sorted = true value which does sort by surname but does not sort the Valuemember: membershipNo also so i get a member with an incorrect membershipNo.

i use the following code to populate the combo box:

Common.vb

 Public Function PopulateCombo(ByVal cboBox As ComboBox, ByVal psSQL As String, ByVal IsTrue As Boolean, ByVal ErrorText As String, ByVal Value As String, ByVal Display As String, Optional ByVal Othertext As String = "") As DataSet
        Dim SelectCmd As Odbc.OdbcCommand
        SelectCmd = New System.Data.Odbc.OdbcCommand
        SelectCmd.Connection = gThisConn
        If IsTrue Then
            If Othertext = "" Then
                SelectCmd.CommandText = "(Select -1 as `" & Value & "`, '                                                  ' as `" & Display & "` from member) Union (" & psSQL & ")"
            Else
                SelectCmd.CommandText = "(Select -1 as `" & Value & "`, '--All--                                           ' as `" & Display & "`) Union All (" & psSQL & ")"
            End If

        Else
            SelectCmd.CommandText = psSQL
        End If


        Dim thisDA As Odbc.OdbcDataAdapter
        thisDA = New System.Data.Odbc.OdbcDataAdapter
        thisDA.SelectCommand = SelectCmd

        Try
            Dim thisDs As DataSet
            thisDs = New DataSet

            thisDA.Fill(thisDs)
            cboBox.DataSource = thisDs.Tables(0)
            cboBox.ValueMember = thisDs.Tables(0).Columns(0).ToString
            cboBox.DisplayMember = thisDs.Tables(0).Columns(1).ToString
        Catch Ex As Exception
            MessageBox.Show("Error Populating " & ErrorText & " - " & Ex.Message)
        Finally
            SelectCmd.Dispose()
            thisDA.Dispose()
        End Try
        Return Nothing
    End Function

I use a global string for PsSQL:

'Public Const gpopcboMemberSearch As String = "Select MembershipNo, ([Surname] &' '&[forename] &' - '& [MembershipNo] & ' - ' & [DateOfBirth]) as `MemberDetails` from [Member] Where [Active] = True Order by [Surname], [Forename] "

this code calls the populate combo code above to populate the combobox
 
PopulateCombo(cboMemberSearch, gpopcboMemberSearch & " Order By Surname", True, "MemberSelect", "MembershipNo", "Surname")

i need to sort ASC by surname then forename and the relevent Valuemember: memberhsipNo needs to correspond with the member selected.

can anyone help solve my Problem?

Hello there, Kingkev83!

You can Order By more than one field, that should fields you need. Try it!

Dököll