SELECT * from table like from text box. (vb.net 2008 + ms access 2003)

Question

I've been trying make a SQL LIKE statement for a search button to display data to, but it doesn't work . I've tried searching about it and even copy-pasted some of the working codes but still no.. I'm really new to this so please excuse my fail coding.

(vb.net 2008 + ms access 2003) here's my code :

item = txtsearch.Text
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim dr As OleDbDataReader
        con.Open()

            cmd = New OleDbCommand("SELECT * FROM moviedb WHERE Title Like = '%" & item & "%' ", con)
            dr = cmd.ExecuteReader()

            If (dr.Read() = True) Then



                da = New OleDbDataAdapter("SELECT * FROM moviedb WHERE Title  = '%" & item & "%' ", con)

                da.Fill(ds, "List")

                DataGridView1.DataSource = ds.Tables("List")

     con.Close()

I've also tried changing the "%" to "*", but yeah still doesn't do it. It says Syntax error (missing operator) in query expression 'Title LIKE = '%@item%''.

score 2 · Answer 1 · edited Nov 12 '13 at 10:03

2

I was looking for the same, but here is the correct way:

cmd = New OleDbCommand("SELECT * FROM moviedb WHERE Title Like  '%" & item & "%' ", con)

You have to remove the = sign

edited Nov 12 '13 at 10:03

Keren Caelen

1,466
3
17
38

answered Nov 12 '13 at 09:44

user2982612

21
2

Steve · Accepted Answer · 2013-09-23T12:34:39.313

There are some things wrong in your code:

First, where do you set the connection string?

 Dim con = new OleDbConnection(.....connection string here ....)

The connection string is essential to open the database, without it you cannot call the Open method on the connection. This site contains a lot of connection strings examples, included the one for Access 2003 ConnectionStrings

Second, you need to use a parameterized query to avoid problems if your text to search for contains a single quotes (and of course to avoid Sql Injections)

Third, you don't need to query two times the database just to discover if there are records returned by the query. You could directly bind the return to your grid or test the number of rows returned in the table List of the dataset

Dim ds As New DataSet

Using con = new OleDbConnection(......constring....)
Using cmd = new OleDbCommand("SELECT * FROM moviedb WHERE Title Like ?", con)
    con.Open()
    cmd.Parameters.AddWithValue("@p1", "%" & item & "%")
    Using da = New OleDbDataAdapter(cmd)
         da.Fill(ds, "List")
         DataGridView1.DataSource = ds.Tables("List")

    End Using
End Using
End Using

Thanks for the response! I'm sorry about the connection string, it was there but haven't included cause it was at the top. That was just a part of the whole btn code. — user2806886, Sep 23 '13 at 11:57
Check also the answer from G_Wilson. I have not seen that. LIKE is the unique operator needed here — Steve, Sep 23 '13 at 12:35

score 0 · Answer 3 · answered Sep 23 '13 at 12:25

0

Also, another thing. You don't need an = Operator when your using LIKE i have found.

answered Sep 23 '13 at 12:25

GeoffWilson

433
7
21

score -1 · Answer 4 · answered Sep 23 '13 at 11:21

-1

Can you try this :

"SELECT * FROM moviedb WHERE Title Like = '%" & item & "%'", con

source

answered Sep 23 '13 at 11:21

user2567674

171
1
3
15

SELECT * from table like from text box. (vb.net 2008 + ms access 2003)

4 Answers4