none
ISSUE IN SAVING STATEMENTS RRS feed

  • Question

  • 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.

    Friday, February 28, 2020 1:29 PM

Answers

  • HI,
    I cannot check your code in all details, but you must use Using OR Finally!

        Using con As New SqlConnection(appx.myconnection)
          Try
            con.Open() ' open for all cmd.ExecuteNonQuery
            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)"
            Using cmd As New SqlCommand(cb, con)
              For Each rowa As DataGridViewRow In DataGridView1.Rows
                If Not rowa.IsNewRow Then
                  cmd.Parameters.Clear()
                  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.ExecuteNonQuery()
                End If
              Next
            End Using
            ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CR-SV-001", vbOKOnly + vbCritical, "Error Code : CRV-SV-001")
            'Finally
            '  con.Close()
            '  con.Dispose()
    
          End Try
          Try
            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)"
            Using cmd As New SqlCommand(cb1, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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.ExecuteNonQuery()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-SV-002", vbOKOnly + vbCritical, "Error Code : CRV-SV-002")
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '------------------------------ledger save----------------------------------------
          Try
            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)"
            Using cmd As New SqlCommand(cb2, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView2.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-LG-002", vbOKOnly + vbCritical, "Error Code : CRV-LG-002")
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '--------------------------------------------------SERIAL NO----------------------------------------------------------
          Try
            Dim cb4 As String = "insert into accountserialnox(serialnox) VALUES (@d1010)"
            Using cmd As New SqlCommand(cb4, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  'cmd.Parameters.Clear()
                  cmd.Parameters.AddWithValue("@d1010", row.Cells(0).Value)  'date
                  cmd.ExecuteNonQuery()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-SER-003", vbOKOnly + vbCritical, "Error Code : CRV-SER-003")
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '---------------------------------------------PLUS AND MINUS PROCESS-----------------------------------------------------------
          Try
    
    
    
            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.Clear()
                  com.Parameters("@id").Value = row.Cells(4).Value
                  com.Parameters("@dr").Value = row.Cells(8).Value
                  com.ExecuteNonQuery()
    
                End Using
              End Using
            Next
    
          Catch ex As Exception
            MsgBox(ex.Message)
    
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '----------------------------------------------------------------------
    
    
          Try
    
    
            Dim cb5 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
            Using cmd As New SqlCommand(cb5, con)
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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()
                End If
              Next
            End Using ' close and dispose SqlCommand
          Catch ex As Exception
            MsgBox(ex.Message)
    
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
    
          Try
            Dim cb6 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
            Using cmd As New SqlCommand(cb6, con)
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
    
          Catch ex As Exception
            MsgBox(ex.Message)
    
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
    
        End Using
    
    
        '------------------------------------------------------------------
        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 = ""


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Saturday, February 29, 2020 2:44 PM

All replies

  • IMO, if all this is being done in some method at the UI, then this is not optimal programming.  Each one of the database interactions should be in their own methods and being called.

    You should be trying to implement some kind of SoC where the code is segregated.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    Friday, February 28, 2020 2:34 PM
  • Replace ExecuteReader with ExecuteNonQuery and add cmd.Parameters.Clear after each ExecuteNonQuery.

    Friday, February 28, 2020 4:43 PM
  • same error 
    Saturday, February 29, 2020 5:29 AM
  • im sorry abt that can u guide me how i can do that ? @ DA924x
    Saturday, February 29, 2020 5:39 AM
  • Hi,
    change your code like following:

        Using con As New SqlConnection(appx.myconnection)
          Try
            con.Open() ' open for all cmd.ExecuteNonQuery
            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)"
            Using cmd As New SqlCommand(cb, con)
              For Each rowa As DataGridViewRow In DataGridView1.Rows
                If Not rowa.IsNewRow Then
                  cmd.Parameters.Clear()
                  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.ExecuteNonQuery()
                End If
              Next
            End Using ' close and dispose SqlCommand
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CR-SV-001", vbOKOnly + vbCritical, "Error Code : CRV-SV-001")
          End Try
          Try
            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)"
            Using cmd As New SqlCommand(cb1, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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.ExecuteNonQuery()
                End If
              Next
            End Using ' close and dispose SqlCommand
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-SV-002", vbOKOnly + vbCritical, "Error Code : CRV-SV-002")
          End Try
          '------------------------------ledger save----------------------------------------
          Try
            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)"
            Using cmd As New SqlCommand(cb2, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView2.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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()
                End If
              Next
            End Using ' close and dispose SqlCommand
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-LG-002", vbOKOnly + vbCritical, "Error Code : CRV-LG-002")
          End Try
          '--------------------------------------------------SERIAL NO----------------------------------------------------------
          Try
            Dim cb4 As String = "insert into accountserialnox(serialnox) VALUES (@d1010)"
            Using cmd As New SqlCommand(cb4, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  cmd.Parameters.AddWithValue("@d1010", row.Cells(0).Value)  'date
                  cmd.ExecuteNonQuery()
                End If
              Next
            End Using ' close and dispose SqlCommand
          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 com As New SqlCommand("", con)
              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.Clear()
              com.Parameters("@id").Value = row.Cells(4).Value
              com.Parameters("@dr").Value = row.Cells(8).Value
              com.ExecuteNonQuery()
            End Using ' close and dispose SqlCommand
          Next
          '----------------------------------------------------------------------
          Dim cb5 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
          Using cmd As New SqlCommand(cb5, con)
            For Each row As DataGridViewRow In DataGridView1.Rows
              If Not row.IsNewRow Then
                cmd.Parameters.Clear()
                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()
              End If
            Next
          End Using ' close and dispose SqlCommand
          '
          Dim cb6 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
          Using cmd As New SqlCommand(cb6, con)
            For Each row As DataGridViewRow In DataGridView1.Rows
              If Not row.IsNewRow Then
                cmd.Parameters.Clear()
                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()
              End If
            Next
          End Using ' close and dispose SqlCommand
        End Using ' close and dispose SqlConnection
        '------------------------------------------------------------------
        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 = ""    


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks





    Saturday, February 29, 2020 5:46 AM
  • so u remove all con.close?

    then how i to close con.open connection ?

    also im getting error

    ExecuteNonQuery: Connection property has not been initialized.


    • Edited by Omi4u Saturday, February 29, 2020 7:14 AM
    Saturday, February 29, 2020 7:07 AM
  • Hi,
    End Using close the connection and dispose the connection object (see comment in my code).

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Saturday, February 29, 2020 7:12 AM
  • dear do i have to remove end using with

        con.Close()
                        con.Dispose()

    ?

    can u please guide me or give me an example of how to do it by posting some codes of mine ?
    • Edited by Omi4u Saturday, February 29, 2020 8:05 AM
    Saturday, February 29, 2020 8:04 AM
  • Hi,
    I correct my code:

          Using cmd As New SqlCommand(cb6, con)


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Saturday, February 29, 2020 8:11 AM
  • Hi,
    I correct my posted code. 

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Saturday, February 29, 2020 8:12 AM
  • ok but how to use 

    close and dispose SqlCommand

    do i have to remove end using and write con.close and con.dispose?

    Saturday, February 29, 2020 9:08 AM
  • Hi,
    no!

    Using con As New SqlConnection(appx.myconnection) ' instantiate new connection object
        con.Open() ' open for all cmd.ExecuteNonQuery
    ' if you use only SqlDataAdapter it is not necessary to open connection, in other cases you must open connection
        Dim cb As String = "insert into …" ' sql string
        Using cmd As New SqlCommand(cb, con) ' instantiate new command object with SQL string and connection object
            For Each rowa As DataGridViewRow In DataGridView1.Rows
    ' if you use foreach and parameters.AddWithValue inside you must at first clear parameter collection
                  cmd.Parameters.Clear()
                  cmd.Parameters.AddWithValue("@d25", row.Cells(0).Value)
                  Dim cnt = cmd.ExecuteNonQuery() ' in cnt you can find the number of affected rows
            Next
        End Using ' implicitly close and dispose SQL command object, separately close and dispose sql command object is not necessary and can run into errors
    End Using ' implicitly close and dispose SQL connection object, separately close and dispose sql connection is not necessary and can run into errors
    
    ___
    
    Using con As New SqlConnection(appx.myconnection) ' instantiate new connection object
    ...
    End Using ' implicitly close and dispose SqlConnection, separately close and dispose sql connection is not necessary and can run into errors
    
    is the same as
    
    Dim con As SqlConnection
    Try
        con = New SqlConnection(appx.myconnection) ' instantiate new connection object
    ...
    Catch
    ...
    Finally
        con?.close
        con?.Dispose
    End Try


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks




    Saturday, February 29, 2020 9:53 AM
  • hi peter 

    please check if my code is ok now 

        Using con As New SqlConnection(appx.myconnection)
                Try
                    con.Open() ' open for all cmd.ExecuteNonQuery
                    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)"
                    Using cmd As New SqlCommand(cb, con)
                        For Each rowa As DataGridViewRow In DataGridView1.Rows
                            If Not rowa.IsNewRow Then
                                cmd.Parameters.Clear()
                                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.ExecuteNonQuery()
                            End If
                        Next
                    End Using
                    ' close and dispose SqlCommand
    
                Catch ex As Exception
                    MsgBox(ex.Message & " " & "Error Code : CR-SV-001", vbOKOnly + vbCritical, "Error Code : CRV-SV-001")
                Finally
                    con.Close()
                    con.Dispose()
    
                End Try
                Try
                    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)"
                    Using cmd As New SqlCommand(cb1, con)
                        ' Data to be inserted
                        For Each row As DataGridViewRow In DataGridView1.Rows
                            If Not row.IsNewRow Then
                                cmd.Parameters.Clear()
                                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.ExecuteNonQuery()
                            End If
                        Next
                    End Using ' close and dispose SqlCommand
                 
                Catch ex As Exception
                    MsgBox(ex.Message & " " & "Error Code : CRV-SV-002", vbOKOnly + vbCritical, "Error Code : CRV-SV-002")
                Finally
                    con.Close()
                    con.Dispose()
                End Try
                '------------------------------ledger save----------------------------------------
                Try
                    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)"
                    Using cmd As New SqlCommand(cb2, con)
                        ' Data to be inserted
                        For Each row As DataGridViewRow In DataGridView2.Rows
                            If Not row.IsNewRow Then
                                cmd.Parameters.Clear()
                                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()
                            End If
                        Next
                    End Using ' close and dispose SqlCommand
             
                Catch ex As Exception
                    MsgBox(ex.Message & " " & "Error Code : CRV-LG-002", vbOKOnly + vbCritical, "Error Code : CRV-LG-002")
                Finally
                    con.Close()
                    con.Dispose()
                End Try
                '--------------------------------------------------SERIAL NO----------------------------------------------------------
                Try
                    Dim cb4 As String = "insert into accountserialnox(serialnox) VALUES (@d1010)"
                    Using cmd As New SqlCommand(cb4, con)
                        ' Data to be inserted
                        For Each row As DataGridViewRow In DataGridView1.Rows
                            If Not row.IsNewRow Then
                                cmd.Parameters.Clear()
                                cmd.Parameters.AddWithValue("@d1010", row.Cells(0).Value)  'date
                                cmd.ExecuteNonQuery()
                            End If
                        Next
                    End Using ' close and dispose SqlCommand
                 
                Catch ex As Exception
                    MsgBox(ex.Message & " " & "Error Code : CRV-SER-003", vbOKOnly + vbCritical, "Error Code : CRV-SER-003")
                Finally
                    con.Close()
                    con.Dispose()
                End Try
                '---------------------------------------------PLUS AND MINUS PROCESS-----------------------------------------------------------
                Try
    
          
    
                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()
    
                        End Using
                    End Using
                    Next
    
                   Catch ex As Exception
                Finally
                    con.Close()
                    con.Dispose()
                End Try
                '----------------------------------------------------------------------
    
    
                Try
    
          
                Dim cb5 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
                Using cmd As New SqlCommand(cb5, con)
                    For Each row As DataGridViewRow In DataGridView1.Rows
                        If Not row.IsNewRow Then
                            cmd.Parameters.Clear()
                            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()
                        End If
                    Next
                End Using ' close and dispose SqlCommand
                   Catch ex As Exception
                Finally
                    con.Close()
                    con.Dispose()
                End Try
    
                Try
                    Dim cb6 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
                    Using cmd As New SqlCommand(cb6, con)
                        For Each row As DataGridViewRow In DataGridView1.Rows
                            If Not row.IsNewRow Then
                                cmd.Parameters.Clear()
                                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()
                            End If
                        Next
                    End Using ' close and dispose SqlCommand
    
    
                Catch ex As Exception
    
                Finally
                    con.Close()
                    con.Dispose()
                End Try
    
            End Using
    
    
            '------------------------------------------------------------------
            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 = ""

    Saturday, February 29, 2020 11:22 AM
  • im sorry abt that can u guide me how i can do that ? @ DA924x

    Like what is being shown make some methods in the form class and call them individually and don't slam all the code together like you have done.

    Some rules you may want to follow.

    https://dzone.com/articles/rule-30-–-when-method-class-or

    public sub SomeMethodName()
        Using con As New SqlConnection(appx.myconnection)
                Try
                    con.Open() ' open for all cmd.ExecuteNonQuery
                    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)"
                    Using cmd As New SqlCommand(cb, con)
                        For Each rowa As DataGridViewRow In DataGridView1.Rows
                            If Not rowa.IsNewRow Then
                                cmd.Parameters.Clear()
                                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.ExecuteNonQuery()
                            End If
                        Next
                    End Using
                    ' close and dispose SqlCommand
    
                Catch ex As Exception
                    MsgBox(ex.Message & " " & "Error Code : CR-SV-001", vbOKOnly + vbCritical, "Error Code : CRV-SV-001")
                Finally
                    con.Close()
                    con.Dispose()
    
                End Try
    end sub
    

    Saturday, February 29, 2020 1:48 PM
  • HI,
    I cannot check your code in all details, but you must use Using OR Finally!

        Using con As New SqlConnection(appx.myconnection)
          Try
            con.Open() ' open for all cmd.ExecuteNonQuery
            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)"
            Using cmd As New SqlCommand(cb, con)
              For Each rowa As DataGridViewRow In DataGridView1.Rows
                If Not rowa.IsNewRow Then
                  cmd.Parameters.Clear()
                  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.ExecuteNonQuery()
                End If
              Next
            End Using
            ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CR-SV-001", vbOKOnly + vbCritical, "Error Code : CRV-SV-001")
            'Finally
            '  con.Close()
            '  con.Dispose()
    
          End Try
          Try
            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)"
            Using cmd As New SqlCommand(cb1, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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.ExecuteNonQuery()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-SV-002", vbOKOnly + vbCritical, "Error Code : CRV-SV-002")
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '------------------------------ledger save----------------------------------------
          Try
            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)"
            Using cmd As New SqlCommand(cb2, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView2.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-LG-002", vbOKOnly + vbCritical, "Error Code : CRV-LG-002")
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '--------------------------------------------------SERIAL NO----------------------------------------------------------
          Try
            Dim cb4 As String = "insert into accountserialnox(serialnox) VALUES (@d1010)"
            Using cmd As New SqlCommand(cb4, con)
              ' Data to be inserted
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  'cmd.Parameters.Clear()
                  cmd.Parameters.AddWithValue("@d1010", row.Cells(0).Value)  'date
                  cmd.ExecuteNonQuery()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
          Catch ex As Exception
            MsgBox(ex.Message & " " & "Error Code : CRV-SER-003", vbOKOnly + vbCritical, "Error Code : CRV-SER-003")
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '---------------------------------------------PLUS AND MINUS PROCESS-----------------------------------------------------------
          Try
    
    
    
            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.Clear()
                  com.Parameters("@id").Value = row.Cells(4).Value
                  com.Parameters("@dr").Value = row.Cells(8).Value
                  com.ExecuteNonQuery()
    
                End Using
              End Using
            Next
    
          Catch ex As Exception
            MsgBox(ex.Message)
    
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
          '----------------------------------------------------------------------
    
    
          Try
    
    
            Dim cb5 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
            Using cmd As New SqlCommand(cb5, con)
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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()
                End If
              Next
            End Using ' close and dispose SqlCommand
          Catch ex As Exception
            MsgBox(ex.Message)
    
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
    
          Try
            Dim cb6 As String = "insert into ledgerbalance(coaid,accounttitle,dated,openingbalance,closingbalance) VALUES (@d200,@d201,@d202,@d203,@d204)"
            Using cmd As New SqlCommand(cb6, con)
              For Each row As DataGridViewRow In DataGridView1.Rows
                If Not row.IsNewRow Then
                  cmd.Parameters.Clear()
                  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()
                End If
              Next
            End Using ' close and dispose SqlCommand
    
    
          Catch ex As Exception
            MsgBox(ex.Message)
    
            'Finally
            '  con.Close()
            '  con.Dispose()
          End Try
    
        End Using
    
    
        '------------------------------------------------------------------
        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 = ""


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Saturday, February 29, 2020 2:44 PM