Date_ Ref_no Account_Code Account_Name Details Debit Credit Balance
1/1/2013 1002 Cash-in-Hand Jkhjkjk 1000 0 1000
14/5/2013 CH99273601 1002 Cash-in-Hand Hhtr 0 500 1000
Date_ Ref_no Account_Code Account_Name Details Debit Credit Balance
1/1/2013 1002 Cash-in-Hand Jkhjkjk 1000 0 1000
14/5/2013 CH99273601 1002 Cash-in-Hand Hhtr 0 500 500
Please I want the balance column to automatic compute the balance according to the previous debit and credit entry. So far this is my code: What i want is the code should give the output of the second table in datadrid at form load.
Imports System.Data.SqlClient
Public Class Form1
Dim sCommand As SqlCommand
Dim sAdapter As SqlDataAdapter
Dim sBuilder As SqlCommandBuilder
Dim sDs As DataSet
Dim sTable As DataTable
Dim dtRunningTot As New DataTable
Shared StrConn As String = ""
Private Function ConnectionString() As String
StrConn = "Data Source=.\sqlexpress;Initial Catalog=icmisc;Integrated Security=True"
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 Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dtRunningTot = FillDatatable("Select date_,ref_no,account_code,account_name,details,debit,credit,(select sum(debit-credit) as balance from cashbook where cashbook.id <= t1.id )as balance from cashbook as t1 order by date_")
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("balance").Value = Val(DataGridView1.Rows(i).Cells("debit").Value)
Else
DataGridView1.Rows(i).Cells("balance").Value = Val(DataGridView1.Rows(i - 1).Cells("balance").Value) + Val(DataGridView1.Rows(i).Cells("debit").Value)
End If
Next
Next
End Sub
End Class