error in total while updating record

[Omi4u]

Dear All Please check my below code.

i have a cash payment conditional entry ..... my code is saving and updating record but it adding double value of my actual value like if i am doing Debit - 1000 it adding 2000 ad debit and if i am doing Credit 1000-it doing 2000 as credit ... Please guide

my codes are


If (CheckBox1.CheckState = CheckState.Checked) Then
DataGridView1.Visible = True
CashpaymentDataGridView.Visible = False

' typedr.Visible = True



con = New SqlConnection(appx.myconnection)
con.Open()
Dim cb As String = "insert into cashpayment(cashpaymentid,date,coa_id2,paymentfrom,coa_id,paymentto,amountdr,remarks,totalamount) Values (@d12,@d13,@d14,@d15,@d16,@d17,@d18,@d21,@d22)"
cmd = New SqlCommand(cb)
cmd.Parameters.AddWithValue("@d12", CashPaymentidTextBox.Text)
cmd.Parameters.AddWithValue("@d13", DateDateTimePicker.Value)
cmd.Parameters.AddWithValue("@d14", Coa_id2TextBox.Text)
cmd.Parameters.AddWithValue("@d15", PaymentfromTextBox.Text)
cmd.Parameters.AddWithValue("@d16", Coa_idTextBox.Text)
cmd.Parameters.AddWithValue("@d17", PaymenttoTextBox.Text)
cmd.Parameters.AddWithValue("@d18", AmountdrTextBox.Text)

cmd.Parameters.AddWithValue("@d21", RemarksTextBox.Text)
cmd.Parameters.AddWithValue("@d22", TotalamountTextBox.Text)
cmd.Connection = con
cmd.ExecuteReader()
con.Close()



con = New SqlConnection(appx.myconnection)
con.Open()

Dim cb1 As String = "insert into [cashpayment_join](cpvidj,cashpaymentid,date,coafrom,paymentfrom,coato,paymentto,dr,type,description,userid) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)"
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("@d1", row.Cells(0).Value) 'cashpaymentid
cmd.Parameters.AddWithValue("@d2", row.Cells(1).Value) 'cashpaymentid
cmd.Parameters.AddWithValue("@d3", row.Cells(2).Value) 'date
cmd.Parameters.AddWithValue("@d4", row.Cells(3).Value) 'paymentto
cmd.Parameters.AddWithValue("@d5", row.Cells(4).Value) 'paymentfrom
cmd.Parameters.AddWithValue("@d6", row.Cells(5).Value) 'dr
cmd.Parameters.AddWithValue("@d7", row.Cells(6).Value) 'cr
cmd.Parameters.AddWithValue("@d8", row.Cells(7).Value) 'description
cmd.Parameters.AddWithValue("@d9", row.Cells(8).Value) 'total
cmd.Parameters.AddWithValue("@d10", row.Cells(9).Value) 'total
cmd.Parameters.AddWithValue("@d11", row.Cells(10).Value) 'total
' cmd.Parameters.AddWithValue("@d10", row.Cells(9).Value) 'userid
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End If
Next
con.Close()



For z As Integer = 0 To DataGridView1.Rows.Count - 1

If DataGridView1.Rows(z).Cells(8).Value = "Credit".ToString Then



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 opening_balance = opening_balance - @dr where AccountID=@id"
com.Parameters.AddWithValue("@id", row.Cells(5).Value)
com.Parameters.AddWithValue("@dr", row.Cells(7).Value)
com.ExecuteNonQuery()

End Using
' cn.Close is not required
End Using




Next

End If
Next


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

CashpaymentDataGridView.DataSource = Nothing


CashpaymentDataGridView.Rows.Clear()

Call AddNewToolStripMenuItem_Click(sender, e)

ElseIf result = DialogResult.Yes Then
cpvreport()

CashpaymentDataGridView.DataSource = Nothing


CashpaymentDataGridView.Rows.Clear()

