How to filter a grid view according to the drop down list values

Q: I have set of dropdownlists which used to filter the data in my grid view. So, on filter button click开发者_Go百科 I want it to access the database stored procedure and bring back the required data.

Here is my code so far. And Stored Procedure, and html for the gridview just in case its wrong too. my .cs:

 public partial class Animals : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        public string FormatURL(object strArgument)
        {

            return ("readrealimage.aspx?id=" + strArgument);

        }


        protected void btnFilter_Click(object sender, EventArgs e)
        {
            SqlConnection MyConnection = new
            SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

            SqlCommand command = new SqlCommand("sp_SearchAnimals", MyConnection);

            if (AnimalTypeDDL.Text != "Dont Mind")
            {
                AnimalTypeDDL.Text = command.Parameters["@Type_of_Animal"].Value.ToString();
            }


            if (CrossBreedDDL.Text != "Dont Mind")
            {
                CrossBreedDDL.Text = command.Parameters["@CrossBreed"].Value.ToString();
            }

            if (SexDDL.Text != "Dont Mind")
            {
                SexDDL.Text = command.Parameters["@Sex"].Value.ToString();
            }

            if (SizeDDL.Text != "Dont Mind")
            {
                SizeDDL.Text = command.Parameters["@Size"].Value.ToString();
            }

            if (AgeDDL.Text != "Dont Mind")
            {
                AgeDDL.Text = command.Parameters["@Age"].Value.ToString();
            }

            if (LocationDDL.Text != "Dont Mind")
            {
                LocationDDL.Text = command.Parameters["@Location"].Value.ToString();
            }

            if (RescueDDL.Text != "Dont Mind")
            {
                RescueDDL.Text = command.Parameters["@Name"].Value.ToString();
            }

            if (ChildrenDDL.Text != "Select one")
            {
                ChildrenDDL.Text = command.Parameters["@Children"].Value.ToString();
            }
            if (OtherCatsDDL.Text != "Select one")
            {
                OtherCatsDDL.Text = command.Parameters["@OtherCats"].Value.ToString();
            }
            if (OtherDogsDDL.Text != "Select one")
            {
                OtherDogsDDL.Text = command.Parameters["@OtherDogs"].Value.ToString();
            }

            GridView1.DataSource = ds;
            GridView1.DataBind();

            if (GridView1.Rows.Count >= 1)
            {
                Panel1.Visible = true;
                GridView1.Visible = true;
                lblMsg.Visible = false;

            }
            else if (GridView1.Rows.Count < 1)
            {
                GridView1.Visible = false;
                Panel1.Visible = false;
                lblMsg.Text = "Your search criteria returned no results.";
                lblMsg.Visible = true;
            }

            command.CommandType = CommandType.Text;
            command.Connection.Open();
            SqlDataReader MyDataReader = command.ExecuteReader();
            MyConnection.Close();

        }


        protected void buttonClear_Click(object sender, EventArgs e)
        {
            AnimalTypeDDL.Text = "Don't Mind";
            CrossBreedDDL.Text = "Don't Mind";
            SexDDL.Text = "Don't Mind";
            SizeDDL.Text = "Don't Mind";
            AgeDDL.Text = "Don't Mind";
            LocationDDL.Text = "Don't Mind";
            RescueDDL.Text = "Don't Mind";

        }
    }

my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER PROC [dbo].[sp_SearchAnimals]
 (
 @Type_of_Animal nvarchar(50),
 @CrossBreed nvarchar(50),
 @Sex nvarchar(50),
 @Size nvarchar(50),
 @Age nvarchar(50),
 @Location nvarchar(50),
 @Name nvarchar(50),
 @Children nvarchar(50),
 @OtherCats nvarchar(50),
 @OtherDogs nvarchar(50),
 @Details nvarchar(300) 
 )
 AS
BEGIn

SELECT
           AD.Location,AD.Details, R.Name,
           I.Content
FROM
   AnimalDetails As AD INNER JOIN
   Images As I ON AD.ImageId = I.ImageId,
   AnimalDetails As DA INNER JOIN RescueDetails As R ON DA.RescueId = R.RescueId 
    WHERE
   (@Type_of_Animal is NUll OR AD.Type_of_Animal = @Type_of_Animal) AND
   (@CrossBreed is null OR AD.CrossBreed = @CrossBreed) AND
   (@Sex is null or AD.Sex = @Sex) AND
   (@Size is null or AD.Size = @Size) AND
   (@Age is null or AD.Age = @Age) AND
   (@Location is null or AD.Location = @Location) AND
   (@Name is null or R.Name = @Name) AND
   (@Children is null or AD.Children = @Children) AND
   (@OtherCats is null or AD.OtherCats = @OtherCats) AND
   (@OtherDogs is null or AD.OtherDogs = @OtherDogs) AND
   (@Details is null or AD.Details = @Details) 

    END

My .aspx:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
        OnRowDataBound="GridView1_RowDataBound" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
        AllowPaging="True" AllowSorting="True">
        <RowStyle CssClass="gridViewRowStyle" Wrap="True" />
        <HeaderStyle CssClass="gridViewHeaderStyle" Wrap="False" />
        <SelectedRowStyle Wrap="true" CssClass="gridViewSelectedRowStyle" />
        <AlternatingRowStyle CssClass="gridViewAltRowStyle" />
        <Columns>
            <asp:BoundField DataField="Type_of_Animal" HeaderText="Type_of_Animal" />
            <asp:BoundField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
            <asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" />
            <asp:BoundField DataField="Location" HeaderText="Location" />
        </Columns>
        <FooterStyle CssClass="gridViewHeaderStyle" Wrap="False" />
        <PagerSettings Position="Bottom" />
        <PagerStyle CssClass="gridViewHeaderStyle" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
        SelectCommand="SELECT [Type_of_Animal], [Sex], [Age], [Location]         From[AnimalDetails] ">
    </asp:SqlDataSource>
    <div style="font-family: Arial; font-size: small;">
        &nbsp;&nbsp;You are viewing page
        <%=GridView1.PageIndex + 1%>of
        <%=GridView1.PageCount%>
    </div>
    </asp:Panel>
    <asp:Label ID="lblMsg" runat="server" Text="Your search criteria did not return any Logs"
        Visible="False" CssClass="label"></asp:Label>
    </td> </tr> </table>

After seening your code i'm unable to find the line as to where you have assigned a DataSet to the SqlCommand command ResultSet.

I mean DataSet ds = command.ExecuteDataSet();

After that, check to see that your stored procedure is returning any results by passing it the same parameters from the Sql Server Management Studio.

Also, you are using a DataReader but binding with ds which is undefined.