Populate winform chart with multiple filters

ravi kumar 331 Reputation points
2021-02-15T11:00:31.177+00:00

Dear all ,

In my winform application i want to populate my charts where the data source is a stored procedure having multiple parameters , the values for the parameter will be provided from the winform controls.
But when i click the filter button on the date criteria is getting evaluated and all the others are not , kindly verify my below images and code and kindly guide me where i am wrong:

Image of my winform:68152-image.png
code for chart data : 

ArrayList deptname = new ArrayList();  
        ArrayList deptcount = new ArrayList();  
  
        ArrayList deptname2 = new ArrayList();  
        ArrayList deptsum2 = new ArrayList();  
  
        ArrayList rejcat = new ArrayList();  
        ArrayList rejsum = new ArrayList();  
  
        private void btnfilter_Click(object sender, EventArgs e)  
        {  
            deptname.Clear();  
            deptcount.Clear();  
            deptpercentage();  
            deptname2.Clear();  
            deptsum2.Clear();  
            deptrejqty();  
            rejcat.Clear();  
            rejsum.Clear();  
            toprejreason();  
        }  
  
        private void toprejreason()  
        {  
            SqlConnection con = new SqlConnection(cs);  
            SqlCommand cmd = new SqlCommand("toprejreason", con);  
            cmd.Parameters.AddWithValue("@fromDate", dtfrom.Value);  
            cmd.Parameters.AddWithValue("@toDate", dtto.Value);  
            cmd.Parameters.AddWithValue("@depname", comboBox1.Text);  
            cmd.Parameters.AddWithValue("@mattype", textBox1.Text);  
            cmd.Parameters.AddWithValue("@matnum", textBox2.Text);  
            cmd.CommandType = CommandType.StoredProcedure;  
            con.Open();  
            SqlDataReader dr = cmd.ExecuteReader();  
            while (dr.Read())  
            {  
                rejcat.Add(dr.GetString(0));  
                rejsum.Add(dr.GetInt32(1));  
            }  
            chart1.Series[0].Points.DataBindXY(rejcat, rejsum);  
            dr.Close();  
            con.Close();  
        }  
  
        private void deptrejqty()  
        {  
            SqlConnection con = new SqlConnection(cs);  
            SqlCommand cmd = new SqlCommand("deptrejqty", con);  
            cmd.Parameters.AddWithValue("@fromDate", dtfrom.Value);  
            cmd.Parameters.AddWithValue("@toDate", dtto.Value);  
            cmd.Parameters.AddWithValue("@depname", comboBox1.Text);  
            cmd.Parameters.AddWithValue("@mattype", textBox1.Text);  
            cmd.Parameters.AddWithValue("@matnum", textBox2.Text);  
            cmd.CommandType = CommandType.StoredProcedure;  
            con.Open();  
            SqlDataReader dr = cmd.ExecuteReader();  
            while (dr.Read())  
            {  
                deptname2.Add(dr.GetString(0));  
                deptsum2.Add(dr.GetDecimal(1));  
            }  
            deptrej.Series[0].Points.DataBindXY(deptname2, deptsum2);  
            dr.Close();  
            con.Close();  
        }  
  
  
        private void deptpercentage()  
        {  
            SqlConnection con = new SqlConnection(cs);  
            SqlCommand cmd = new SqlCommand("deptpercentage", con);  
            cmd.Parameters.AddWithValue("@fromDate", dtfrom.Value);  
            cmd.Parameters.AddWithValue("@toDate", dtto.Value);  
            cmd.Parameters.AddWithValue("@depname", comboBox1.Text);  
            cmd.Parameters.AddWithValue("@mattype", textBox1.Text);  
            cmd.Parameters.AddWithValue("@matnum", textBox2.Text);  
            cmd.CommandType = CommandType.StoredProcedure;  
            con.Open();  
            SqlDataReader dr = cmd.ExecuteReader();  
            while (dr.Read())  
            {  
                deptname.Add(dr.GetString(0));  
                deptcount.Add(dr.GetDecimal(1));  
            }  
            chartdeptper.Series[0].Points.DataBindXY(deptname, deptcount);  
            dr.Close();  
            con.Close();  
        }  
  
        private void FormDashboard_FormClosed(object sender, FormClosedEventArgs e)  
        {  
            Application.Exit();  
        }  
    }  
}

code of stored procedure: 

