another dreaded Concurrency Violation problem

[maxhodges]

My code generated a concurrency violation when I have an ORDER BY
clause in the SQL, but it works fine without the ORder by. What gives?
Is MS not supporting ORder By in this ADO.NET disconnected DataSet
paradigm?

X "SELECT * FROM tblKanji ORDER BY CardID ASC"
O "SELECT * FROM tblKanji"

Dim sql As String = "SELECT * FROM tblKanji ORDER BY " &
orderByKey & " " & OrderDirection
Debug.WriteLine(sql)

m_da = New OleDbDataAdapter(sql, m_cn)
m_da.FillSchema(m_ds, SchemaType.Source, "Kanji")
m_da.Fill(m_ds, "Kanji")

Dim cmdBuilder As New OleDbCommandBuilder(m_da)
With m_da
.InsertCommand = cmdBuilder.GetInsertCommand
.DeleteCommand = cmdBuilder.GetDeleteCommand
.UpdateCommand = cmdBuilder.GetUpdateCommand
End With

With m_ds.Tables("Kanji")
.Rows(0)("StrokeCount") =
..Rows(0)("StrokeCount").ToString & "9"
m_da.Update(m_ds, "Kanji")
End With

 

[David Sceppa]

Max,

The exception you described occurs when the DataAdapter
executes the corresponding query (UpdateCommand / DeleteCommand)
to submit the pending change, but the Command reports that the
query affected no rows in the database. ADO.NET itself is not
even aware of the ORDER BY clause in the DataAdapter's
SelectCommand.

Keep in mind that the OleDbCommandBuilder asks your OLE DB
provider and back-end for schema information (base column and
table names, key info, etc.) about the resultset in order to
construct the updating logic for the DataAdapter. The updating
logic should be the same regardless of whether or not the
DataAdapter's SelectCommand contains an ORDER BY clause.

I ran similar code using the OLE DB .NET Data Provider
accessing a SQL Server database and did not receive an exception.

Your best bet would be to isolate the problem as much as
possible to determine what's involved. The more information you
can provide about the minimum requirements to reproduce the
problem, the better. (Does the problem only reproduce with a
specific table? Does the problem require a column of a
particular data type in the resultset? etc.)

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.