Question ComboBox Validation

[CHUCKD]

Hello Everyone,

I have been searching for an easier way to validate the text property of my combobox. Below is a background of what I am try to accomplish:

I have a from that contains many controls, mainly comboboxes that are bound to datatables. I have these set up in such a fashion as to when the user begins to type, the values begin to show and auto populate the combobox. This makes the selection process easier to select an actual value in the list. But, as we all know, you can lead a horse to water but you can't make him drink. Users still have the ability to type something that is not in the list. I do not want to allow them to leave the combobox until the have selected or typed a valid value from the list because I am building a SQL query to retrieve data for a later form. I don't want to lose the above mentioned functionality of being able to type it in. I currently have it working as follows, but there must be a more elegant way to do this. Open to suggestions.

I first populate my combo box like so:

Dim strSQL As String = "Select NOMSIZ_0" _
                       & " FROM PRODUCTION.YNOMSIZ AS Y"
        Dim da As New SqlDataAdapter(strSQL, conn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cboNomSize
            .DataSource = dt
            .DisplayMember = "NOMSIZ_0"
            .Text = "*"
        End With

then validate like this:

Private Sub cboNomSize_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles cboNomSize.Validating

        Dim strSQL As String = "Select NOMSIZ_0" _
                       & " FROM PRODUCTION.YNOMSIZ AS Y"
        Dim da As New SqlDataAdapter(strSQL, conn)
        Dim dt As New DataTable
        da.Fill(dt)
        Dim dr As DataRow
       
        For Each dr In dt.Rows
            If (dr("NOMSIZ_0")) = cboNomSize.Text Then
                
                GoTo SKIP
            
            End If
           
        Next
        MsgBox("Please select a value from the list")
        Me.cboNomSize.Select()
        dt.Dispose()
SKIP:

    End Sub

The part I don't like the most is that I feel like I should be able to use the list already loaded in the combobox data, but I can't figure out how to do it. So I end up having to reload it, this seem like a waste of time. Any help would be greatly appreciated.

Thanks,
Chuck

 

[jmcilhinney]

Woah! You definitely shouldn't be querying the database in the Validating event handler. You've already got the data. Compare the Text to that. This is untested but I think it should work:

Dim field = Me.ComboBox1
Dim value = field.Text
Dim item = field.Items.Cast(Of DataRowView).FirstOrDefault(Function(row) field.GetItemText(row).Equals(value, StringComparison.CurrentCultureIgnoreCase))

If item Is Nothing Then
    'No match.
    e.Cancel = True
Else
    field.SelectedItem = item
End If

That will find the first item where the text of that item is equal to the current Text, ignoring case. If there is no such item then validation fails, otherwise that item is selected.

 

[jmcilhinney]

Also, never use GoTo under any circumstances. There's never a scenario that can't be handled using a better construct.

 

[CHUCKD]

That is exactly what I was looking for, I knew I should not have to re-query the information. This works like a champ. Thank you for your prompt and accurate response!