CODEWITHSUNDEEP
vb.netwinformsdatagridview
Paul Johnson
Paul Johnson

Reputation: 323

DataGridView - Concurrency Issue When Updating or Deleting New Records

I have a problem with a DataGridView based application. After adding records to the grid, if I subsequently attempt to update or delete the new records I get the following respective errors. Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. Concurrency violation: the DeleteCommand affected 0 of the expected 1 records. A save of the dataset is enforced in the RowValidated event.

Private Sub uxGrid_RowValidated(sender As Object, e As DataGridViewCellEventArgs) Handles uxGrid.RowValidated
    If IsDGVRowDirty(sender, e.RowIndex) Then
        Save()
    End If
End Sub

Private Sub Save()
    Const procName As String = "Save"
    Try
        _Logger.SendLog(Me.Name & "." & procName & " - Saving alarm definition data.", NLog.LogLevel.Trace)
        _myAlarmDefinitionMngr.Save(_myDataSet)
        _Logger.SendLog(Me.Name & "." & procName & " - Alarm definition data has been saved.", NLog.LogLevel.Trace)
    Catch ex As Exception
        MsgBox("There was a problem whilst saving your changes.  Please reload the form and try again.  " & vbCrLf & vbCrLf & ex.Message, MsgBoxStyle.Critical)
        _Logger.SendLog(ex.Message & ".  Thrown in module " & Me.Name.ToString & "." & procName, NLog.LogLevel.Error, ex)
    Finally
    End Try
End Sub

The client app sits on top of a relatively simple 3-tier architectural design talking to an Oracle 11 back-end, in this case comprising the elements AlarmDefinitionManager.vb, AlarmDefinitionDB.vb, AlarmDefinition.vb.

Public Function Save(ByVal myDataSet As DataSet) As Integer
    Dim myOda As OracleDataAdapter
    Dim myConnection As New OracleConnection
    Dim myCommand As OracleCommand = Nothing
    Dim myDataAdapter As OracleDataAdapter
    Dim myBuilder As OracleCommandBuilder
    Dim sqlStatement As String

    myConnection = New OracleConnection
    myConnection.ConnectionString = _connectStr
    sqlStatement = "SELECT ID, LEGENDID, STATUSID, STATUSTYPEID, DIGITALSET FROM P_TBL_ALARMDEF"
    If myDataSet.HasChanges Then
        Try
            myOda = New OracleDataAdapter(sqlStatement, myConnection)
            myBuilder = New OracleCommandBuilder(myOda)
            myOda.SelectCommand = New OracleCommand(sqlStatement, myConnection)
            myOda.Update(myDataSet, "AlarmDefinition")
            myDataSet.AcceptChanges()
            myConnection.Close()
        Catch ex As Exception
            Throw
        Finally
            myCommand = Nothing
            myDataAdapter = Nothing
            myBuilder = Nothing
           CType(myConnection, IDisposable).Dispose()
        End Try
    End If
End Function

My understanding of the situation is that I need to refresh the datasource in the grid, such that it reflects the changes made. In order to achieve this I wish to refresh the grid by hooking the refresh process into an event in the datagridview. However none of the events which I have tried seem to work. With each event tried thus far, I have received errors to the effect that a refresh of the datasource is not permitted from within the event. On reflection this seems logical. Is there any event associated with the datagridview which I can use to force a refresh of the datasource?

Upvotes: 0

Views: 371

Answers (2)

Paul Johnson
Paul Johnson

Reputation: 323

Sorted my problem. Each row change is saved directly to the database within the RowValidated event.

Upvotes: 0

user7452368
user7452368

Reputation:

You shouldn't try to save your dataset while validating the row. do your changes to your dataset then set your datasource to null and reattach your datasource back to the grid to force a refresh.

I personally don't like making changes to the datagrid. I prefer to edit the row outside the datagrid and send an UPDATE cmd to SQL then refresh my grid. I get more control on the changes made to the grid.

Upvotes: 1

Related Questions