How to do opening and closing ... Please help

See more:

VB

CrystalReports

, +

I am facing an issue in crystal reports. I am not able to get or solve the issue of opening balance which is the closing balance of previous day. Let me show you my ledger structure in ms sql.

[dbo].[ledgerreport](
[ledgerautonumber] [int] IDENTITY(1,1) NOT NULL,
[serialno] [varchar](50) NULL,
[accountiddr] [varchar](50) NULL,
[accountnamedr] [varchar](50) NULL,
[accountidcr] [varchar](50) NULL,
[accountnamecr] [varchar](50) NULL,
[dateledger] [date] NULL,
[voucherid] [varchar](50) NULL,
[debitamount] [decimal](18, 2) NULL,
[creditamount] [decimal](18, 2) NULL,
[description] [varchar](50) NULL,
[closingbalance] [decimal](18, 2) NULL,
[unit] [varchar](50) NULL

and my code that im using in vb.net

<pre>Try
        con = New SqlConnection(appx.myconnection)
        con.Open()
        Dim cb2 As String = "insert into ledgerreport(serialno,dateledger,voucherid,debitamount,creditamount,description,closingbalance,unit,accountiddr,accountnamedr,accountidcr,accountnamecr) VALUES (@d100,@d101,@d102,@d103,@d104,@d105,@d106,@d107,@d108,@d109,@d110,@d111)"
        cmd = New SqlCommand(cb2)
        cmd.Connection = con
        ' Prepare command for repeated execution
        cmd.Prepare()
        ' Data to be inserted
        For Each row As DataGridViewRow In DataGridView1.Rows
            If Not row.IsNewRow Then
                cmd.Parameters.AddWithValue("@d100", row.Cells(0).Value) 'serial no
                cmd.Parameters.AddWithValue("@d101", row.Cells(3).Value) 'dateledger
                cmd.Parameters.AddWithValue("@d102", row.Cells(2).Value) 'voucher id
                cmd.Parameters.AddWithValue("@d103", row.Cells(7).Value) 'debit amount
                cmd.Parameters.AddWithValue("@d104", row.Cells(8).Value) 'credit amount
                cmd.Parameters.AddWithValue("@d105", row.Cells(6).Value) 'description
                cmd.Parameters.AddWithValue("@d106", row.Cells(13).Value) 'closing balance
                cmd.Parameters.AddWithValue("@d107", row.Cells(1).Value) 'unit
                cmd.Parameters.AddWithValue("@d108", row.Cells(4).Value) 'accountiddr
                cmd.Parameters.AddWithValue("@d109", row.Cells(5).Value) 'accoutnamedr
                cmd.Parameters.AddWithValue("@d110", AccountidcrTextEdit.Text) 'accountidcr
                cmd.Parameters.AddWithValue("@d111", cashaccounttxtedit.Text) ' accountnamecr


                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
            End If
        Next
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message & " " & "Error Code : CPV-LG-002", vbOKOnly + vbCritical, "Error Code : CPV-LG-002")
    End Try

now what I am doing here is whenever user do a transaction in cash payment, the ledger code save the details in sql table. And in report I use fields and parameters to generate report. but I am not able to get the opening balance that is the closing balance of my last day report. Here I am facing issue. how I can get opening balance using my above codes.

please guide me and help me to solve the issue

let me explain more

what i need is to save closing balance after every entry to a ledger.

suppose i have two accounts

cash in hand 10,000

supplier 0

now supplier's account is 0 and i took some material from him cost 5000. now i have to pay him

so i will pass entry

supplier account 5000

cash in hand 5000

now opening balance of supplier was 0 in his ledger

after above entry closing balance will be 5000

similarly if i open cash in hand ledger it will give me opening balance = 5000

after entry it will give me closing balance = 0

this is what i need

so can i use your code

Thanks

What I have tried:

i tried above codes but i exectly dont know the real way to do it.