CODEWITHSUNDEEP
c#asp.netsql-server-2008gridviewfilter
snayr
snayr

Reputation: 11

how to filter a gridview

I have a gridview connected to a database by sqldatasource. I have multiple drop down lists and a table where users can select different data to filter the gridview.

I know how to set this up for one control but have no idea how to do it for multiple ones. Ideally what I would like to do is onfilterButton_Click the code behind connects to a stored procedure in the database. The parameters would depend on the filter options the user has chosen. I just don't know how to write the stored procedure. (I am not the best at SQL)

But I am open to suggestions of better ways to do this. I am using c# visual studios 2010 and sql server 2008.

I have been stuck on this for over a week now so really any practical help would be welcome.

Upvotes: 1

Views: 13651

Answers (4)

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

If you are not good in writing SP's then you can directly filter your SQL DataSource by using the FilterExpression property.

sql.FilterExpression = "Filteration Expression";
GridView1.DataBind();

Edit: Something that will work for you:

if (DropDownList1.SelectedItem.Text != "All")
    {
        SqlDataSource1.FilterExpression = "Title like '" + textbox1.Text + "' and Category like " + DropDownList1.SelectedValue;
    }
    else
    {
        SqlDataSource1.FilterExpression = "Title like '" + textbox1.Text + "'";
    }

 GridView1.DataBind();

Upvotes: 1

markpsmith
markpsmith

Reputation: 4918

Your stored procedure would declare all your parameters like so:

CREATE PROCEDURE GetData
 @parameter1 varchar(50),
 @parameter2 int    
etc...

Then you would call your stored procedure with the parameters like:

GetData(dropdownlist1.SelectedItem.Value, dropdownlist2.SelectedItem.Value);

Is that the sort of thing you mean?

UPDATE: For a situation where the 'All' option was chosen, you could still pass the value as a null parameter, and in the stored procedure you would declare it as 'optional':

CREATE PROCEDURE GetData
     @parameter1 varchar(50) = NULL,
     @parameter2 int

So the query in your stored procedure would look something like this:

SELECT *
FROM Table
WHERE  ((@parameter1 IS NULL) OR (column1 = @parameter1 )) 
AND column2 = @parameter2

Upvotes: 0

thevan
thevan

Reputation: 10364

Write One Stored Procedure and Pass DropDownList values as Parameters like this:

 CREATE PROC CUST_Details
 (
 @CustomerID INT,
 @CompanyID INT
 )
 AS
 BEGIN

    SELECT
               Customer.CustomerName,
               Company.CompanyName
    FROM
       Customer INNER JOIN
       Company ON Customer.CompanyID = Company.CompanyID
        WHERE
       (@CompanyID = -1 OR Customer.CompanyID = @CompanyID) AND
       (@CustomerID = -1 OR Customer.CustomerID = @CustomerID)

 END

Before that, in your DropDownList add item such as "<-----Select----->" as DataTextField and "-1" as DataValueField.

In Both DropDownList SelectedIndexChanged Event, pass DropDownList's SelectedValue as parameters and once again call the database and databind it with the Grid.

Upvotes: 1

Saurabh
Saurabh

Reputation: 5727

Below might give you idea how this works, please check:

<html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
        <form id="FORM1" runat="server">

            <p>Show all employees with the following title:
            <asp:DropDownList
                id="DropDownList1"
                runat="server"
                AutoPostBack="True">
                <asp:ListItem>Sales Representative</asp:ListItem>
                <asp:ListItem>Sales Manager</asp:ListItem>
                <asp:ListItem>Vice President, Sales</asp:ListItem>
            </asp:DropDownList></p>

            <asp:SqlDataSource
                id="SqlDataSource1"
                runat="server"
                ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
                SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
                FilterExpression="Title='{0}'" OnFiltering="SqlDataSource1_Filtering">
                <FilterParameters>
                    <asp:ControlParameter Name="Title" ControlId="DropDownList1" PropertyName="SelectedValue"/>
                </FilterParameters>
            </asp:SqlDataSource><br />

            <asp:GridView
                id="GridView1"
                runat="server"
                DataSourceID="SqlDataSource1"
                AutoGenerateColumns="False">
                <columns>
                    <asp:BoundField Visible="False" DataField="EmployeeID" />
                    <asp:BoundField HeaderText="First Name" DataField="FirstName" />
                    <asp:BoundField HeaderText="Last Name" DataField="LastName" />
                </columns>
            </asp:GridView>
                <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

        </form>
    </body>
</html>

Server Side:

protected void SqlDataSource1_Filtering(object sender, SqlDataSourceFilteringEventArgs e)
    {
        Label1.Text = e.ParameterValues[0].ToString();
    }

Upvotes: 0

Related Questions