update statement in vb.net

[Omi4u]

Dear All Below is my update statement .


For Each row As DataGridViewRow In CashpaymentDataGridView.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,type=@type where AccountID=@id"
com.Parameters.AddWithValue("@id", row.Cells(3).Value)
com.Parameters.AddWithValue("@dr", row.Cells(8).Value)

com.Parameters.AddWithValue("@type", "CR")
com.ExecuteNonQuery()
com.CommandText = "Update COA set opening_balance = opening_balance + @dr,type=@typee where AccountID=@id"
com.Parameters("@id").Value = row.Cells(5).Value
com.Parameters("@dr").Value = row.Cells(7).Value
com.Parameters.AddWithValue("@typee", "DR")
com.ExecuteNonQuery()


End Using
End Using



Next


now here this script is working fine but what i need is ..... when user save the data, the parameter @typee and @type work as per Chart of account balance. like

if balance in chart of account goes to negative or less then 0 then it must save it as CR else DR .


i wrote a code for it but it not working


For ia As Integer = 0 To COADataGridView.Rows.Count - 1
If COADataGridView.Rows(ia).Cells(5).Value < 0 Then

com.Parameters.AddWithValue("@type", "CR")

Else

com.Parameters.AddWithValue("@type", "DR")

End If

i wrote this code after but it not working . com.Parameters("@dr").Value = row.Cells(7).Value

below is my complete code

For Each row As DataGridViewRow In CashpaymentDataGridView.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,type=@type where AccountID=@id"
com.Parameters.AddWithValue("@id", row.Cells(3).Value)
com.Parameters.AddWithValue("@dr", row.Cells(8).Value)




For ia As Integer = 0 To COADataGridView.Rows.Count - 1
If COADataGridView.Rows(ia).Cells(5).Value < 0 Then

com.Parameters.AddWithValue("@type", "CR")

Else

com.Parameters.AddWithValue("@type", "DR")

End If



com.ExecuteNonQuery()




com.CommandText = "Update COA set opening_balance = opening_balance + @dr,type=@typee where AccountID=@id"
com.Parameters("@id").Value = row.Cells(5).Value
com.Parameters("@dr").Value = row.Cells(7).Value



For ia As Integer = 0 To COADataGridView.Rows.Count - 1
If COADataGridView.Rows(ia).Cells(5).Value < 0 Then

com.Parameters.AddWithValue("@type", "CR")

Else

com.Parameters.AddWithValue("@type", "DR")

End If





com.ExecuteNonQuery()

Next

Next
End Using
End Using

Next



please check and guide accordingly


Thanks in advance

Continue reading...