[2008] - LIKE Query

[The Little Guy]

How would you do a like query? This is currently what I have, but it doesn't work, I get an error.

Error Message:
Public member 'Read' on type 'Integer' not found.

Dim connection As New OleDbConnection(dbQueryString)
Dim command As New OleDbCommand("select * from info where `@sType` like @search order by `Title`", connection)
 
command.Parameters.AddWithValue("@sType", searchFilter.Text)
command.Parameters.AddWithValue("@search", searchBox.Text & "%")
connection.Open()
Dim dr = command.ExecuteNonQuery()
While dr.Read()
       ' Some code
End While

[jmcilhinney]

There's nothing wrong with your query. The problem is here:

Dim dr = command.ExecuteNonQuery()

You're calling ExecuteNonQuery, which returns an Integer, rather than ExecuteReader, which returns an OleDbDataReader. This is EXACTLY why you should NOT use type inference when declaring variables. I STRONGLY suggest that, if you use type inference at all, that you ONLY ever use it when you're assigning a literal value to the variable. Better you simply don't use it at all.

[The Little Guy]

1. what is type inference?

2. Any reason after changing to ExecuteReader, it doesn't go into the while loop? If i do "a" (without quotes), it doesn't find anything in my database, even though the word "Animals" (without quotes) is in there.

[jmcilhinney]

1. Open your MSDN Library, type in the term and see.

2. Your query is never going to work. I didn't notice to begin with because I wasn't looking for it but you're trying to specify a column name using a parameter. That is not possible. Identifiers like table and column names must be specified explicitly. Always use parameters to insert values into an SQL statement, as you are, but you have to use string concatenation to insert a variable table or column name.

What you should do is populate a ComboBox with a list of valid column names. If you know what they are at design time then you can hard code them. If you don't then you can use the GetSchema method of an OleDbConnection object to get the information. The user will then select a known value from a list, rather than being able to type any old thing into a TextBox. As such there's no need for you to validate the value they enter. You can then concatenate the column name into the SQL string:

Dim command As New OleDbCommand("select * from info where `" & searchCombo.SelectedValue & "` like @search order by `Title`", connection)