将OleDBDataAdapter与Excel 2003一起使用 [英] Using OleDBDataAdapter with Excel 2003
问题描述
我有一个Excel 2003文件，在电子表格上有一个表
我可以使用dataadapter成功填充数据表和datagridview
当我来使用更新命令但是我总是得到并发错误，我不相信它是并发错误，但这似乎是零记录的默认错误消息更新
连接字符串是
Provider = Microsoft.ACE.OLEDB.12.0;数据源= F： \ My Folder \ Myy Sheet.xls; < br />
扩展属性=Excel 8.0; HDR = YES;
测试的更新声明是
更新[My_Sheet $]设置[部件代码] =''__ foo''其中（[部件代码]如''* OQ2 *''）
更新代码为
公共 Sub 更新（ ByVal 值 As PartsList.PartsListDataTableDataTable）
尝试
我 .m_oDataAdapter.UpdateCommand.Connection.Open（）
我 .m_oDataAdapter.Update（value）
Catch ex As DBConcurrencyException
停止
最后
如果 我 .m_oDataAdapter.UpdateCommand.Connection.State = ConnectionState.Open 然后 我 .m_oDataAdapter.UpdateCommand.Connection.Close（）
结束 尝试
结束 Sub
错误的详细信息是：
System.Data.DBConcurrencyException被捕获
消息：并发冲突：UpdateCommand影响了预期的1条记录中的0条。
堆栈跟踪是：
在System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors（RowUpdatedEventArgs rowUpdatedEvent，BatchCommandInfo [] batchCommands，Int32 commandCount）
在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）
在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：第168行
我已更改语法但错误仍然存在
任何人都可以提出一个建议，说明为什么这不起作用？
设置[部件代码] =''__ foo ''where（[Part Code] like''* OQ2 *''）
更新代码是
公共 Sub 更新（ ByVal value As PartsList.PartsListDataTableDataTable）
尝试
我 .m_oDataAdapter.UpdateCommand.Connection.Open（）
我 .m_oDataAdapter.Update（value）
Catch ex As DBConcurrencyException
停止
最后
如果 Me .m_oDataAdapter.UpdateCommand.Connection.State = ConnectionState.Open Then 我 .m_oDataAdapter.UpdateCommand.Connection.Close（）
结束 尝试
结束 Sub
错误的详细信息是：
System.Data.DBConcurrencyException被捕获
消息：并发冲突：UpdateCommand影响了预期的1条记录中的0条。
堆栈跟踪是：
在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 ）
在System.Data.Common.DbDataAdapter.UpdateFromDataTable（DataTable dataTable，DataTableMapping tableMapping）
在System.Data.Common.DbDataAdapter.Update（DataTable dataTable）
在DO_Tools.PartsListDataAdapter.Update（PartsListDataTableDataTable值）在C：\ Users \ Me \documents\visual studio 2010 \Projects\DO_Tools \DO_Tools \_DataAdapters \PartsListDataAdapter.vb：168行
我已经改变了语法，但错误仍然存在
有人能提出一个建议，说明为什么这不起作用？
< blockquote>我找到了解决方案
我已经为更新命令添加了所有列的参数但是我在测试期间没有使用所有这些参数，只有规范和部件代码，甚至这些在测试期间都没有在更新命令中使用。
通过更改update命令，只添加在参考中引用的参数更新命令，然后更新操作正常。
修订更新命令：
更新[My_Sheet
设置 [规范] =？ where（[Part Code] =？）
修改后的代码：
我 .m_oDataAdapter.UpdateCommand = 新 OleDb.OleDbCommand（sUpdateSQL，我 .m_oConn）
Me .m_oDataAdapter.UpdateCommand.Parameters.Add（& amp; quot; @ Specification& ; amp; quot;，OleDb.OleDbType。 Char ， 255 ，& amp; quot;规格& amp; amp; quot;）
Me .m_oDataAdapter.UpdateCommand.Parameters.Add（& amp; quot; @ OldPartCode& amp; quot; ，OleDb.OleDbType。 Char ， 255 ，& amp; quot; PartCode& amp; quot;） .SourceVersion = DataRowVersion.Original
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?
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?
I''ve found the solution
I had added parameters for all the columns to the update command but I wasn''t using all of them during testing, only Specification and Part Code and even these weren''t used in the update command during testing.
By changing the update command only adding the parameters that were referenced in the update command then the update action works fine.
Amended Update Command:
Update [My_Sheet
Set [Specification]=? where ([Part Code] = ?)
Amended Code:
Me.m_oDataAdapter.UpdateCommand = New OleDb.OleDbCommand(sUpdateSQL, Me.m_oConn) Me.m_oDataAdapter.UpdateCommand.Parameters.Add(&quot;@Specification&quot;, OleDb.OleDbType.Char, 255, &quot;Specification&quot;) Me.m_oDataAdapter.UpdateCommand.Parameters.Add(&quot;@OldPartCode&quot;, OleDb.OleDbType.Char, 255, &quot;PartCode&quot;).SourceVersion = DataRowVersion.Original
这篇关于将OleDBDataAdapter与Excel 2003一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
