I am in the process of converting some of my asp/vbscript code to asp.net. I have been stuck on one particular piece for a little while now.

I have an app that displays a dataset with 2 select boxes -"ItemNumber" and "Species", which allow the user to "filter" the results to their liking. I needed to provide the functionality that would allow the user to select 1 or both of the boxes if so desired.
Here is an example of one of the vbscript code snippets driving the "Species" select box in the asp page:

sql = "SELECT * From vw_cat2 "


If Not IsEmpty(Request("Species")) Then
Dim strSpecies
strSpecies = Trim(Request("Species"))
If strSpecies <> "" Then
If blnWhere Then sql = sql & "AND " Else sql = sql & "WHERE " : blnWhere = True
If (Left(strSpecies, 1) = "*" And Len(strSpecies) > 1) Then
sql = sql & "(Species LIKE '%" & Replace(Mid(strSpecies, 2), "'", "''") & "') "
ElseIf (Right(strSpecies, 1) = "*" And Len(strSpecies) > 1) Then
sql = sql & "(Species LIKE '" & Replace(Mid(strSpecies, 1, Len(strSpecies)-1), "'", "''") & "%') "
Else
sql = sql & "(Species = '" & Replace(strSpecies, "'", "''") & "') "
End If
End If
End If

The best I have been able to come up with in asp.net is:

MyCommand = New SqlDataAdapter("select ItemNumber, Species, stdprice, Location from vw_cat2 where Species='" & Species.SelectedItem.Value & "' and ItemNumber='" & ItemNumber.SelectedItem.Value & "'", myConnection)

However this does not provide me the 'and/or' flexibility I need. If a user selects a "Species" but not an "ItemNumber" I want to test for 'where' in that situation.

Andy advice would be appreciated.

Thanks!

Hey,

Use a data view, use the following select:

select ItemNumber, Species, stdprice, Location from vw_cat2

Then use the RowFilter property to specify the species or itemnumbre filter:

Dim vw as dataview = ds.tables(0).defaultview
vw.RowFilter = "Species = '" & <variable> & "' and ItemNumber = '" & <variable & "'"

Only include the row filter properties that you want. Then you can bind whatever to the dataview.

Brian

The code that you use to determine what to include in your SQL query shouldn't be a whole lot different from what you had in ASP/VBScript. The logic is entirely the same, the difference is that you are working with server controls instead of having to interrogate the request object directly. Otherwise, it should be close to identical.