how to make a simple SQL query from VB.net (access db)

Question

I have a working dataset and datagrid already in my project, but I want to make my own quicksearch button. The following code gives error for connectionstring PROVIDER KEYWORD NOT SUPPORTED

connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\prod.mdb"

sql = "Select (*) from table1 where prodid=" + searchfield.Text

cnn = New SqlConnection(connetionString)
Try
    cnn.Open()
    cmd = New SqlCommand(sql, cnn)
    Dim ret = (cmd.ExecuteScalar())
    cmd.Dispose()
    cnn.Close()
    Text = ret

Answer 1

Are you trying to just search for a particular value in field or asking for a full blown query designer?

If it is the first one, should be relatively easy, you can either select the rows directly in the datagrid or fire a paramaterized query to get the result.

If it is the second, things are slightly more complicated. You might have to use a third party component.

As requested :-)

You are using wrong Connection. You should be using OleDbConnection class. I hate to nitpick, but you are generating SQL Statements on the fly by String concatenation which leaves you open to SQL Injection attacks

Answer 2

its better to use

dataset.table.select("a=b")