gridview不按年度过滤器显示结果 [英] gridview not showing result according to yearwise filter
问题描述
在gridview中我试图显示一年中项目的总支出（按照2011-2012，2012-2013等），我有以下列工资，TA，意外事件，NRC，机构费用和其他。我只想要一行我可以显示汇总数据，我用来实现这个目的的方法是：
protected void GridView1_DataBound（ object sender，EventArgs e）
{
foreach （GridViewRow row in GridView1.Rows）
{
row.Visible = row.RowIndex.Equals（ 1 ）;
}
}
i希望结果采用这种格式[URL = http：//www.image-share。 com / ijpg-2143-192.html] [IMG] http://www.image-share.com/upload/2143/192m.jpg [/ IMG] [/ URL]
所以我正在做的是在我插入标签的所有列中，然后我试图在它们上显示结果。基本上出错的是我的查询根据年份没有产生所需的输出。
这里是我正在使用的代码：
< pre lang =c＃> / * 填充gridview * /
protected void Button1_Click（ object sender，EventArgs e）
{
string str = string .Format（ @ 选择m.pcode，m.fyyear，m.salary，m.ta，m.contigency，m.nrc，m.institcharges，m.others ，y.yearlyalloc，y.salary as sal1，y.talary as ta1，y.contigency as cont1，y.nrc as nrc1，y.institcharges as inst1，y.others as other1 FROM FROM monthly AS m inner join y y on m .pcode = y.pcode其中m.pcode =（' + DropDownList1.SelectedItem.ToString（）+ ）和y.fyyear一样（' + DropDownList2.SelectedItem.ToString（）+ '），con）;
SqlDataAdapter da = new SqlDataAdapter（str，con）;
DataTable dtNew = new DataTable（）;
da.Fill（dtNew）;
GridView1.DataSource = dtNew;
GridView1.DataBind（）;
}
protected void GridView1_RowDataBound（ object sender，GridViewRowEventArgs e）
{
SqlConnection con = new SqlConnection（ConfigurationManager.ConnectionStrings [ connstr]。ConnectionString）;
if （e.Row.RowType == DataControlRowType.DataRow）
{
t1 + = Convert.ToDouble（DataBinder.Eval） （e.Row.DataItem， salary））;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
Label lbsal =（Label ）e.Row.FindControl（ lbsal）;
lbsal.Text = t1.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t2 + = Convert.ToDouble（DataBinder.Eval（e.Row.DataItem， ta））;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
Label lbta =（Label ）e.Row.FindControl（ lbta）;
lbta.Text = t2.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t3 + = Convert.ToDouble（DataBinder.Eval（e.Row.DataItem， contigency））;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
Label lbcont =（Label ）e.Row.FindControl（ lbcont）;
lbcont.Text = t3.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t4 + =转换。 ToDouble（DataBinder.Eval（e.Row.DataItem， nrc））;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
Label lbnrc =（Label ）e.Row.FindControl（ lbnrc）;
lbnrc.Text = t4.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t5 + = Convert.ToDouble（DataBinder.Eval（e.Row.DataItem， institcharges））;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
Label lbinst =（Label ）e.Row.FindControl（ lbinst）;
lbinst.Text = t5.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t6 + = Convert.ToDouble（DataBinder.Eval（e.Row.DataItem， 其他））;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
Label lbothers =（Label ）e.Row.FindControl（ lbothers）;
lbothers.Text = t6.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t7 = t1 + t2 + t3 + t4 + t5 + t6;
}
if （e.Row.RowType == DataControlRowType.Footer）
{
Label mtotal =（Label ）e.Row.FindControl（ mtotal）;
mtotal.Text = t7.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t8 = Convert.ToDouble（（（DataRowView）e.Row.DataItem）[ annualalloc]。ToString （））;
}
if （e.Row.RowType == DataControlRowType.Footer）
{
Label tyear =（标签）e.Row.FindControl（ tyear）;
tyear.Text = t8.ToString（）;
}
if （e.Row.RowType == DataControlRowType.DataRow）
{
t8 = Convert.ToDouble（（（DataRowView）e.Row.DataItem）[ annualalloc]。ToString （））;
}
if （e.Row.RowType == DataControlRowType.Footer）
{
Label tyear =（标签）e.Row.FindControl（ tyear）;
tyear.Text = t8.ToString（）;
}
}
如果您的DropDownList2包含2012-2013之类的项目，请尝试以下查询
string str = string.Format（@select m.pcode，m.fyyear，m.salary，m.ta，m.contigency，m.nrc，
m.institcharges，m.others，y.yearlyalloc，y.salary as sal1，y.ta as ta1，y.contigency as cont1，
y.nrc as nrc1，y.institcharges as inst1，y.others as other1 FROM每月AS m内连接y
on m.pcode = y.pcode其中m.pcode =（''+ DropDownList1.SelectedItem.ToString（）+''）和
y .fyyear like（+ String.Join（，，DropDownList2.SelectedItem.Value.Split（'' - ''））+），con）;
以下查询做了诀窍
string str = string.Format（@ 选择m.pcode，sum（m.salary） as SalaryConsumed，y.salary，sum（m.ta）as taConsumed，y.ta，sum（m.contigency）as contiused，y.contigency，sum（m.nrc）as nrcconsumed，y.nrc，sum（m。 institcharges）as institgiven，y.institcharges，sum（m.others）as miscused，y.others，m.fyyear，y.yearlyalloc from month m
inner join y y on m.pcode = y.pcode and m .fyyear = y.fyyear其中m.pcode =（' + DropDownList1.SelectedItem.ToString（）+ '）和m.fyyear =（' + DropDownList2.SelectedItem.ToString（）+ '）分组由m.fyyear，m.pcode，y.salary，y.ta，y.contigency，y.nrc，y.institcharges，y.others，y.yearlyalloc，con）;
in a gridview i am trying to show total of a project''s expenditure during the year this has be in accordance to year (like 2011-2012, 2012-2013 etc.), i have following columns salary,T.A, contingency, NRC, institutional charges and others.I only want a single row in which i can show the summarized data, the the approach i am using to achieve this is:
protected void GridView1_DataBound(object sender, EventArgs e)
{
foreach (GridViewRow row in GridView1.Rows)
{
row.Visible = row.RowIndex.Equals(1);
}
}
i want the result to be in this format [URL=http://www.image-share.com/ijpg-2143-192.html][IMG]http://www.image-share.com/upload/2143/192m.jpg[/IMG][/URL]
so what i am doing is in all the columns i am inserting labels and then i am trying to display the result on them. what basically is getting wrong is my query is not producing desired output according to years.
here is the code i am using:
/*to populate gridview*/
protected void Button1_Click(object sender, EventArgs e)
{
string str = string.Format(@"select m.pcode, m.fyyear, m.salary, m.ta, m.contigency, m.nrc, m.institcharges, m.others,y.yearlyalloc,y.salary as sal1,y.ta as ta1,y.contigency as cont1,y.nrc as nrc1,y.institcharges as inst1,y.others as other1 FROM monthly AS m inner join yearly y on m.pcode=y.pcode where m.pcode=('" + DropDownList1.SelectedItem.ToString() + "' ) AND y.fyyear like('" + DropDownList2.SelectedItem.ToString() + "')", con);
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dtNew = new DataTable();
da.Fill(dtNew);
GridView1.DataSource = dtNew;
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
if (e.Row.RowType == DataControlRowType.DataRow)
{
t1 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "salary"));
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbsal = (Label)e.Row.FindControl("lbsal");
lbsal.Text = t1.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t2 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ta"));
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbta = (Label)e.Row.FindControl("lbta");
lbta.Text = t2.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t3 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "contigency"));
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbcont = (Label)e.Row.FindControl("lbcont");
lbcont.Text = t3.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t4 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "nrc"));
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbnrc = (Label)e.Row.FindControl("lbnrc");
lbnrc.Text = t4.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t5 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "institcharges"));
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbinst = (Label)e.Row.FindControl("lbinst");
lbinst.Text = t5.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t6 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "others"));
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbothers = (Label)e.Row.FindControl("lbothers");
lbothers.Text = t6.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t7 = t1 + t2 + t3 + t4 + t5 + t6;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label mtotal = (Label)e.Row.FindControl("mtotal");
mtotal.Text = t7.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t8 = Convert.ToDouble(((DataRowView)e.Row.DataItem)["yearlyalloc"].ToString());
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label tyear = (Label)e.Row.FindControl("tyear");
tyear.Text = t8.ToString();
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
t8 = Convert.ToDouble(((DataRowView)e.Row.DataItem)["yearlyalloc"].ToString());
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label tyear = (Label)e.Row.FindControl("tyear");
tyear.Text = t8.ToString();
}
}if ur DropDownList2 contains items like "2012-2013" then try following query
string str = string.Format(@"select m.pcode, m.fyyear, m.salary, m.ta, m.contigency, m.nrc, m.institcharges, m.others,y.yearlyalloc,y.salary as sal1,y.ta as ta1,y.contigency as cont1, y.nrc as nrc1,y.institcharges as inst1,y.others as other1 FROM monthly AS m inner join yearly y on m.pcode=y.pcode where m.pcode=(''" + DropDownList1.SelectedItem.ToString() + "'' ) AND y.fyyear like(" + String.Join(",",DropDownList2.SelectedItem.Value.Split(''-'')) + ")", con);
the following query did the trick
string str = string.Format(@"select m.pcode,sum( m.salary) as SalaryConsumed, y.salary,sum(m.ta) as taConsumed,y.ta,sum(m.contigency) as contiused, y.contigency,sum(m.nrc) as nrcconsumed,y.nrc,sum(m.institcharges) as institgiven,y.institcharges,sum(m.others) as miscused,y.others, m.fyyear,y.yearlyalloc from monthly m inner join yearly y on m.pcode = y.pcode and m.fyyear = y.fyyear where m.pcode=('" + DropDownList1.SelectedItem.ToString() + "' ) AND m.fyyear=('" + DropDownList2.SelectedItem.ToString() + "')group by m.fyyear, m.pcode, y.salary, y.ta,y.contigency,y.nrc,y.institcharges,y.others,y.yearlyalloc", con);
这篇关于gridview不按年度过滤器显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
