ISSUE IN SAVING STATEMENTS

Omi4u · Feb 28, 2020

dear all

im facing issues while saving my cash payment.

below is my code

Try
con = New SqlConnection(appx.myconnection)
con.Open()
Dim cb As String = "insert into cashreceipt(cashreceiptid,date,accountiddr,debitaccount,accountidcr,creditaccount,debitamount,creditamount,type,narrations,totalamountdr,totalamountcr,unit,textid) Values (@d12,@d13,@d14,@d15,@d16,@d17,@d18,@d19,@d20,@d21,@d22,@d23,@d24,@d25)"
cmd = New SqlCommand(cb)
cmd.Connection = (con)
cmd.Prepare()
For Each rowa As DataGridViewRow In DataGridView1.Rows
If Not rowa.IsNewRow Then
cmd.Parameters.AddWithValue("@d12", rowa.Cells(2).Value)
cmd.Parameters.AddWithValue("@d13", rowa.Cells(3).Value)
cmd.Parameters.AddWithValue("@d14", AccountiddrTextEdit.Text)
cmd.Parameters.AddWithValue("@d15", DebitaccountTextEdit.Text)
cmd.Parameters.AddWithValue("@d16", rowa.Cells(4).Value)
cmd.Parameters.AddWithValue("@d17", rowa.Cells(5).Value)
cmd.Parameters.AddWithValue("@d18", rowa.Cells(7).Value)
cmd.Parameters.AddWithValue("@d19", rowa.Cells(8).Value)
cmd.Parameters.AddWithValue("@d20", TypeTextEdit.Text)
cmd.Parameters.AddWithValue("@d21", rowa.Cells(6).Value)
cmd.Parameters.AddWithValue("@d22", TotalamountcrLabel1.Text)
cmd.Parameters.AddWithValue("@d23", TotalamountdrSpinEdit.Text)
cmd.Parameters.AddWithValue("@d24", rowa.Cells(1).Value)
cmd.Parameters.AddWithValue("@d25", rowa.Cells(12).Value)
cmd.Connection = con
cmd.ExecuteReader()
End If
Next
con.Close()
Catch ex As Exception
MsgBox(ex.Message & " " & "Error Code : CR-SV-001", vbOKOnly + vbCritical, "Error Code : CRV-SV-001")
End Try
Try
con = New SqlConnection(appx.myconnection)
con.Open()
Dim cb1 As String = "insert into cashreceiptjoin(serialnox,unit,cashreceiptid,date,accountiddr,debitaccount,accountidcr,creditaccount,amountdebit,amountcredit,narration,amountinwords,username) VALUES (@d25,@d26,@d27,@d28,@d29,@d30,@d31,@d32,@d33,@d34,@d35,@d36,@d37)"
cmd = New SqlCommand(cb1)
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("@d25", row.Cells(0).Value) 'cpvid
cmd.Parameters.AddWithValue("@d26", row.Cells(1).Value) 'date
cmd.Parameters.AddWithValue("@d27", row.Cells(2).Value) 'accountiddr
cmd.Parameters.AddWithValue("@d28", row.Cells(3).Value) 'debitaccount
cmd.Parameters.AddWithValue("@d29", AccountiddrTextEdit.Text)
cmd.Parameters.AddWithValue("@d30", DebitaccountTextEdit.Text) 'paymentto
cmd.Parameters.AddWithValue("@d31", row.Cells(4).Value) 'accountidcr
cmd.Parameters.AddWithValue("@d32", row.Cells(5).Value) 'coato
cmd.Parameters.AddWithValue("@d33", row.Cells(7).Value) 'dr
cmd.Parameters.AddWithValue("@d34", row.Cells(8).Value) 'cr
cmd.Parameters.AddWithValue("@d35", row.Cells(6).Value) 'description
cmd.Parameters.AddWithValue("@d36", AmountinwordsLabel1.Text) 'userid
cmd.Parameters.AddWithValue("@d37", UsernameTextEdit.Text) 'unit
'cmd.Parameters.AddWithValue("@d37", Label2.Text & " Only") 'total
'cmd.Parameters.AddWithValue("@d38", Label2.Text & " Only") 'total
' cmd.Parameters.AddWithValue("@d10", row.Cells(9).Value) 'userid
'blncecr
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End If
Next
con.Close()
Catch ex As Exception
MsgBox(ex.Message & " " & "Error Code : CRV-SV-002", vbOKOnly + vbCritical, "Error Code : CRV-SV-002")
End Try
'------------------------------ledger save----------------------------------------
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 DataGridView2.Rows
If Not row.IsNewRow Then
cmd.Parameters.AddWithValue("@d100", row.Cells(1).Value) 'serial no
cmd.Parameters.AddWithValue("@d101", row.Cells(0).Value) 'dateledger
cmd.Parameters.AddWithValue("@d102", row.Cells(6).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(9).Value) 'description
cmd.Parameters.AddWithValue("@d106", row.Cells(10).Value) 'closing balance
cmd.Parameters.AddWithValue("@d107", row.Cells(11).Value) 'unit
cmd.Parameters.AddWithValue("@d108", row.Cells(4).Value) 'accountiddr
cmd.Parameters.AddWithValue("@d109", row.Cells(5).Value) 'accoutnamedr
cmd.Parameters.AddWithValue("@d110", row.Cells(2).Value) 'accountidcr
cmd.Parameters.AddWithValue("@d111", row.Cells(3).Value) ' accountnamecr
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End If
Next
con.Close()
Catch ex As Exception
MsgBox(ex.Message & " " & "Error Code : CRV-LG-002", vbOKOnly + vbCritical, "Error Code : CRV-LG-002")
End Try
'--------------------------------------------------SERIAL NO----------------------------------------------------------
Try
con = New SqlConnection(appx.myconnection)
con.Open()
Dim cb4 As String = "insert into accountserialnox(serialnox) VALUES (@d1010)"
cmd = New SqlCommand(cb4)
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("@d1010", row.Cells(0).Value) 'date
' cmd.Parameters.AddWithValue("@d10", row.Cells(9).Value) 'userid
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End If
Next
con.Close()
Catch ex As Exception
MsgBox(ex.Message & " " & "Error Code : CRV-SER-003", vbOKOnly + vbCritical, "Error Code : CRV-SER-003")
End Try
'---------------------------------------------PLUS AND MINUS PROCESS-----------------------------------------------------------
For Each row As DataGridViewRow In DataGridView1.Rows
' Using cn As New SqlConnection(appx.myconnection)
Using cn As New SqlConnection(appx.myconnection)
cn.Open()
Using com As New SqlCommand("", cn)
com.CommandText = "Update COA set openingbalance = openingbalance + @dr where coaid=@id"
com.Parameters.AddWithValue("@id", AccountiddrTextEdit.Text)
com.Parameters.AddWithValue("@dr", row.Cells(7).Value)
com.ExecuteNonQuery()
com.CommandText = "Update COA set openingbalance = openingbalance - @dr where coaid=@id"
com.Parameters("@id").Value = row.Cells(4).Value
com.Parameters("@dr").Value = row.Cells(8).Value
com.ExecuteNonQuery()
' com.CommandText = "Update coa set type = @dr where AccountID=@id"
' com.Parameters("@id").Value = row.Cells(5).Value
' com.Parameters("@dr").Value = row.Cells(16).Value
' com.ExecuteNonQuery()
' com.CommandText = "Update coa set type = @dr where AccountID=@id"
' com.Parameters("@id").Value = row.Cells(3).Value
' com.Parameters("@dr").Value = row.Cells(16).Value
' com.ExecuteNonQuery()
End Using
End Using
Next
'----------------------------------------------------------------------
con = New SqlConnection(appx.myconnection)
con.Open()
Dim cb5 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
cmd = New SqlCommand(cb5)
cmd.Connection = con
cmd.Prepare()
For Each row As DataGridViewRow In DataGridView1.Rows
If Not row.IsNewRow Then
cmd.Parameters.AddWithValue("@d200", row.Cells(4).Value)
cmd.Parameters.AddWithValue("@d201", row.Cells(5).Value)
cmd.Parameters.AddWithValue("@d202", row.Cells(3).Value)
cmd.Parameters.AddWithValue("@d203", blncetxt.Text)
cmd.Parameters.AddWithValue("@d204", TextBox5.Text)
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End If
Next
con.Close()
con = New SqlConnection(appx.myconnection)
con.Open()
Dim cb6 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
cmd = New SqlCommand(cb6)
cmd.Connection = con
cmd.Prepare()

