In asp.net c# I am trying to return the total sum when the flag = 'D' as sumDebt and the total sum when the flag = 'C' as sumcredit my code is :
SqlParameter[] para = new SqlParameter[4];
para[0] = new SqlParameter("@stat_leger", ddlACCcode.SelectedValue);
para[1] = new SqlParameter("@branch", DDLBranch.SelectedValue);
para[2] = new SqlParameter("@from", db.getDate(txtFrom.Text));
para[3] = new SqlParameter("@to", db.getDate(txtTo.Text));
DataTable dtreport = db.SelectCmdText("Select sum(stat_amount) as sumDebt from PostedVoucher where stat_flag ='D' and stat_leger =@stat_leger and branch=@branch and stat_date between @from and @to UNION Select sum(stat_amount) as sumcredit from PostedVoucher where stat_flag ='C' and stat_leger =@stat_leger and branch=@branch and stat_date between @from and @to ", para);
GridView1.DataSource = dtreport;
GridView1.DataBind();
in html the grid view :
<asp:TemplateField HeaderText="sumDebt">
<ItemTemplate>
<asp:Label ID="sumDebt" runat="server" Text='<%#string.Format("{0:0.000}",float.Parse(Eval("sumDebt").ToString()))%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="sumcredit">
<ItemTemplate>
<asp:Label ID="sumcredit" runat="server" Text='<%#string.Format("{0:0.000}",float.Parse(Eval("sumcredit").ToString()))%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
the problem that in gridview it's return an error ( DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'sumcredit'.)
To merge Table try like this:-
SqlConnection conn = new SqlConnection("connectionString");
SqlCommand cmd = new SqlCommand("Select sum(stat_amount) as sumDebt from PostedVoucher where stat_flag ='D' and stat_leger =@stat_leger and branch=@branch and stat_date between @from and @to UNION Select sum(stat_amount) as sumcredit from PostedVoucher where stat_flag ='C' and stat_leger =@stat_leger and branch=@branch and stat_date between @from and @to", conn);
cmd.Parameters.AddWithValue("@stat_leger", ddlACCcode.SelectedValue);
cmd.Parameters.AddWithValue("@branch", DDLBranch.SelectedValue);
cmd.Parameters.AddWithValue("@from", db.getDate(txtFrom.Text));
cmd.Parameters.AddWithValue("@to", db.getDate(txtTo.Text));
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
DataTable tbl1 = dataSet.Tables[0];
DataTable tbl2 = dataSet.Tables[1];
tbl1.Columns.Add("sumcredit");
for (int i = 0; i < tbl2.Rows.Count; i++)
{
DataRow dr = tbl2.Rows[i];
DataRow dr1 = tbl1.Rows[i];
foreach (DataColumn v_Column in dr.Table.Columns)
{
if (dr1.Table.Columns.Contains("sumcredit"))
{
dr1["sumcredit"] = dr["sumcredit"];
}
}
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments