An updating of a table in access database

See more:

VB

MS-Access

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)

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.