For Each row As DataGridViewRow In DataGridView1.Rows
If Not row.IsNewRow Then
cmd.Parameters.AddWithValue("@d200", AccountiddrTextEdit.Text)
cmd.Parameters.AddWithValue("@d201", DebitaccountTextEdit.Text)
cmd.Parameters.AddWithValue("@d202", row.Cells(3).Value)
cmd.Parameters.AddWithValue("@d203", blncetxtcr.Text)
cmd.Parameters.AddWithValue("@d204", TextBox4.Text)
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End If
Next
con.Close()

'------------------------------------------------------------------
Dim result As Integer = MessageBox.Show("Posted Successfully. Do you want to print", "Entry Posted Successfully", MessageBoxButtons.YesNo, MessageBoxIcon.Information)
If result = DialogResult.No Then
DataGridView1.DataSource = Nothing
DataGridView1.Rows.Clear()

DataGridView2.DataSource = Nothing
DataGridView2.Rows.Clear()

Call TsrtToolStripMenuItem_Click(sender, e)
ElseIf result = DialogResult.Yes Then
' cpvreport()
DataGridView2.DataSource = Nothing
DataGridView2.Rows.Clear()
Call TsrtToolStripMenuItem_Click(sender, e)
End If
' Call TsrtToolStripMenuItem_Click(sender, e)
' SearchLookUpEdit1.Text = "Search Unit"
SearchLookUpEdit2.Text = "Select Account"
CreditamountSpinEdit.Text = "0"
NarrationsTextEdit.Text = ""
AmountinwordsLabel1.Text = ""
TotalamountcrLabel1.Text = ""
TotalamountdrSpinEdit.Text = ""

im getting below error

There is already an open DataReader associated with this Command which must be closed first.

Continue reading...

ISSUE IN SAVING STATEMENTS

Omi4u

Guest

Similar threads