Filter Gridview using a stored procedure

Question

I’d like to filter a grid view. I use a stored procedure to bind the data.

This is my code:

 <asp:TextBox ID="txtSearch" runat="server"  ></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="search"/>
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateEditButton="True" EditRowStyle-Width="50px"  OnRowEditing="GridView1_RowEditing" OnRowCancelingEdit="TaskGridView_RowCancelingEdit" AutoGenerateColumns="False" OnRowUpdating="GridView1_RowUpdating" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="10" >
<Columns>
    <asp:TemplateField HeaderStyle-Width="30px" ItemStyle-Width="30px" >
    <ItemTemplate>
        <%# Container.DataItemIndex + 1 %>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="Uusername" ReadOnly="true"  HeaderStyle-Height="35px" ItemStyle-Height="30px" HeaderStyle-Width="130px" ItemStyle-Width="130px"  />
    <asp:BoundField DataField="Umail" ReadOnly="true" HeaderStyle-Height="35px" ItemStyle-Height="30px"    HeaderStyle-Width="160px" ItemStyle-Width="160px"   />
    <asp:BoundField DataField="Pname"  ReadOnly="true" HeaderStyle-Height="35px" ItemStyle-Height="30px"    HeaderStyle-Width="100px" ItemStyle-Width="100px" />
    <asp:BoundField DataField="Pfamily"  ReadOnly="true" HeaderStyle-Height="35px" ItemStyle-Height="30px"    HeaderStyle-Width="150px" ItemStyle-Width="150px"  />
    <asp:BoundField DataField="UisActiveMob" ReadOnly="true"  HeaderStyle-Height="35px" ItemStyle-Height="30px"   HeaderStyle-Width="100px" ItemStyle-Width="100px" />

</Columns>

</asp:GridView>

I fill gridview like this:

 con.Open();
            string query = "getDataProfile";
            SqlCommand com = new SqlCommand(query, con);
            com.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter adapter = new SqlDataAdapter(com);
            DataSet dset = new DataSet();
            adapter.Fill(dset, "t1");
            var result = com.ExecuteReader();
            GridView1.EmptyDataText = "No Records Found";
            GridView1.DataSource = dset.Tables["t1"];
            GridView1.DataBind();

            con.Close();

I know if I use a data source, I have to add a filter like this:

<FilterParameters>
        <asp:ControlParameter ControlID="CountryListBox"   PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="LastNameTextBox" PropertyName="Text" />
      </FilterParameters>
    </asp:SqlDataSource>

But I didn't use data source! How can I search in my gridview?

Answer 1

You should change your stored procedure code in which it can get parameter. Assume your stored procedure is :

//Add @Country and @LastName Parameters to stored procedure.
CREATE PROCEDURE getDataProfile (@Country INT, @LastName NVarchar(50))
As
//Your stored procedure code goes here

Notice: You does not need any SqlDataSource. Change Source section:

 <asp:Button ID="btnSearch" runat="server" Text="search"/>

To:

 <asp:Button ID="btnSearch" OnClick="btnSearch_Click" runat="server" Text="search"/>

Also be aware change connection string to your connection string. Your codes must be like this:

        protected void btnSearch_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();

        con.ConnectionString = "Your Connection String";
        con.Open();
        string query = "getDataProfile";
        SqlCommand com = new SqlCommand(query, con);
        com.CommandType = CommandType.StoredProcedure;

        SqlParameter param = com.CreateParameter();
        param.ParameterName = "@Country";
        param.Value = CountryListBox.SelectedValue;
        param.DbType = DbType.Int32;
        com.Parameters.Add(param);

        param = com.CreateParameter();
        param.ParameterName = "@LastName";
        param.Value = LastNameTextBox.Text;
        param.DbType = DbType.String;
        com.Parameters.Add(param);



        SqlDataAdapter adapter = new SqlDataAdapter(com);
        DataSet dset = new DataSet();
        adapter.Fill(dset, "t1");
        var result = com.ExecuteReader();
        GridView1.EmptyDataText = "No Records Found";
        GridView1.DataSource = dset.Tables["t1"];
        GridView1.DataBind();

        con.Close();
    }