SUN SUN - 4 months ago 24
Vb.net Question

Using ViewState to apply filters on Listview

I have used ListView Control to gets list of products from database. I also stores result in viewstate . Now to apply filter from checkbox to get refined data I want to know how can I use viewState values?

e.g. If 10 Products found in Music category when page loads. Now if user apply filter(Bluetooth) then only that products should be shown which are in Music & has bluetooth..

Now It is working Like on page load Music category gets fetched Then if I check Bluetooth filter then all bluetooth products comes which are not related to music.

Private Sub shop_Load(sender As Object, e As EventArgs) Handles Me.Load
Dim pageName As String = Me.Page.RouteData.Values("category").ToString()
if not Me.isPostback Then
Try
query = select * from products where category = '"+pageName+"'
Dim conString As String = ConfigurationManager.ConnectionStrings("conio").ConnectionString
Dim con As New MySqlConnection(conString)
Dim cmd As New MySqlCommand(query)
con.Open()
Dim da As New MySqlDataAdapter()
cmd.Connection = con
da.SelectCommand = cmd
Dim dt As New DataTable()
da.Fill(dt)
ViewState("Data") = dt
products.DataSource = dt
products.DataBind()
catHeading.Text = pageName
itemCount.Text = dt.Rows.Count.ToString
con.Close()
Catch ex As Exception
Response.Write(ex)
End Try
End If
End Sub


Filter Apply code

Private Sub priceFilter_SelectedIndexChanged(sender As Object, e As EventArgs) Handles priceFilter.SelectedIndexChanged
'buildWhereClause()

Dim price As String = priceFilter.SelectedValue.ToString()
Dim dt As DataTable = DirectCast(ViewState("Data"), DataTable)
Dim dr As DataRow() = dt.[Select]((Convert.ToString("category='") & price) + "'")
products.DataSource = dt
products.DataBind()
itemCount.Text = dt.Rows.Count.ToString
End Sub


I just want when user apply any filter then it should check from viewstate(Data) rather to entire table.

Answer

Save your category in viewstate & on Checked get that category in string & join that string in your query. something like this

    Dim constr As String = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString
    Dim query As String = "select * from table"
    Dim joiner As String = ""

    Dim condition As String = String.Empty
    Dim whereClause As String = String.Empty
    Dim priceCondition As String = String.Empty
    Try
        Dim category As String = ViewState("Data")
        condition = String.Concat(condition, joiner, String.Format("{0}", category))
        If joiner = "" Then joiner = ""


        joiner = " where "
        If Not String.IsNullOrEmpty(condition) Then
            whereClause = String.Concat(whereClause, joiner, String.Format("category Like '%{0}%'", condition))
            joiner = " and "
        End If


        'Same way you can apply multiple filters as you want & then get that in one string like below

        Dim masterClause As String = String.Empty
        masterClause = (query & whereClause)

        Using con As New MySqlConnection(constr)
            Using cmd As New MySqlCommand(masterClause)
                Using sda As New MySqlDataAdapter(cmd)
                    cmd.Connection = con
                    Using dt As New DataTable()
                        sda.Fill(dt)
                        products.DataSource = dt
                        products.DataBind()
                        itemCount.Text = dt.Rows.Count.ToString
                    End Using
                End Using
            End Using
        End Using