Filter ASP.Net GridView using multiple columns with Stored Procedure in C# and VB.Net

akhter

SOLVED CLOSED ACTIVE ANSWERED
User: akhter
Posted: on May 13, 2019 11:48 PM
Forum: Data Controls
Answer: 1
Views: 14940
Sample Code: Download

i want to search record in gridview date wise ,Employee wise and Section wise.

datewise and employee my query is working fine but when i apply search on Section then result is not coming as i want ,i created deferent procedure

here is my html code

<table class="auto-style1">
    <tr>
        <td class="auto-style2">
            &nbsp;
        </td>
        <td>
            <h1>
                &nbsp;Produce Bale Item Wise</h1>
        </td>
        <td>
            &nbsp;
        </td>
    </tr>
    <tr>
        <td class="auto-style2">
            &nbsp;
        </td>
        <td>
            <h4>
                DateSearch : From&nbsp;
                <asp:TextBox ID="txtitempbstart" runat="server" TextMode="DateTimeLocal"></asp:TextBox>
                &nbsp;To:
                <asp:TextBox ID="txtitempbend" runat="server" TextMode="DateTimeLocal"></asp:TextBox>
                <asp:Button ID="btnalbb" runat="server" Text="Search" OnClick="btnpbiw_Click" />
                <asp:DropDownList ID="DDworker" runat="server" CssClass="form-control js-example-placeholder-single"
                    ToolTip="Select ">
                </asp:DropDownList>
                <asp:DropDownList ID="DDLSec" runat="server">
                </asp:DropDownList>
            </h4>
        </td>
        <td>
            &nbsp;
        </td>
    </tr>
    <tr>
        <td class="auto-style2">
            &nbsp;
        </td>
        <td>
            <div style="height: 500px; width: 720px; overflow: auto;">
                <asp:GridView ID="GVallbb" runat="server" OnRowDataBound="GVallbb_RowDataBound">
                    <Columns>
                        <asp:TemplateField HeaderText="Bale No">
                            <ItemTemplate>
                                <asp:Label ID="BalNo" runat="server" Text='<%#Bind("BalNo")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="OrderNo">
                            <ItemTemplate>
                                <asp:Label ID="OrderNo" runat="server" Text='<%#Bind("OrderNo")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Item Name">
                            <ItemTemplate>
                                <asp:Label ID="itemName" runat="server" Text='<%#Bind("Descriptionitem")%>'></asp:Label>
                            </ItemTemplate>
                            <FooterTemplate>
                                <asp:Label ID="lblTotalText" Text="Total" runat="server"></asp:Label></FooterTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
            </div>
        </td>
        <td>
            &nbsp;
        </td>
    </tr>
</table>

here is my c# Code

SqlConnection con = new SqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        txtitempbstart.Text = DateTime.Now.ToString();
        txtitempbend.Text = DateTime.Now.ToString();
        {
            con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SilverProduction;MultipleActiveResultSets=True;");
            con.Open();

            {
                {
                    con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SilverProduction;MultipleActiveResultSets=True;");
                    con.Open();
                    SqlDataAdapter adpwn = new SqlDataAdapter("select * from Worker", con);
                    DataSet dswn = new DataSet();
                    adpwn.Fill(dswn);
                    DDworker.Items.Add(new ListItem("--Select worker--", ""));
                    DDworker.AppendDataBoundItems = true;
                    DDworker.DataSource = dswn.Tables[0];
                    DDworker.DataTextField = "Namworker";
                    DDworker.DataValueField = "IDwokrer";
                    DDworker.DataBind();
                    con.Close();
                }
                {
                    con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SilverProduction");
                    con.Open();
                    SqlDataAdapter adpbas = new SqlDataAdapter("select * from Sections", con);
                    DataSet dsbas = new DataSet();
                    adpbas.Fill(dsbas);
                    DDLSec.Items.Add(new ListItem("--Select Section--", ""));
                    DDLSec.AppendDataBoundItems = true;
                    DDLSec.DataSource = dsbas.Tables[0];
                    DDLSec.DataTextField = "Secnam";
                    DDLSec.DataValueField = "SecID";
                    DDLSec.DataBind();

                    con.Close();
                }

            }
        }
    }
}

