asked on

VB.net Concurrency Violation Error

I had this question after viewing VB.net Concurrency Violation Error.

Using Vb.Net and VS2017. I am trying to update a SQl table and everything works fine when I test the code with a variable (integer) set to a number that is in the table as a PK. However, when I load the same variable with the PK taken from either the datagridview (DGV) column or the dataset that fills the DGV I get a concurrency violation error. Single user so no update violations. The update are carried out with parameters. Here are some snippets of code.

This works

 ' Create the UpdateCommand.
        DiD = 137 ' This works
        
        sqlString = "UPDATE Data SET  PaidIn = @PMPaidIn WHERE DataID =@DiD"
        command = New SqlCommand(sqlString, connection)
        command.Parameters.Add("@DID", SqlDbType.Int).Value = DiD
        command.Parameters.Add("@PMPaidIn", SqlDbType.Decimal).Value = PMPaidIn

        adapter.UpdateCommand = command
        command.ExecuteNonQuery()

Open in new window

but when I do this

Private Sub DgvData_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DgvData.CellValueChanged

        If e.RowIndex < 0 Then Exit Sub
        row = e.RowIndex
        DiD = dts.Tables(0).Rows(row).Item("DataID") ' When debugging this DataID is 137 but violation occurs
....
more code load the variables for other columns
End Sub

Open in new window

I have checked GetType() of the DiD variable and DataID

Ron Malmstead

Shooting a little in the dark here but...What is the value of PMPaidin variable at the time of the update?

I read somewhere that this can be caused by sql rounding issues in a situation like you are describing.

AndyAinscow

Do I understand correctly, you get a concurrency error on this line of code?
DiD = dts.Tables(0).Rows(row).Item("DataID")

If not then please post your code where you get the error and exactly what the error message is.

Richard Sprague

ASKER

To Ron thanks for helping. The value of PMPaidIn is fine, it only crashes at DataID when I load the variable DiD with data from the DGV or table.

Andy, yes just as you said, crashes at that point.

AndyAinscow

I'm puzzled based on your description of what happens because that error sounds like someone (or something) is editing but you have only a single user environment.

Richard Sprague

ASKER

Yes. Confusing me too! Only one instance of VS is open, only me using it. Don't understand why it's Ok when I load the variable DiD with 137 but getting the same number from the DGV or table it doesn't like it. So the thought is that when I get the data from the DGV it maybe leaves the table in update mode???

AndyAinscow

Does it occur if you run the compiled app OUTSIDE of the VS debugging environment? (Just double click on the exe in windows explorer)

Richard Sprague

ASKER

Yes it does! Is this a clue......?

AndyAinscow

Just to confirm - it still errors when run outside of VS ?

Richard Sprague

ASKER

Yes, it does error.

AndyAinscow

Hmm. Somehow it seems you have this is an mid-edit state when you atempt to read. I don't know why based on your description.

Ron Malmstead

Maybe it's caused by the datagridview still being in the edit state.

Can you try calling dgv.EndEdit() before the update...or before you assign the variable value?

Richard Sprague

ASKER

Ok Ron. I will try that tomorrow as it is getting late here now. Many thanks for your time.

Richard Sprague

ASKER

Hi
Adding DGV.EndEdit(0 didn't make a difference. Part code shown below

Private Sub DgvData_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DgvData.CellValueChanged

If e.RowIndex < 0 Then Exit Sub
row = e.RowIndex
DgvData.EndEdit()
DiD = dts.Tables(0).Rows(row).Item("DataID")
With DgvData.Rows(row)
If IsDBNull(.Cells(e.ColumnIndex).Value) And e.ColumnIndex > 4 And e.ColumnIndex < 12 Then _
.Cells(e.ColumnIndex).Value = "0.00"
ParentID = .Cells("ParentID").Value

ASKER CERTIFIED SOLUTION

Richard Sprague

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial