Geek asks and answers

VB.NET - a search function using a text field and a combo box

Good day to all! I would like to ask for help regarding my code here. The main problem is finding the keyword provided by the text box from the MySQL database. Here is my GUI for reference.

The GUI of my program

When I enter my search key in the text box and the selected column in the combo box, the search query will have its output in the list. I tried many combinations to get the output, but to no avail.

Here is my code for you to help me.

Private Sub Search()

    lviClientList.Items.Clear()
    Dim strSqlSearch As String = "SELECT code, Company, StAdd, City, ContactPerson, Phone, Mobile, Email, Remarks FROM tblclients WHERE '@Column' LIKE '%" & txtSearchCriteria.Text & "%'"

    Dim item As New ListViewItem()

    If cboColumns.SelectedIndex = 0 Then
        column = "code"
    ElseIf cboColumns.SelectedIndex = 1 Then
        column = "Company"
    ElseIf cboColumns.SelectedIndex = 2 Then
        column = "StAdd"
    ElseIf cboColumns.SelectedIndex = 3 Then
        column = "City"
    ElseIf cboColumns.SelectedIndex = 4 Then
        column = "ContactPerson"
    ElseIf cboColumns.SelectedIndex = 5 Then
        column = "Phone"
    ElseIf cboColumns.SelectedIndex = 6 Then
        column = "Mobile"
    ElseIf cboColumns.SelectedIndex = 7 Then
        column = "Email"
    ElseIf cboColumns.SelectedIndex = 8 Then
        column = "Remarks"
    End If

    Dim mysqlCommand As New MySqlCommand(strSqlSearch, mysqlConnection)
    mysqlCommand.Parameters.AddWithValue("@Column", column)

    Try
        mysqlConnection.Open()
        mysqlReader = mysqlCommand.ExecuteReader()

        Do While mysqlReader.Read()

            item = lviClientList.Items.Add(mysqlReader("code").ToString)
            item.SubItems.Add(mysqlReader("Company").ToString)
            item.SubItems.Add(mysqlReader("StAdd").ToString)
            item.SubItems.Add(mysqlReader("City").ToString)
            item.SubItems.Add(mysqlReader("ContactPerson").ToString)
            item.SubItems.Add(mysqlReader("Phone").ToString)
            item.SubItems.Add(mysqlReader("Mobile").ToString)
            item.SubItems.Add(mysqlReader("Email").ToString)
            item.SubItems.Add(mysqlReader("Remarks").ToString)

        Loop

    Catch ex As Exception

        MsgBox("No results found.", MsgBoxStyle.OkOnly, "Project Analysis System")

    Finally

        mysqlReader.Close()
        mysqlConnection.Close()

    End Try

End Sub
+5
source share
1 answer

It is not clear why your code does not work. Try changing the code in the sentence.Catch

MsgBox("No results found.", MsgBoxStyle.OkOnly, "Project Analysis System")

at

Msgbox(ex.Message.ToString(), MsgBoxStyle.OkOnly, "Project Analysis System")

so that you know what the exact mistake is.

ColumnName, . WHERE , .

,

Private Sub Search()

    lviClientList.Items.Clear()
    Dim item As New ListViewItem()
    Dim _isFound As Boolean = False

    Dim colName() As String = {"code", "Company", "StAdd", "City", "ContactPerson", "Phone", "Mobile", "Email", "Remarks"}

    Dim strSqlSearch As String = "SELECT code, Company, StAdd, City, " & _
                                    "ContactPerson, Phone, Mobile, Email, Remarks " & _
                                    "FROM tblclients " & _
                                    "WHERE " & colName(cboColumns.SelectedIndex) & " LIKE CONCAT('%', @valueName, '%')"

    Using myConn As New MySqlConnection("connectionStringHere")
        Using myComm As New MySqlCommand()
            With myComm
                .Connection = myConn
                .CommandType = CommandType.Text
                .CommandText = strSqlSearch
                .Parameters.AddWithValue("@valueName", txtSearchCriteria.Text);
            End With
            Try
                myConn.Open()
                Dim myReader As MySqlDataReader = myComm.ExecuteReader()

                While myReader.Read()
                    _isFound = True
                    item = lviClientList.Items.Add(myReader("code").ToString)
                    item.SubItems.Add(myReader("Company").ToString)
                    item.SubItems.Add(myReader("StAdd").ToString)
                    item.SubItems.Add(myReader("City").ToString)
                    item.SubItems.Add(myReader("ContactPerson").ToString)
                    item.SubItems.Add(myReader("Phone").ToString)
                    item.SubItems.Add(myReader("Mobile").ToString)
                    item.SubItems.Add(myReader("Email").ToString)
                    item.SubItems.Add(myReader("Remarks").ToString)
                End While

                If Not _isFound Then
                    MsgBox("No results found.", MsgBoxStyle.OkOnly, "Project Analysis System")
                End If

            Catch ex As MySqlException
                Msgbox(ex.Message.ToString(), MsgBoxStyle.OkOnly, "Project Analysis System")
            End Try
        End Using
    End Using

End Sub
+3

More articles:


All Articles