Concurrency violation: the UpdateCommand affected 0 records.

schreck

If changing ename in datagrid and try to update error occurs:
"Concurrency violation: the UpdateCommand affected 0 records."
With OleDbDataAdapter and MSORA works well.
Is it a Beta issues?

Public Class FormTest
Inherits System.Windows.Forms.Form

Dim con As Oracle.DataAccess.Client.OraConnection
Dim cmd As Oracle.DataAccess.Client.OraCommand
Dim da As Oracle.DataAccess.Client.OraDataAdapter
Dim cb As Oracle.DataAccess.Client.OraCommandBuilder
Dim ds As DataSet

#Region " Vom Windows Form Designer generierter Code "

Public Sub New()
MyBase.New()

' Dieser Aufruf ist f|r den Windows Form-Designer erforderlich.
InitializeComponent()

' Initialisierungen nach dem Aufruf InitializeComponent() hinzuf|gen

End Sub

' Die Form |berschreibt den Lvschvorgang der Basisklasse, um Komponenten zu bereinigen.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

' F|r Windows Form-Designer erforderlich
Private components As System.ComponentModel.IContainer

'HINWEIS: Die folgende Prozedur ist f|r den Windows Form-Designer erforderlich
'Sie kann mit dem Windows Form-Designer modifiziert werden.
'Verwenden Sie nicht den Code-Editor zur Bearbeitung.
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents ButtonFill As System.Windows.Forms.Button
Friend WithEvents ButtonUpdate As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.ButtonFill = New System.Windows.Forms.Button()
Me.DataGrid1 = New System.Windows.Forms.DataGrid()
Me.ButtonUpdate = New System.Windows.Forms.Button()
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'ButtonFill
'
Me.ButtonFill.Location = New System.Drawing.Point(320, 16)
Me.ButtonFill.Name = "ButtonFill"
Me.ButtonFill.Size = New System.Drawing.Size(112, 32)
Me.ButtonFill.TabIndex = 0
Me.ButtonFill.Text = "Fill"
'
'DataGrid1
'
Me.DataGrid1.CaptionText = "Test Datenzugriff"
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(8, 64)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(544, 344)
Me.DataGrid1.TabIndex = 1
'
'ButtonUpdate
'
Me.ButtonUpdate.Location = New System.Drawing.Point(440, 16)
Me.ButtonUpdate.Name = "ButtonUpdate"
Me.ButtonUpdate.Size = New System.Drawing.Size(112, 32)
Me.ButtonUpdate.TabIndex = 2
Me.ButtonUpdate.Text = "Update"
'
'FormTest
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(560, 413)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.ButtonUpdate, Me.DataGrid1, Me.ButtonFill})
Me.Name = "FormTest"
Me.Text = "FormTest"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub

#End Region

Private Sub ButtonFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonFill.Click
' Connect
Try
con = New Oracle.DataAccess.Client.OraConnection( _
"User Id=scott;Password=tiger;Data Source=MTV_np")
con.Open()

' Create the OraCommand
cmd = New Oracle.DataAccess.Client.OraCommand("select * from emp")
cmd.Connection = con
cmd.CommandType = CommandType.Text

' Create the OraDataAdapter
da = New Oracle.DataAccess.Client.OraDataAdapter(cmd)

' Create the OraCommandBuilder
cb = New Oracle.DataAccess.Client.OraCommandBuilder(da)

da.InsertCommand = cb.GetInsertCommand
da.UpdateCommand = cb.GetUpdateCommand
da.DeleteCommand = cb.GetDeleteCommand

' Populate a DataSet
ds = New DataSet()
da.FillSchema(ds, SchemaType.Source)
da.Fill(ds)
Me.DataGrid1.DataSource = ds
Me.DataGrid1.DataMember = "Table"
Catch ee As Exception
MessageBox.Show(ee.Message)
End Try
End Sub

Private Sub ButtonUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonUpdate.Click
Try
da.Update(ds, "Table")
Catch ee As Exception
MessageBox.Show(ee.Message)
End Try
End Sub

End Class

3004

Jens,

This is a problem with ODP.NET. This issue will be fixed in the next Beta release of ODP.NET. In the meantime, a workaround would be to NOT set the individual OraDataAdapter commands (InsertCommand, UpdateCommand, and DeleteCommand) with the SQL generated by the OraCommandBuilder. Generally speaking (in ADO.NET terms), if the command properties are not set and the OraCommandBuilder is instantiated for a data adapter, invoking .Update on the dataadapter will automatically utilize the SQL generated by the commandbuilder since the "custom SQL" is not provided by the application. Essentially, leaving the dataadapter commands to null and associating the commandbuilder to the dataadapter enables "auto-SQL generation."

There's actually a benefit ODP.NET provides in leaving the command properties of the OraDataAdapter to null. For example, an UPDATE statement actually requires only the modified column data in the SET clause. Therefore, ODP.NET does not bind unmodified column data in the SET clause of an UPDATE SQL. (The WHERE clause will of course contain all the columns in the datatable (except for non-scalar data) to avoid dirty writes.) This approach can provide better performance if there are some large scalar data in the DataTable that are not modified. In other words, the OraDataAdapter does not use the public Get(Insert/Update/Delete)Command() methods when OraDataAdapter.Update() is called. Instead, OraDataAdapter uses an internal function which builds an optimized UPDATE SQL for that row that only binds the updated column data in the SET clause.

If you use the public Get(Insert/Update/Delete)Command() method explicitly as in your application, the OraCommandBuilder will generate the UPDATE SQL which sets every column in the datatable since it has to be generic enough to update any row in the DataTable. Therefore, your application is NOT actually using the enhanced functionality of "auto-generated command" that ODP.NET provides. Instead, the application is using the generated SQL as a "Custom SQL".

thanks,

nari.