protected void btnpbiw_Click(object sender, EventArgs e)
{
    con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SilverProduction;MultipleActiveResultSets=True;");
    con.Open();
    if (DDworker.SelectedValue == "")
    {
        SqlCommand cmd = new SqlCommand("Searchbaledatewise2");

        cmd.Parameters.Add(new SqlParameter("@StartDate", DateTime.Parse(txtitempbstart.Text).ToString("M/d/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InvariantCulture)));
        cmd.Parameters.Add(new SqlParameter("@EndDate", DateTime.Parse(txtitempbend.Text).ToString("M/d/yyyy h:mm:ss tt ", System.Globalization.CultureInfo.InvariantCulture)));
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GVallbb.DataSource = dt;
        GVallbb.DataBind();
        con.Close();
    }
    else if (DDworker.SelectedValue != "")
    {
        SqlCommand cmd = new SqlCommand("Searchbaledatewise");

        cmd.Parameters.Add(new SqlParameter("@StartDate", DateTime.Parse(txtitempbstart.Text).ToString("M/d/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InvariantCulture)));
        cmd.Parameters.Add(new SqlParameter("@EndDate", DateTime.Parse(txtitempbend.Text).ToString("M/d/yyyy h:mm:ss tt ", System.Globalization.CultureInfo.InvariantCulture)));
        cmd.Parameters.Add(new SqlParameter("@workerID", DDworker.SelectedValue));
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GVallbb.DataSource = dt;
        GVallbb.DataBind();
        con.Close();
    }

    if (DDLSec.SelectedValue != "")
    {
        SqlCommand cmd = new SqlCommand("Searchbaledatewise3");
        cmd.Parameters.Add(new SqlParameter("@StartDate", DateTime.Parse(txtitempbstart.Text).ToString("M/d/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InvariantCulture)));
        cmd.Parameters.Add(new SqlParameter("@EndDate", DateTime.Parse(txtitempbend.Text).ToString("M/d/yyyy h:mm:ss tt ", System.Globalization.CultureInfo.InvariantCulture)));
        cmd.Parameters.Add(new SqlParameter("@workerID", DDworker.SelectedValue));
        cmd.Parameters.Add(new SqlParameter("@SecID", DDLSec.SelectedValue)); 
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GVallbb.DataSource = dt;
        GVallbb.DataBind();
        con.Close();
    }
}
Decimal TotalValue = 0;
protected void GVallbb_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        Label lbl = (Label)e.Row.FindControl("itemQty");
        TotalValue += Convert.ToDecimal(lbl.Text);
    }
    else if (e.Row.RowType == DataControlRowType.Footer)
    {
        Label lbl = (Label)e.Row.FindControl("lblTotal");
        lbl.Text = TotalValue.ToString();
    }
}

here is my store procedure for Section

ALTER PROCEDURE [dbo].[Searchbaledatewise3]
    @StartDate Datetime,
@Enddate   Datetime,
@workerID int,
@SecID int
AS
BEGIN
    SET NOCOUNT ON;
        SELECT  Probale.Prdno AS BalNo, SalesOrder.OrderNo, ItemMasterFile.Descriptionitem,Probale.prdqty AS QTY,ItemMasterFile.BaleSize, salesorder.Order_Ref_No as Ref_ID,Worker.Namworker  as worker,Sections.Secnam As Section,Probale.EntryDate
FROM            Probale INNER JOIN
                SalesOrder ON SalesOrder.OrderNo = Probale.OrderNo INNER JOIN
                ItemMasterFile ON ItemMasterFile.CodeItem = Probale.Codeitem
                inner Join Worker on Worker.IDwokrer=Probale.IDwokrer
                inner Join Sections on Sections.SecID=ItemMasterFile.SecID
        where Convert(DateTime,Probale.EntryDate,103) between @StartDate and @Enddate or  (Worker.IDwokrer=@workerID or @workerID IS NULL) and (Sections.SecID=@SecID) 
END

or is there any way to hanlde all searching conding in one store procedure please guide thanks

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 2

Reply
ANSWER
User: pandeyism
Replied: on May 14, 2019 02:22 AM
Report

Hi akhter,

Refer below sample.

SQL

CREATE PROCEDURE SearchCountryANDCityByDateWise
    @OrderStartDate DATETIME,
    @OrderEndDate DATETIME,
    @Country VARCHAR(50) =  NULL,
    @ShipCity VARCHAR(50) = NULL
AS
BEGIN
    SELECT TOP 10 OrderId
           ,OrderDate
           ,ShipCountry
           ,ShipCity
    FROM Orders
    WHERE OrderDate BETWEEN @OrderStartDate AND @OrderEndDate AND
    (ShipCountry = @Country OR @Country IS NULL) AND 
    (ShipCity = @ShipCity OR @ShipCity IS NULL)
END

HTML

<asp:TextBox ID="txtFrom" runat="server"></asp:TextBox>
&nbsp;To:
<asp:TextBox ID="txtTo" runat="server"></asp:TextBox>
<asp:Button ID="btnalbb" runat="server" Text="Search" OnClick="Search" />
<asp:DropDownList ID="ddlShipCountry" runat="server" ToolTip="Select">
</asp:DropDownList>
<asp:DropDownList ID="ddlShipCity" runat="server">
</asp:DropDownList>
<asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField HeaderText="OrderId">
            <ItemTemplate>
                <asp:Label ID="lblOrderId" runat="server" Text='<%#Bind("OrderId")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="OrderDate">
            <ItemTemplate>
                <asp:Label ID="lblOrderDate" runat="server" Text='<%#Bind("OrderDate")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ShipCountry">
            <ItemTemplate>
                <asp:Label ID="lblShipCountry" runat="server" Text='<%#Bind("ShipCountry")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ShipCity">
            <ItemTemplate>
                <asp:Label ID="lblShipCity" runat="server" Text='<%#Bind("ShipCity")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Namespaces

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        gvOrders.DataSource = GetData("SELECT TOP 10 OrderId, CONVERT(VARCHAR,OrderDate,102) OrderDate, ShipCity, ShipCountry FROM Orders");
        gvOrders.DataBind();

        ddlShipCountry.DataSource = GetData("SELECT OrderId, ShipCountry FROM Orders");
        ddlShipCountry.DataTextField = "ShipCountry";
        ddlShipCountry.DataValueField = "OrderId";
        ddlShipCountry.DataBind();

        ddlShipCity.DataSource = GetData("SELECT OrderId, ShipCity FROM Orders");
        ddlShipCity.DataTextField = "ShipCity";
        ddlShipCity.DataValueField = "OrderId";
        ddlShipCity.DataBind();
    }
}

private DataTable GetData(string query)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }
    }
}

protected void Search(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SearchCountryANDCityByDateWise", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@OrderStartDate", txtFrom.Text);
            cmd.Parameters.AddWithValue("@OrderEndDate", txtTo.Text);
            cmd.Parameters.AddWithValue("@Country", ddlShipCountry.SelectedItem.Text);
            cmd.Parameters.AddWithValue("@ShipCity", ddlShipCity.SelectedItem.Text);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                gvOrders.DataSource = dt;
                gvOrders.DataBind();
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        gvOrders.DataSource = GetData("SELECT TOP 10 OrderId, CONVERT(VARCHAR,OrderDate,102) OrderDate, ShipCity, ShipCountry FROM Orders")
        gvOrders.DataBind()
        ddlShipCountry.DataSource = GetData("SELECT OrderId, ShipCountry FROM Orders")
        ddlShipCountry.DataTextField = "ShipCountry"
        ddlShipCountry.DataValueField = "OrderId"
        ddlShipCountry.DataBind()
        ddlShipCity.DataSource = GetData("SELECT OrderId, ShipCity FROM Orders")
        ddlShipCity.DataTextField = "ShipCity"
        ddlShipCity.DataValueField = "OrderId"
        ddlShipCity.DataBind()
    End If
End Sub

Private Function GetData(ByVal query As String) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SearchCountryANDCityByDateWise", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@OrderStartDate", txtFrom.Text)
            cmd.Parameters.AddWithValue("@OrderEndDate", txtTo.Text)
            cmd.Parameters.AddWithValue("@Country", ddlShipCountry.SelectedItem.Text)
            cmd.Parameters.AddWithValue("@ShipCity", ddlShipCity.SelectedItem.Text)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                gvOrders.DataSource = dt
                gvOrders.DataBind()
            End Using
        End Using
    End Using
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Filter ASP.Net GridView using multiple columns with Stored Procedure in C# and VB.Net

Company

Explore

Follow us

Contact Us