Vb.net Advance text box search

Question

I'm implementing a system and in that system, I want to search product while text changing on textbox in the datagridview. I've already done that part.

But I found out that search command only helping me to find the product 1st word of the cell. But I want to filter it with any of the selected cells.

Example: In my datagridview there is a column Product Name. In that column, there Is a data as Galaxy A20s. Currently, I have to type on my search textbox starting as Galaxy.... then only search result appearing. I want to modify the output like if I'm type A20s then also result should be filtered in the datagridview.

Any possibilities to do that in vb.net.

Here is the code which I'm using currently.

Try
            con = New OleDbConnection(cs)
            con.Open()
            cmd = New OleDbCommand("SELECT (ProductID) as [Product ID],(ProductName) as [Product Name],(Brand) as [Brand],(UnitPrice) as [Unit Price]  from ProductDB where ProductName like '" & txtSearch.Text & "%' order by ProductName", con)
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataSet As DataSet = New DataSet()
            myDA.Fill(myDataSet, "ProductDB")
            dgFillProduct_Stock.DataSource = myDataSet.Tables("ProductDB").DefaultView

            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

Thanks

score 0 · Accepted Answer · answered May 27 '20 at 20:30

Your SQL WHERE clause has the % wildcard only at the end of the value:

ProductName like '" & txtSearch.Text & "%'

So the beginning of the ProductName needs to match, but the end can be anything. If you want that functionality for both ends of the string value, you can simply put the wildcard on both ends:

ProductName like '%" & txtSearch.Text & "%'

IMPORTANT: Your code is currently open to SQL injection, which is not only a glaring security hole but also a very common source of bugs. You should be using query parameters instead of directly placing user input in your query, since a malicious user could input code into your query.

This question and its accepted answer are a great example of how to do this.

Vb.net Advance text box search

1 Answers1