更新访问数据库中的表 [英] An updating of a table in access database

问题描述

我需要创建访问数据库并将其填入代码中。我使用以下代码在里面创建数据库和表。前几列需要存储信息，lastone用于主键。

Hi, I need to create access database and fill it up in code. I do use following code to create database and table inside. First few columns need to store information, lastone is for Primary Key.

Dim MainBase As New ADOX.Catalog
Dim MainBaseIndex As ADOX.Index
Dim ShopsTable As New ADOX.Table

MainBase.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.mdb;Jet OLEDB:Database Password=111"

ShopsTable.Name = "Shops"
ShopsTable.ParentCatalog = MainBase
ShopsTable.Columns.Append("CatalogueIndex", DataTypeEnum.adVarWChar)
ShopsTable.Columns.Append("ShopIndex", DataTypeEnum.adInteger)
ShopsTable.Columns.Append("ShopName", DataTypeEnum.adVarWChar)
ShopsTable.Columns.Append("ShopWWW", DataTypeEnum.adVarWChar)
ShopsTable.Columns.Append("ShopDate", DataTypeEnum.adDate)

'Will be as Primary Key
ShopsTable.Columns.Append("LineId", DataTypeEnum.adInteger)
ShopsTable.Columns("LineId").Properties("Autoincrement").Value = True

'Key
MainBaseIndex = New ADOX.Index
MainBaseIndex.Name = "PrimaryKey"
MainBaseIndex.PrimaryKey = True
MainBaseIndex.Columns.Append("LineId")
ShopsTable.Indexes.Append(MainBaseIndex)

MainBase.Tables.Append(ShopsTable)
ShopsTable = Nothing

魔术它有效。



然后我正在阅读它填写数据。

Magicly it works.

Then I'm reading it to fill up with data.

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.mdb;Jet OLEDB:Database Password=111"
Dim cmd As New OleDb.OleDbCommand With {.Connection = cn}
cn.Open()
Dim DBShops As New DataSet

cmd.CommandText = "SELECT * FROM Shops"
DA = New OleDbDataAdapter(cmd)
DA.Fill(DBShops)

DA.Dispose()
cn.Close()

这是有效的，我几乎相信上帝我填写了我的数据，现在试图保存它。

It's works and I'm almost belive in God. I fill up my data and now trying to save it.

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.mdb;Jet OLEDB:Database Password=111"
Dim cmd As New OleDb.OleDbCommand With {.Connection = cn}
Dim MyBuilder As OleDbCommandBuilder
cn.Open()

cmd.CommandText = "SELECT * FROM Shops"
DA = New OleDbDataAdapter(cmd)
MyBuilder = New OleDbCommandBuilder(DA)
DA.Update(DBShops)

这又有效了！是的，上帝存在，魔法是真实的。我是一名成功的程序员，将赚取数十亿美元！



同时。我做了一次更新，做了两次，但第三次我被错误压垮了：并发冲突：UpdateCommand影响了预期的1条记录中的0条。怎么样？为什么？！..



---



额外来自网络：



这个例外似乎仍然会在更新时引起人们的注意，例如DataSet。



这只是因为默认情况下DataSet（或ADO.Net）使用Optimistic Concurrency。



这意味着更新时，整行（而不仅仅是更改的列） ）用于与数据库中的数据进行比较。



这意味着如果您尝试更新数据库中不再存在的行，则更新从DataAdapter开始，更新将失败，但上面有例外。



出现这种情况的典型情况是你将数据输入断开连接的DataSet，你做了一些工作它然后尝试更新。



但是，在您选择数据到客户端和发送更新之间，另一个用户正在从他的应用程序中删除该行。 />


或者可能是您要从应用程序中的其他位置删除数据。



分辨率：



错了！据我所知，我只是从一台计算机上读取这个数据库。另外，我很确定没有删除任何单行。仅添加和更新。所以...死胡同。

And it's works again! Yes, God is exist and magic is real. I'm successful programmer and will earn billions dollars!

Meanwhile. I did update once, did it twice but at the third time I've been crushed by error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. How? Why?!..

---

Extra from the web:

This exception still seems to catch people out when updating, for example, DataSets.

This is simply because DataSet (or ADO.Net rather) uses Optimistic Concurrency by default.

This means that when updating, the whole row (rather than the changed column only) is used for comparison with the data in the database.

This then means that if you are trying to update a row that no longer exists in the database, the update from the DataAdapter the update will fail with the exception above.

Typical scenarios when this may happen is that you get your data into the disconnected DataSet, you do some work on it and then try the update.

However, between you selecting the data into the client and sending the update, another user is deleting this row from his application.

Or it can be that you are deleting the data from somewhere else in your application.

Resolution:

Wrong! As I know my application, only it is reading this database from the single computer. Also, I pretty sure that is NO single line has been deleted. Only added and updated. So... dead-end.

推荐答案

我看到你打开了连接，但是你关闭它们了吗？ .NET框架可能无法像创建它们那样快速清理连接。尝试使用语句包装你的连接，以便在完成它们时自动销毁它们，如果有异常，它们也将被销毁。

I see that you open connections, but do you close all them? The .NET framework may not be cleaning up your connections as fast as you are creating them. Try wrapping your connections in using statements so that they are automatically destroyed when you are done with them and if there was an exception, they will also be destroyed.

这篇关于更新访问数据库中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！