Auto search through textbox and dynamically populate datagrid

[rajeevanand]

hello everyone
can anyone help on this.
i have a table with column's (itemcode, itemname, price) on the vb form i have one search field (textbox1) and datagrid
i have data as
itemcode itemname price
1234 apple 10
1235 orange 12
1345 grapes 15
1356 banana 15
1357 apricot 25
2345 strawberry 20
2346 guava 10
now what i am trying is if i type 1 then grid show all data's starting with 1 and as i type 12 then it should filter only remaining data with itemcode starting with 12... and so on.\
i hope i am able to get my point through..
simple search i am able to i.e. when whole string match's with itemcode it shows the details in gridview.
code i m using on change event of textbox is

 Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
'i have opened connection above         
        cn.Open()
        Dim a As New OleDbDataAdapter("Select * from Item where ItemName = ' " & TextBox1.Text & "'", cn)
        Dim b As New DataSet
        a.Fill(b, "Item")
        DataGridView2.DataSource = b.Tables(0)
        cn.Close()

    End Sub

thanx in advance

[jmcilhinney]

If you want to make partial matches then you have to use the LIKE operator rather than =, and you must couple that with one or more wildcards. It appears that you want to perform a "starts with" search, so that means using a wildcard at the end of the input value that will match zero or more characters. In this and EVERY other case, you should also be using parameters, which will, among other things, avoid the error that you have made by putting a spurious space in your value.

Dim adapter As New OleDbDataAdapter("SELECT * FROM Item WHERE ItemName LIKE @ItemName", connection)
 
adapter.Parameters.AddWithValue("@ItemName", TextBox1.Text & "%")

By the way, if all you're using is a single DataTable then using a DataSet is pointless. Just create a DataTable.

[rajeevanand]

sorry, i have actually just started with vb.net and dont know use of parameter's.
anyway i m getting following error if i use your code.

could you please explain code without parameter's

Thanx

[jmcilhinney]

Soryy, that should be:

adapter.SelectCommand.Parameters

[rajeevanand]

Sorry it doesn't work, gives following error



and just a gentle reminder that values in textbox1 are numeric, if that's causing any problem..

thanx

[jmcilhinney]

Um, you've left off the AddWithValue. I meant that it should have been 'adapter.SelectCommand.Parameters' rather than just 'adapter.Parameters'. Parameters is a property of each command and each command is a property of the data adapter. AddWithValue is a method of the parameter collection that allows you to add a parameter with a value to the parameter collection of the adapter's SELECT command.

[rajeevanand]

Thanx dear

Finally i got it, i happen to do one tutorial with parameters. that's how got hold of it..

Cheers