Filtering a SqlDataSource

[MzKitty]

Hi. I'm using vs2008 and I have a form where the user can enter data in a vehicle field or leave blank for all and I need to configure my sqldatasource for the gridview on that scenario. My current select is the code below and is based on the user having entered a "from" vehicle and a "to" Vehicle, but I would need to give the user the option of not entering anything into these fields.

SELECT TicketNo, UniqueID, TicketDate, LocationID, CustomerID, OrderID, VehicleID FROM JwsData1.Tkhist1 WHERE (LocationID = ?) AND (CustomerID = ?) AND (TicketDate >= ?) AND (TicketDate <= ?) AND (VehicleID >= ?) AND (VehicleID <= ?) ORDER BY TicketNo, UniqueID

These two fields are strings pertaining to a vehicle's license plate. Can this be done? Or do I need to assign a default value of "1" to the "From" vehicle and a default of "Z" to the "To" vehicle?

 

[Andrzejek]

I assume you use this sql to populate some kind of table which is the data source for your grid.

How about - if your "from" text box is txtVehFrom and your "to" is txtVehTo:

Dim sql As String = "SELECT TicketNo, UniqueID, TicketDate, " & _
    " LocationID, CustomerID, OrderID, VehicleID " & _
    " FROM JwsData1.Tkhist1 " & _
    " WHERE (LocationID = ?) AND (CustomerID = ?) AND (TicketDate >= ?) " & _
    " AND (TicketDate <= ?) "

If Strings.Len(txtVehFrom.Text) > 0 And Strings.Len(txtVehTo.Text) > 0 Then
    sql &= " AND (VehicleID >= '" & txtVehFrom.Text & "') " & _
      " AND (VehicleID <= '" & txtVehTo.Text & "') "
End If

sql &= "ORDER BY TicketNo, UniqueID"

Have fun.

---- Andy

 

[MzKitty]

My sql is not coded in my app; it is in my custom sql data source. I can go into 'Configure data source wizard' and work with my sql, but the parameters are represented as ?. I can't do 'if else' statements in there. I can give the parameters default values, but I can't tell it 'If ?CustomerID = "" then do all customers'. Is there a way to pass the sql statement to the data source after the user has entered his parameters, so the data going into the gridview is the correct filtered information?