We help IT Professionals succeed at work.

Filter a gridview with a SQLDataSource using a checkbox

ohait
ohait asked
on
Medium Priority
1,646 Views
Last Modified: 2012-06-27
I have a gridview with a SQL Datasource. I am trying to filter my gridview with a checkbox. I want to use this checkbox to filter out my records. i.e. when it is not ticked I would like my gridview to show the results from the following query.

SELECT * FROM Products WHERE supplierID = 1

and when it is ticked I would like my gridview to show.

SELECT * FROM Products WHERE (supplierID = 1) AND (UnitPrice < 10 OR UnitPrice IS NULL)

I would prefer to do this with a paramaterised query but I am not sure how or whether it is even possible.

Thanks in advance for your help.

Comment
Watch Question

How many DataGrids are you using in the page?

-Nauman.
To learn more about parameterized query please visit the following URL:

Using Parameterized Queries in ASP.Net
http://aspnet101.com/aspnet101/tutorials.aspx?id=1

-Nauman.

Author

Commented:
Thanks.

There isn't a datagrid on the page just a gridview as I am using ASP.NET 2.0.  One other note.. The SQLDataSource has been added straight on to the design page not the code behind page.

Author

Commented:
I found this code somewhere which when adapted seemed to solve my problem. sds is my SQlDataSource

    Protected Sub chkExcludeGrouped_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkExcludeGrouped.CheckedChanged
        If chkExcludeGrouped.Checked = True Then
            ViewState("sql") = "SELECT * FROM Products WHERE supplierID = 1"
        Else
            ViewState("sql") = "SELECT * FROM Products WHERE (supplierID = 1) AND (UnitPrice < 10 OR UnitPrice IS NULL)"
        End If
        sds.SelectCommand = ViewState("sql").ToString()
    End Sub


    Protected Overrides Sub OnLoad(ByVal e As EventArgs)

        MyBase.OnLoad(e)
        If Not ViewState("sql") Is Nothing Then
            sds.SelectCommand = ViewState("sql").ToString()
        End If

    End Sub
Commented:
PAQed with points refunded (125)

GranMod
Community Support Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.