Call AddNewToolStripMenuItem_Click(sender, e)



End If




ElseIf (CheckBox1.CheckState = CheckState.Checked) Then


DataGridView1.Visible = True
CashpaymentDataGridView.Visible = False

' Try
con = New SqlConnection(appx.myconnection)
con.Open()
Dim cb As String = "insert into cashpayment(cashpaymentid,date,coa_id2,paymentfrom,coa_id,paymentto,amountdr,remarks,totalamount) Values (@d12,@d13,@d14,@d15,@d16,@d17,@d18,@d21,@d22)"
cmd = New SqlCommand(cb)
cmd.Parameters.AddWithValue("@d12", CashPaymentidTextBox.Text)
cmd.Parameters.AddWithValue("@d13", DateDateTimePicker.Value)
cmd.Parameters.AddWithValue("@d14", Coa_id2TextBox.Text)
cmd.Parameters.AddWithValue("@d15", PaymentfromTextBox.Text)
cmd.Parameters.AddWithValue("@d16", Coa_idTextBox.Text)
cmd.Parameters.AddWithValue("@d17", PaymenttoTextBox.Text)
cmd.Parameters.AddWithValue("@d18", AmountdrTextBox.Text)
cmd.Parameters.AddWithValue("@d21", RemarksTextBox.Text)
cmd.Parameters.AddWithValue("@d22", TotalamountTextBox.Text)
cmd.Connection = con
cmd.ExecuteReader()
con.Close()



con = New SqlConnection(appx.myconnection)
con.Open()

Dim cb1 As String = "insert into [cashpayment_join](cpvidj,cashpaymentid,date,coafrom,paymentfrom,coato,paymentto,dr,type,description,userid) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)"
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("@d1", row.Cells(0).Value) 'cashpaymentid
cmd.Parameters.AddWithValue("@d2", row.Cells(1).Value) 'cashpaymentid
cmd.Parameters.AddWithValue("@d3", row.Cells(2).Value) 'date
cmd.Parameters.AddWithValue("@d4", row.Cells(3).Value) 'coafrom
cmd.Parameters.AddWithValue("@d5", row.Cells(4).Value) 'paymentfrom
cmd.Parameters.AddWithValue("@d6", row.Cells(5).Value) 'coato
cmd.Parameters.AddWithValue("@d7", row.Cells(6).Value) 'paymentto
cmd.Parameters.AddWithValue("@d8", row.Cells(7).Value) 'dr
cmd.Parameters.AddWithValue("@d9", row.Cells(8).Value) 'type
cmd.Parameters.AddWithValue("@d10", row.Cells(9).Value) 'des
cmd.Parameters.AddWithValue("@d11", row.Cells(10).Value) 'total
' cmd.Parameters.AddWithValue("@d10", row.Cells(9).Value) 'userid
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End If
Next
con.Close()


For i As Integer = 0 To DataGridView1.Rows.Count - 1

If DataGridView1.Rows(i).Cells(8).Value = "Debit".ToString Then


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 opening_balance = opening_balance + @dr where AccountID=@id"
com.Parameters.AddWithValue("@id", row.Cells(5).Value)
com.Parameters.AddWithValue("@dr", row.Cells(7).Value)
com.ExecuteNonQuery()

End Using
' cn.Close is not required
End Using
Next
End If


Next


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

CashpaymentDataGridView.DataSource = Nothing


CashpaymentDataGridView.Rows.Clear()

Call AddNewToolStripMenuItem_Click(sender, e)

ElseIf result = DialogResult.Yes Then
cpvreport()

CashpaymentDataGridView.DataSource = Nothing


CashpaymentDataGridView.Rows.Clear()

Call AddNewToolStripMenuItem_Click(sender, e)



End If
' Catch ex As Exception
'MsgBox("Please Fill All Details Else Contact Developer.", vbCritical + vbOKOnly, "CPV-ERROR-0002")

' Beep()
' End Try


End If

Continue reading...