Filtering a Query

[bytemedia]

I am working on a VB.net application for our billing database. It's basically just an interface that management can use to pull information from the database without having to search through the actual tables.

The database is in MS Access. Here's what I'm having trouble with...

I have a query set up like SELECT Task FROM Hours WHERE Customer = 298 AND hours.date = #4/3/2008#

So this returns all work entries for Customer ID #32 on the given date.

What they want me to add now is a textbox on the form where they can enter a keyword to filter the work entry returns. So for example, if they enter "cake" into the text field, only the work entries for the given customer on the given date that contain the word "cake" in the task column will be returned.

I'm not a VB.net expert by any means and I just have no idea where to start. I tried to google it but gave up after wading through way too many unhelpful articles.

 

[ScottyB]

Hi

To implement this what you require is a what is called a parameterized query. Here is a link that might help you start. If you have any problems understanding how this works, let us know and we can help you.

http://www.vb-helper.com/howto_net_db_parameterized_query.html

Regards

ScottyB

 

[jmcilhinney]

Dim connection As New OleDbConnection("connection string here")
Dim adapter As New OleDbDataAdapter("SELECT Task FROM Hours WHERE Customer = @Customer AND [Date] = @Date AND Task LIKE '%' + @Task + '%'", connection)

With adapter.SelectCommand.Parameters
    .AddWithValue("@Customer", customerID)
    .AddWithValue("@Date", hoursDate)
    .AddWithValue("@Task", taskSubstring)
End With

 

[cjard]

You know.. I always found it neater to put the % inside the parameter value in oracle.. does that work as a wildcard in SQLS?

SELECT Task FROM Hours WHERE Customer = @Customer AND [Date] = @Date AND Task LIKE @Task

cmd.Parameters("Task").Value = "%cake%"

 

[eddie]

I would like to do more on the same subject. I have more text boxes that can be used in a search. So how to use parameters when i don't know how many will be included in a query? I made this piece of code that works (but not shown in the grid, that's my other topic) but is there any other solution?

utakmice_SQL = "SELECT * FROM PAROVI "
Stop
If frmStats.txtTip.Text <> "" Then
sWhere = " Tip = '" & frmStats.txtTip.Text & "'"
End If
If frmStats.txtKoef.Text <> "" Then
If sWhere <> "" Then
sWhere = sWhere & " AND "
End If
sWhere = sWhere & " Koeficijent = '" & frmStats.txtKoef.Text & "'"
End If
If sWhere <> "" Then
utakmice_SQL = utakmice_SQL & " WHERE " & sWhere
End If

 

[cjard]

you ahve 2 options. either write a query where all parameters are present:

SELECT * FROM parovi WHERE
(col1 = @param1 OR @param1 IS NULL) AND
(col2 = @param2 OR @param2 IS NULL)


Now to wildcard any parameter set it to null

-

OR if youre capable of making an sql where you use string concatentaiton to put the values in, you can sure as anything use string concat to put the PARAMETER NAMES in and then add the parameters:

sqlcommand.CommandText = "SELECT * FROM parovi WHERE 1=1 "

if search_on_col1 then
  sqlcommand.CommandText += " AND col1 = @param1 "
  sqlcommand.Parameters.Add("@col1", textBox1.Text)
end if

get it?!