Calculate the running total in a datagridview

Santosh4u
Posted by Santosh4u under Windows Forms category on | Views : 9502
Description:
Let you Have Employee Table having 10 Records.At the time of Binding this Table in Grid View,you want show the Running Total of Salary in Last Column as ("Running Total") of Each Row then the Below COde will helpful to you to Show the Running Total.
VB.NET Code
  Dim dtRunningTot As New DataTable
Shared StrConn As String = ""
Private Function ConnectionString() As String
StrConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Santosh_Practice;Data Source=SANTOSH\SQLEXPRESS" 'Myconnection is Key in App.Config
Return StrConn
End Function
Private Function GetConnectObj() As SqlConnection
Return New SqlConnection(ConnectionString())
End Function
Private Function FillDatatable(ByVal Query As String) As DataTable
Dim objDT As New DataTable()
Dim objda As New SqlDataAdapter(Query, ConnectionString())
objda.Fill(objDT)
Return objDT
End Function
Private Sub frmRunningTotal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dtRunningTot = FillDatatable("Select salary from EMP")
DataGridView1.DataSource = dtRunningTot
For i As Integer = 0 To DataGridView1.Rows.Count - 2
For j As Integer = 0 To DataGridView1.Columns.Count - 1
If i = 0 Then
DataGridView1.Rows(i).Cells("RunningTotal").Value = Val(DataGridView1.Rows(i).Cells("Salary").Value)
Else
DataGridView1.Rows(i).Cells("RunningTotal").Value = Val(DataGridView1.Rows(i - 1).Cells("RunningTotal").Value) + Val(DataGridView1.Rows(i).Cells("Salary").Value)
End If
Next
Next
End Sub

C#.NET Code
  private DataTable dtRunningTot = new DataTable();
public static string StrConn = "";
//TODO: INSTANT C# TODO TASK: Insert the following converted event handler wireups at the end of the 'InitializeComponent' method for forms, 'Page_Init' for web pages, or into a constructor for other classes:
base.Load += frmRunningTotal_Load;

private string ConnectionString()
{
StrConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Santosh_Practice;Data Source=SANTOSH\\SQLEXPRESS"; //Myconnection is Key in App.Config
return StrConn;
}
private SqlConnection GetConnectObj()
{
return new SqlConnection(ConnectionString());
}
private DataTable FillDatatable(string Query)
{
DataTable objDT = new DataTable();
SqlDataAdapter objda = new SqlDataAdapter(Query, ConnectionString());
objda.Fill(objDT);
return objDT;
}
private void frmRunningTotal_Load(object sender, System.EventArgs e)
{
dtRunningTot = FillDatatable("Select salary from EMP");
DataGridView1.DataSource = dtRunningTot;
for (int i = 0; i <= DataGridView1.Rows.Count - 2; i++)
{
for (int j = 0; j < DataGridView1.Columns.Count; j++)
{
if (i == 0)
{
DataGridView1.Rows[i].Cells["RunningTotal"].Value = Microsoft.VisualBasic.Conversion.Val(DataGridView1.Rows[i].Cells["Salary"].Value);
}
else
{
DataGridView1.Rows[i].Cells["RunningTotal"].Value = Microsoft.VisualBasic.Conversion.Val(DataGridView1.Rows[i - 1].Cells["RunningTotal"].Value) + Microsoft.VisualBasic.Conversion.Val(DataGridView1.Rows[i].Cells["Salary"].Value);
}
}
}
}

SQL QUERY TO CALCULATE RUNNING TOTAL
SELECT A.EMPID,A.SALARY,SUM(B.SALARY) AS 'RUNNING TOTAL' FROM EMP A,EMP B
WHERE A.EMPID >=B.EMPID
GROUP BY A.EMPID,A.SALARY
ORDER BY EMPID

Thanx and Regards
Santosh

Comments or Responses

Login to post response