[RESOLVED] Search string syntax

[Kochanski]

I have a TextBox for the user to input a search string. My button click code returns thr error 'No value given for one or more Parameters. I have tried various ways to write a SELECT string. Help appreciated.

Private Sub Button36_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button36.Click
        'Search
        Try
            'Clear the Datatable called dt
            dt.Clear()
            'SELECT conditions
            Dim SearchStr As String = "'%" & Me.TextBox18.Text & "%'"

            da = New OleDb.OleDbDataAdapter("SELECT * FROM [sheet1$] WHERE Surname LIKE SearchStr AND Member LIKE('%Ply.Member%') ORDER BY Surname", con)


            da.Fill(dt)
            'Bind the DataGrid to the Table
            DataGridView1.DataSource = dt
            
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

[keystone_paul]

You appear to be attempting to intermingle variables defined locally such as "SearchStr" with a SQL query - that won't work.

You need to pass things like SearchStr into the SQL query, either using Parameters or amending the literal text you are passing in.

[Kochanski]

Thanks. I'm sure you are right, but I can't progress this because I don't know how to amend the SELECT syntax. How do I get from a user input to a SELECT statement?

[techgnome]

But you do.... you did it here:
Dim SearchStr As String = "'%" & Me.TextBox18.Text & "%'"

...

"SELECT * FROM [sheet1$] WHERE Surname LIKE " & SearchStr & " AND Member LIKE('%Ply.Member%') ORDER BY Surname"

-tg

[keystone_paul]

Well it looks like you have three user defined parameters in there : [sheet1$], '%Ply.Member%' and Searchstring - is that right or are the first two of them actually objects in the database?

If so then you set the query to just have placeholders initially, so it would become something like

"SELECT * FROM ? WHERE Surname LIKE ? AND Member LIKE(?) ORDER BY Surname"

and add Parameters to represent the placeholders.

Alternatively as techgnome illustrated, if you have variables containing the searchstring you can just append the variables into the SQL statement. You kinda did that in your example except you had the variable name inside the string so it wouldn't evaluate to the contents, to get that working you do what tg showed.

Unfortunately I don't know where you are getting the other values that you want to use in the query if they are variables - can you shed more light on what [sheet1$] and Ply.Member are?

NB - using parameters is the preferred way of doing this - see here for details.

[Kochanski]

Thanks tg - just those missing " & in the statement. The code works fine. I'm guessing I will have to search all fields by declaring each one in turn followed by a LIKE statement. Is there any way to search all fields without repeating the Parameters?

Dim SearchStr As String = "'%" & Me.TextBox18.Text & "%'"

            da = New OleDb.OleDbDataAdapter("SELECT * FROM [sheet1$] WHERE Surname LIKE " & SearchStr & " OR Forname LIKE " & SearchStr & " ORDER BY Surname", con)

[Kochanski]

Only if I learn to spell parameters, I guess.