create proc toprejreason  
@fromDate Date,  
@toDate Date,  
@depname varchar,  
@mattype varchar,  
@matnum varchar  
as  
select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
from Semicon_NPD  
Where Date between  @fromDate  and  @toDate or Department=@depname or Material_Type=@mattype or Material_Number=@matnum  
group by Reason_Brief  
order by Sum(Rejection_Qty) desc  
go

when i add the parameter in my combo box or text box and then click the filter button no change is happening. Please help me.

Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,836 questions
Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,630 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,292 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-02-16T22:02:44.187+00:00

    Writing that kind of code is tedious and error-prone. Thankfully there is an easier option: 

    WHERE (Date > @fromdate OR @fromdate IS NULL)
   AND  (Date < @todate OR @todate IS NULL)
   AND  (Material_type = @matname OR @matname IS NULL)
 ...
OPTION (RECOMPILE)

    This is a single SELECT - no need for all those IF.

    The hint OPTION (RECOMPILE) requests a recompile every time. This permits SQL Server to use any index there is on the columns. For this type of query there is no single one-size-fits-all plan.

    I have an article on my web site, Dynamic Search Conditions where I discuss these kind of searches in more detail.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ken Tucker 5,846 Reputation points
    2021-02-15T11:28:06.91+00:00

    I would look at this in line the stored procedure. It will pull all records in the data range or has a record with the right deptname, mattype, or matnum 

       Where Date between  @fromDate  and  @toDate or Department=@depname or Material_Type=@mattype or Material_Number=@matnum

    I am thinking it should be this. 

      Where Date between  @fromDate  and  @toDate and (Department=@depname or Material_Type=@mattype or Material_Number=@matnum)
  2. Erland Sommarskog 101.4K Reputation points MVP
    2021-02-15T22:35:42.907+00:00

    This is the bug:

    @depname varchar,
    @mattype varchar,
    @matnum varchar

    Only varchar is the same as varchar(1). Thus, your input gets truncated and this is why you don't get any hits.

  3. ravi kumar 331 Reputation points
    2021-02-16T04:35:04.287+00:00

    hi @Erland Sommarskog and @Ken Tucker thank you so much for your inputs , with your suggestions i have coded like below , now the it is working like this: 

    USE [PINQC]  
 GO  
 /****** Object:  StoredProcedure [dbo].[toprejreason]    Script Date: 16-02-2021 09:42:56 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 ALTER proc [dbo].[toprejreason]  
 @fromDate Date,  
 @toDate Date,  
 @depname varchar(50),  
 @mattype varchar(50),  
 @matnum varchar(50)  
 as  
 if (@depname is null or @depname='') and (@mattype is null or @mattype='') and (@matnum is null or @matnum='')  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where Date between  @fromDate  and  @toDate  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
 else   
 begin  
 if (@depname is null or @depname='')  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where (Date between  @fromDate  and  @toDate) and (Material_Type=@mattype) and (Material_Number=@matnum)  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
 else   
 begin  
 if (@mattype is null or @mattype='')  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where (Date between  @fromDate  and  @toDate) and (Department=@depname) and (Material_Number=@matnum)  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
 else   
 begin  
 if (@matnum is null or @matnum='')  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where (Date between  @fromDate  and  @toDate) and (Department=@depname) and (Material_Type=@mattype)  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
 else   
 begin  
 if (@mattype is null or @mattype='') and (@matnum is null or @matnum='')  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where (Date between  @fromDate  and  @toDate) and (Department=@depname)  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
 else   
 begin  
 if (@depname is null or @depname='') and (@matnum is null or @matnum='')  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where (Date between  @fromDate  and  @toDate) and (Material_Type=@mattype)  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
 else   
 begin  
 if  (@depname is null or @depname='') and (@mattype is null or @mattype='')  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where (Date between  @fromDate  and  @toDate) and (Material_Number=@matnum)  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
 else  
     select top 10 Reason_Brief as Reason, Sum(Rejection_Qty) as Quantity  
     from Semicon_NPD  
     Where (Date between  @fromDate  and  @toDate) and (Material_Type=@mattype) and (Material_Number=@matnum) and (Department=@depname)  
     group by Reason_Brief  
     order by Sum(Rejection_Qty) desc  
     end  
     end  
     end  
     end  
     end  
     end  
     go
    1. it is now populating the chart when @fromDate , @toDate , @depname & @mattype parameters filled and @matnum kept null.
    2. Not populating the chart when @fromDate , @toDate & @mattype parameters filled and @depname kept null.
    3. **i think when @depname is kept null **no values is getting returned kindly look at my code please guide me pls.
    0 comments