Click here to Skip to main content
15,907,001 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
I have struct in middle of my project that I need Debit Credit balance like Bank statement

Look Like this
Date            Debit       Credit      Balance
2018-01-01      3250.00     0.00        3250.00
2018-01-05      0.00        1000.00     2250.00
2018-01-12      0.00        250.00      2500.00
2018-01-22      500.00      0.00        2000.00


But Once used the code which is below. I got Like this

Date            Debit       Credit      Balance
2018-01-01      3250.00     0.00        3250.00
2018-01-05      0.00        1000.00     -1000.00
2018-01-12      0.00        250.00      -250.00
2018-01-22      500.00      0.00        500.00



Pls advice me
Maideen

What I have tried:

VB
Private Sub PopulateTrans()
     Me.ReportViewer1.LocalReport.DisplayName = "Statement"
     ReportViewer1.ProcessingMode = ProcessingMode.Local
     ReportViewer1.LocalReport.ReportPath = Server.MapPath("Bank_Statement.rdlc")
     Dim dsReport As Report_DataSet = GetData()
     Dim datasource As New ReportDataSource("DataSet1", dsReport.Tables(1))
     ReportViewer1.LocalReport.DataSources.Clear()
     ReportViewer1.LocalReport.DataSources.Add(datasource)
 End Sub

 Private Function GetData() As Report_DataSet
     Dim finalbalance As Integer = 0
     Dim conString As String = ConfigurationManager.ConnectionStrings("ConnectString").ConnectionString
     Dim cmd As SqlCommand = New SqlCommand("SELECT docdate,acno,debit,credit,balance FROM tbl_Reports_all")
     Using con As SqlConnection = New SqlConnection(conString)
         Using sda As SqlDataAdapter = New SqlDataAdapter()
             cmd.Connection = con
             cmd.CommandType = CommandType.Text
             sda.SelectCommand = cmd
             Using trans As Report_DataSet = New Report_DataSet()
                 sda.Fill(trans, "Report_DataSet")
                 Dim dt As DataTable = trans.Tables("Report_DataSet")
                 For i As Integer = 0 To (dt.Rows).Count - 1
                     Dim credit As String = dt.Rows(i)("credit").ToString()
                     Dim debit As String = dt.Rows(i)("debit").ToString()

                     If credit <> "" AndAlso debit = "" Then
                         Dim balance As Integer = If(dt.Rows(i)("balance").ToString() = "", finalbalance, Convert.ToInt32(dt.Rows(i)("balance").ToString()))
                         Dim Testbalance As Integer = balance + Convert.ToInt32(dt.Rows(i)("credit").ToString())
                         finalbalance = Testbalance
                         dt.Rows(i)("balance") = finalbalance

                     ElseIf debit <> "" AndAlso credit = "" Then
                         Dim balance As Integer = If(dt.Rows(i)("balance").ToString() = "", finalbalance, Convert.ToInt32(dt.Rows(i)("balance").ToString()))
                         Dim Testbalance As Integer = balance - Convert.ToInt32(dt.Rows(i)("debit").ToString())
                         finalbalance = Testbalance
                         dt.Rows(i)("balance") = finalbalance
                     End If

                 Next
                 Return trans
             End Using
         End Using
     End Using
 End Function
Posted
Updated 5-Oct-18 0:05am
v2

1 solution

No point in "reading" the balance.

Maintain "running totals" and update the balance in each row as you read it.

for each row:

Add to total debits.
Add to total credits.
CURRENT BALANCE for the current row is simply (total debits - total credits) up to that point.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900