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
cmd.Prepare()
For Each row As DataGridViewRow In DataGridView1.Rows
If Not row.IsNewRow Then
cmd.Parameters.AddWithValue("@d100", row.Cells(0).Value)
cmd.Parameters.AddWithValue("@d101", row.Cells(3).Value)
cmd.Parameters.AddWithValue("@d102", row.Cells(2).Value)
cmd.Parameters.AddWithValue("@d103", row.Cells(7).Value)
cmd.Parameters.AddWithValue("@d104", row.Cells(8).Value)
cmd.Parameters.AddWithValue("@d105", row.Cells(6).Value)
cmd.Parameters.AddWithValue("@d106", row.Cells(13).Value)
cmd.Parameters.AddWithValue("@d107", row.Cells(1).Value)
cmd.Parameters.AddWithValue("@d108", row.Cells(4).Value)
cmd.Parameters.AddWithValue("@d109", row.Cells(5).Value)
cmd.Parameters.AddWithValue("@d110", AccountidcrTextEdit.Text)
cmd.Parameters.AddWithValue("@d111", cashaccounttxtedit.Text)
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.