How to use http request query string property to filter where clause

Question

I need to filter where clause in my GetProduct function using http request query string property. I have set up my filters in urls. (eg burgers.aspx?filter=burgers'). Burgers is the name of database table category(Where ProductCat = filter). I understand I need to pass parameter to interaction class because it does not handle requests. Please help.

Interaction class: Public Class Interaction Inherits System.Web.UI.Page ' New instance of the Sql command object Private cmdSelect As New SqlCommand ' Instance of the Connection class Private conIn As New Connection

Region "Menu functions and subs"

' Set up the SQL statement for finding a Product by ProductCat  
Private Sub GetProduct(ByVal CatIn As String)
    ' SQL String

    Dim strSelect As String
    strSelect = "SELECT *  "
    strSelect &= " FROM Menu "
    strSelect &= " WHERE ProductCat = " 
    strSelect &= "ORDER BY 'ProductCat'"
    ' Set up the connection to the datebase
    cmdSelect.Connection = conIn.Connect
    ' Add the SQL string to the connection
    cmdSelect.CommandText = strSelect
    ' Add the parameters to the connection
    cmdSelect.Parameters.Add("filter", SqlDbType.NVarChar).Value = CatIn

End Sub



'Function to create list of rows and columns
Public Function ReadProduct(ByVal CatIn As String) As List(Of Dictionary(Of String, Object))
    'Declare variable to hold list
    Dim ReturnProducts As New List(Of Dictionary(Of String, Object))
    Try
        Call GetProduct(CatIn)
        Dim dbr As SqlDataReader
        ' Execute the created SQL command from GetProduct and set to the SqlDataReader object
        dbr = cmdSelect.ExecuteReader
        'Get number of columns in current row
        Dim FieldCount = dbr.FieldCount()
        Dim ColumnList As New List(Of String)
        'Loop through all columns and add to list
        For i As Integer = 0 To FieldCount - 1
            ColumnList.Add(dbr.GetName(i))
        Next
        While dbr.Read()
            'Declare variable to hold list
            Dim ReturnProduct As New Dictionary(Of String, Object)
            'Loop through all rows and add to list
            For i As Integer = 0 To FieldCount - 1
                ReturnProduct.Add(ColumnList(i), dbr.GetValue(i).ToString())
            Next
            'Add to final list
            ReturnProducts.Add(ReturnProduct)
        End While
        cmdSelect.Parameters.Clear()
        'Close connection
        dbr.Close()
    Catch ex As SqlException
        Dim strOut As String
        strOut = ex.Message
        Console.WriteLine(strOut)
    End Try
    ' Return the Product object
    Return ReturnProducts
End Function

Code Behind:

Partial Class Burger Inherits System.Web.UI.Page

'String Used to build the necessary markup and product information
Dim str As String = ""
''Var used to interact with SQL database
Dim db As New Interaction

' New instance of the Sql command object
Private cmdSelect As New SqlCommand
' Instance of the Connection class
Private conIn As New Connection

Protected Sub printMenuBlock(ByVal productName As String) 'Set up variable storing the product and pull from databse Dim product = db.ReadProduct(productName)

    'Add necessary markup to str variable, with products information within

    For i As Integer = 0 To product.Count - 1

        str += "<div class='menuItem'>"
        'str += "    <img alt='Item Picture' class='itemPicture' src='" + product(i).ImagePath.Substring(3).Replace("\", "/") + "' />"
        str += "    <div class='itemInfo'>"
        str += "        <h1 class='itemName'>"
        str += "            " + product(i).Item("ProductName") + "</h1>"
        'str += "        <h3 class='itemDescription'>"
        str += "            " + product(i).Item("ProductDescription")
        str += "        <h1 class ='itemPrice'>"
        str += "            " + product(i).Item("ProductPrice") + "</h1>"
        str += "        "
        str += "        </div>"

        str += "    </div>"

    Next

End Sub

''Uses
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    'Dim v = Request.QueryString("filter")

    'Response.Write("filter is")
    'Response.Write(v)

    Dim value = Request.QueryString("filter")

    'Get string from printMenuBlock method 
    printMenuBlock(str)

    'Print the str variable in menuPlace div
    menuPlace.InnerHtml = str

End Sub

End Class

I need a direction on how to pass the Request.QueryString("filter") to GetProduct function to filter by page according to ProductCategory. Thanks in advance.

Answer 1

Try something like this:

Dim filter = Request.QueryString("filter")
Dim sqlStr = "Select * From menu Where ProductCat = @filter Order By ProductCat"
cmdSelect.Parameters.Add("filter", SqlDbType.NVarChar).Value = filter