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:
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.