CODEWITHSUNDEEP
databasevb.net
siyhaz
siyhaz

Reputation: 15

Search function using combo box,text box and list view

enter image description herei have a few error. i have a search function from the database. when i run the project, and click the SEARCH button to view in the listview, the popup message out called "Incorrect syntax near "CONCAT". here the code for CONCAT

Dim strSqlSearch As String = "SELECT Room_Code, Room_Type, Room_No, Room_Price, Room_Status, No_of_Occupancy" & _
                                    "FROM Room" & _
                                    "WHERE" & colName(cboSearch.SelectedIndex) & "LIKE CONCAT ('%', @valueName, '%')"

here full code for SEARCH function

Private Sub Search()

    ListViewRoom.Items.Clear()

    Dim item As New ListViewItem
    Dim _isFound As Boolean = False

    Dim colName() As String = {"Room_Code", "Room_Type", "Room_No", "Room_Price", "Room_Status", "No_of_Occupancy"}

    Dim strSqlSearch As String = "SELECT Room_Code, Room_Type, Room_No, Room_Price, Room_Status, No_of_Occupancy" & _
                                    "FROM Room" & _
                                    "WHERE" & colName(cboSearch.SelectedIndex) & "LIKE CONCAT ('%', @valueName, '%')"

    dbSource = "Data Source=LAILATUL-PC\SERVER;Initial Catalog=HotelManagementSystem;Integrated Security=True"

    Using con As New SqlClient.SqlConnection("Data Source=LAILATUL-PC\SERVER;Initial Catalog=HotelManagementSystem;Integrated Security=True")

        Using com As New SqlClient.SqlCommand()
            With com
                .Connection = con
                .CommandType = CommandType.Text
                .CommandText = strSqlSearch
                .Parameters.AddWithValue("@valueName", txtSearch.Text)

            End With

            Try
                con.Open()
                Dim dr As SqlClient.SqlDataReader = com.ExecuteReader

                While dr.Read
                    _isFound = True

                    item = ListViewRoom.Items.Add(dr("Room_Code").ToString)
                    item.SubItems.Add(dr("Room_Type".ToString))
                    item.SubItems.Add(dr("Room_No".ToString))
                    item.SubItems.Add(dr("Room_Price".ToString))
                    item.SubItems.Add(dr("Room_Status".ToString))
                    item.SubItems.Add(dr("No_of_Occupancy".ToString))
                End While

                If Not _isFound Then
                    MsgBox("No results found.", MsgBoxStyle.OkOnly, "Information")
                End If
            Catch ex As Exception
                MsgBox(ex.Message.ToString(), MsgBoxStyle.OkOnly, "Error")
            End Try

        End Using
    End Using





End Sub

I hope u can help me. Tq

Upvotes: 0

Views: 1527

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

You don't need to use the CONCAT. just remove it from the SQL and it should work.

EDIT

Try this:

Dim strSqlSearch As String = "SELECT Room_Code, Room_Type, Room_No, Room_Price, Room_Status, No_of_Occupancy" & _
                             "FROM Room" & _
                             "WHERE" & colName(cboSearch.SelectedIndex) & "LIKE '%'+ @valueName +'%'"

Edit #2

Dim strSqlSearch As String = "SELECT Room_Code, Room_Type, Room_No, Room_Price, Room_Status, No_of_Occupancy" & _
                             "FROM Room" & _
                             "WHERE" & colName(cboSearch.SelectedIndex) & "LIKE '%" & txtSearch.Text & "%'"

And remove the parameter form the SqlCommand.

Upvotes: 1

Related Questions