I have an Excel 2003 file that has a table on a spreadsheet
I can successfully fill a datatable and a datagridview using a dataadapter
When I come to use the update command however I always get a concurrency error, I don't believe it's a concurrency error but that seems to be the default error messages for zero records updated
The connection string is
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= F:\My Folder\My Sheet.xls; <br />
Extended Properties="Excel 8.0;HDR=YES";
The update statement for testing is
Update [My_Sheet$] Set [Part Code]='__foo' where ([Part Code] like '*OQ2*')
The update code is
Public Sub Update(ByVal value As PartsList.PartsListDataTableDataTable)
Try
Me.m_oDataAdapter.UpdateCommand.Connection.Open()
Me.m_oDataAdapter.Update(value)
Catch ex As DBConcurrencyException
Stop
Finally
If Me.m_oDataAdapter.UpdateCommand.Connection.State = ConnectionState.Open Then Me.m_oDataAdapter.UpdateCommand.Connection.Close()
End Try
End Sub
Error detail is:
System.Data.DBConcurrencyException was caught
Message: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
The stack trace is:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at DO_Tools.PartsListDataAdapter.Update(PartsListDataTableDataTable value) in C:\Users\Me\documents\visual studio 2010\Projects\DO_Tools\DO_Tools\_DataAdapters\PartsListDataAdapter.vb:line 168
I've changed the syntax but the error persisted
Can anyone offer a suggestion why this is not working?