如何在使用文本框搜索datagridview时在标签中显示总和
问题描述:
我有DataGridView有很多数据在里面和一个搜索文本框。 我的问题是当我筛选datagridview行我想我的小计标签根据搜索数据进行更改,但我无法做到这一点,因为我选择与查询小计SUM。如何在使用文本框搜索datagridview时在标签中显示总和
这是我的小计标签代码
SqlConnection con = new SqlConnection(str);
con.Open();
string query = "SELECT SUM(Sub_Total) from (SELECT distinct Sub_Total FROM Sales_Order) as Sales_Order";
using (SqlCommand command = new SqlCommand(query, con))
{
object result = command.ExecuteScalar();
Sub_Tot.Text = Convert.ToString(result); ;
}
con.Close();
文本框框TextChanged代码:
SqlConnection con = new SqlConnection(str);
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select Invoice_no,Date_of_Sale,Customer_Name,Contact,Item_Code,Item_Name,Quantity,Selling_Price,Discount,Paid_Amount,Remaining,Sub_Total,Total From Sales_Order Order By Invoice_no", con);
DataTable dt = new DataTable();
sda.Fill(dt);
dgv.DataSource = dt;
DataView dv = new DataView(dt);
dv.RowFilter = string.Format("Item_Name LIKE '{0}%' ", Search_By_ItemName.Text);
dgv.DataSource = dv;
每一件事工作正常,但根据过滤 我想改变我的小计标签文本,它不改变小计文本根据搜索过滤器怎么可能?
注:它的工作完全使用计时器,但我不希望使用定时器
int sum = 0;
for (int i = 0; i < Sale_Order_Grid.Rows.Count; i++)
{
sum += Convert.ToInt32(Sale_Order_Grid.Rows[i].Cells[8].Value);
}
this.lbl_SubTotal.Text = sum.ToString();
答
啊哈,如果我理解你正确,您需要根据不循环您的网格搜索查询,以填补subtotal
标签把那一列一个一个地加起来?是?
var totlaCol= dt.Columns.Cast<DataColumn>().SingleOrDefault(col => col.ColumnName == "SubTotal");
if (totalCol!= null)
{
var tableRows = dt.AsEnumerable().First();
var sumVal= tableRows.Field<string>(totlaCol);
// or
//sumVal = tableRows.Field<string>(dt.Columns.IndexOf(myColumn));
}
1:
SqlConnection con = new SqlConnection(str);
con.Open();
SqlDataAdapter sda = new SqlDataAdapter("Select Invoice_no,Date_of_Sale,Customer_Name,Contact,Item_Code,Item_Name,Quantity,Selling_Price,Discount,Paid_Amount,Remaining,Sub_Total,Total,SUM(Sub_Total) as SubTotal From Sales_Order Group by Invoice_no,Date_of_Sale,Customer_Name,Contact,Item_Code,Item_Name,Quantity,Selling_Price,Discount,Paid_Amount,Remaining,Sub_Total,Total Order By Invoice_no", con);
DataTable dt = new DataTable();
sda.Fill(dt);
dgv.DataSource = dt;
DataView dv = new DataView(dt);
dv.RowFilter = string.Format("Item_Name LIKE '{0}%' ", Search_By_ItemName.Text);
dgv.DataSource = dv;
通过现场获取你的山坳值:如果为true,您可以使用和聚集在你的T-SQL和DataTable
列,像得到它
请注意,如果您使用Sum
,您应该选择颜色为Group by
。
2 - 您可以使用单独的查询只选择和山坳,并使用单独的DataTable
3-之前我没有测试,但我肯定这样给你期望的输出。
改为通过DataTable进行搜索。 – jdweng
@jdweng任何代码示例? – Usama
类似于:列表 searchRows = dt.AsEnumerable()。其中(x => x.Field (“Customer_Name”)== Search_By_ItemName.Text).ToList(); int sum = searchRows.AsEnumerable()。Sum(x => x.Field (“Paid_Amount”)); –
jdweng