How can I compare a text box entry against a list of database values in the Text

as the title states I am trying to compare or validate a text box entry against a list of acceptable values stored in my database. As of now I have taken the values from my database and store them in a List(of String) and I have a for loop that loops through that list and returns true if the values match, if the values do not match it will return false. Below I have attached the code I am currently working with.

Protected Sub txtSearchOC_TextChanged(sender As Object, e As EventArgs) Handles txtSearchOC.TextChanged
    Dim listEType As List(Of String) = New List(Of String)
    Dim eType As String = txtSearchOC.Text
    Dim strResult As String = ""

    lblPrefix.Text = ""
    lblList.Text = ""

    Dim TypeIDQuery As String = "
        SELECT a.OrderCode
        FROM SKU AS a
        INNER JOIN EnrollmentType AS e ON a.EnrollmentTypeID = e.TypeID
        INNER JOIN Enrollment AS f ON e.RecID = f.EnrollmentTypeID
        WHERE f.AccountNumber = '12345';
        "

    Using connEType As New SqlConnection(ConfigurationManager.ConnectionStrings("WarrantyConnectionString").ToString)
        Using cmdEType As New SqlCommand(TypeIDQuery, connEType)
            cmdEType.Parameters.Add("@AccountNumber", SqlDbType.VarChar, 15).Value = "12345"
            connEType.Open()
            Using sdrEType As SqlDataReader = cmdEType.ExecuteReader
                While sdrEType.Read
                    listEType.Add(sdrEType("OrderCode").ToString)
                End While
            End Using
        End Using
    End Using

    For Each Item As String In listEType
        strResult &= Item & ", "
    Next

    For i = 0 To listEType.Count - 1
        If eType = listEType(i) Then
            lblPrefix.Text = "True"
        End If
        If eType <> listEType(i) Then
            lblList.Text = "Error"
        End If
    Next

    'lblList.Text = strResult
End Sub

In the code I declare my list and a variable to store the text value of the text box. To verify that it pulled the appropriate values from the database I have the strResult variable and can confirm that the appropriate values are being stored.

The problem I am having has to do with the For loop I have at the bottom, when I enter in a valid value that is contained in the listEType, I get the confirmation message of "True" indicating it has matched with one of the values, but I also get the "Error" message indicating that it does not match. If I enter in a value that is not contained in the list I only get the "Error" message which is supposed to happen.

My question is, based on the code I have supplied, why would that For loop be returning both "True" and "Error" at the same time for a valid entry? Also, if there is a better way to accomplish what I am trying to do, I am all ears so to speak as I am relatively new to programming.

CodePudding user response：

Well, as others suggested, a drop down (combo box) would be better.

However, lets assume for some reason you don't want a combo box.

I would not loop the data. You have this amazing database engine, and it can do all the work - and no need to loop the data for such a operation. Why not query the database, and check for the value?

Say like this:

Protected Sub txtSearchOC_TextChanged(sender As Object, e As EventArgs) Handles txtSearchOC.TextChanged

    If txtSearchOC.Text <> "" Then

        Dim TypeIDQuery As String = "
            SELECT a.OrderCode FROM SKU AS a
            INNER JOIN EnrollmentType AS e ON a.EnrollmentTypeID = e.TypeID
            INNER JOIN Enrollment AS f ON e.RecID = f.EnrollmentTypeID
            WHERE f.AccountNumber = @AccoutNumber;"

        Using connEType As New SqlConnection(ConfigurationManager.ConnectionStrings("WarrantyConnectionString").ToString)

            Using cmdEType As New SqlCommand(TypeIDQuery, connEType)
                cmdEType.Parameters.Add("@AccountNumber", SqlDbType.NVarChar).Value = txtSearchOC.Text
                connEType.Open()

                Dim rstData As New DataTable
                rstData.Load(cmdEType.ExecuteReader)

                If rstData.Rows.Count > 0 Then
                    ' we have a valid match
                     lblPrefix.Text = "True"
                Else
                    ' we do not have a valid match
                     lblPrefix.Text = "False"
                End If
            End Using
        End Using
    End If

End Sub

So, pull the data into a data table. You can then check the row count, or even pull other values out of that one row. But, I don't see any need for some loop here.