filtering from a database to a gridview asp.net sql server

See more:

ASP.NET

Gridview

I am having problems with my project. I have a few dropdownlists which a user can filter the data that is in the gridview. So on filter button click 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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Configuration;

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);
    }
   public void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
   {

   }
   protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
   {
   }
   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";

   }
   }

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

<asp:GridView ID="GridView1"  runat="server" AutoGenerateColumns="False"
                       DataSourceID="SqlDataSource1"
                       OnRowDataBound="GridView1_RowDataBound"
                        önSelectedIndexChanged="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:SqlDataSource ID="SqlDataSource1" runat="server"
                       ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"

                       SelectCommand="SELECT [Type_of_Animal], [Sex], [Age], [Location]         From[AnimalDetails] ">

                   <div style="font-family: Arial; font-size: small;">
                         You are viewing page <%=GridView1.PageIndex + 1%>of <%=GridView1.PageCount%>
                   </div>

               <asp:Label ID="lblMsg" runat="server" Text="Your search criteria did not return any Logs"
               Visible="False" CssClass="label">