SQL for oledbDataAdapter search function

I am currently tryi开发者_JAVA技巧ng to create a search function on a page in VB.NET which will allow me to search a database for a customerID from a textbox called txtSearch and then present it into a datagrid.

So far I can display the customerID's manually by using

SELECT * FROM Customers WHERE CustomerID LIKE 'A%' 

but cant figure out how to replace the A with data which will be inputted into the textbox.

You can use parameters to pass in to the query, using syntax like this:

SELECT * FROM Customers 
WHERE CustomerID LIKE @SearchTerm + '%'

In VB.NET:

Dim strQry as String = 
        "SELECT * FROM Customers WHERE CustomerID  LIKE @SearchParam + '%'"

Dim cmd as SqlCommand = new SqlCommand(strQry, connection)
cmd.CommandType= CommandType.Text

Dim prm As SqlParameter = new SqlParameter("@SearchParam",SqlDbType.VarChar,50)
prm.Direction=ParameterDirection.Input
prm.Value = txtUser.Text
cmd.Parameters.Add(prm)

Try to use the following code (just initialize your connection):

Dim dataTable As New DataTable()
Using connection As New OleDbConnection()
    'connection.ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", MapPath("~/App_Data/nwind.mdb"))
    Dim adapter As New OleDbDataAdapter(String.Empty, connection)
    adapter.SelectCommand.CommandText = String.Format("SELECT * FROM [Customers] WHERE [CustomerID] LIKE @SearchTerm + '%'")

    Dim parameter As OleDbParameter = adapter.SelectCommand.Parameters.Add("@SearchTerm", OleDbType.VarChar)
    parameter.Value = txtSearch.Text.Trim()

    adapter.Fill(dataTable)
End Using

This approach will allow concatenating the required SELECT statement and avoid the SQL Injections.