I have a problem that hopefully someone can shed light on. My OleDBdataadapters and OledbTableadapters have gone on strike!!

In a simple vb.net 2005 pro project I have an Access 2003 .mdb with 2 tables each with 1 primary key and all other fields allowing zero length and not required. The database (not read only) is included in the project (see connection string below which works because data is presented on bound forms).

Although each table's data is displayed on one of 2 bound windows forms, it just refuses to update, add new or delete. I've deleted and recreated forms, etc, using wizards and just run time code but the problem is still there. I can change data manually in the mdb.

The code below refers to a site mdb table and was wizard generated. I added an update query to the TableAdapter using the wizard, see sql below. Also see connection string below that.

I get no errors displayed it just wont update. All other projects I've done in the past still update delete, add new OK? Because there are no errors I'm not sure where to look. Are there any additional references needed if you are changing data rather than just looking at it on a form? Surely not.


The site form frmSite has dataSet, BindingSource,TableAdapter and BindingNavigator components added by dropping fields from datasource window onto the form.

Public Class frmSite

    
    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

    Private Sub SiteBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SiteBindingNavigatorSaveItem.Click
 
        Me.Validate()
        Me.SiteBindingSource.EndEdit()
        Me.SiteTableAdapter.Update(Me.CCAdbDataSet.Site)

    End Sub

    Private Sub frmSite_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CCAdbDataSet.Site' table. You can move, or remove it, as needed.
        Me.SiteTableAdapter.Fill(Me.CCAdbDataSet.Site)

    End Sub
End Class

Update sql below:-
UPDATE Site
SET          SiteID = ?, Name = ?, rooms = ?, add1 = ?, add2 = ?, add3 = ?, add4 = ?, add5 = ?, add6 = ?, tel = ?, fax = ?, email = ?, capacity = ?, AllianceMembNo = ?, AllianceInsPolicyNo = ?, 
                  photo = ?, BankName = ?, BankAdd1 = ?, BankAdd2 = ?, BankAdd3 = ?, BankAdd4 = ?, BankPostCode = ?, AccountName = ?, SortCode = ?, AccountNumber = ?, 
                  Reference = ?
WHERE  (SiteID = ?) AND (? = 1 AND Name IS NULL OR
                  Name = ?) AND (? = 1 AND rooms IS NULL OR
                  rooms = ?) AND (? = 1 AND add1 IS NULL OR
                  add1 = ?) AND (? = 1 AND add2 IS NULL OR
                  add2 = ?) AND (? = 1 AND add3 IS NULL OR
                  add3 = ?) AND (? = 1 AND add4 IS NULL OR
                  add4 = ?) AND (? = 1 AND add5 IS NULL OR
                  add5 = ?) AND (? = 1 AND add6 IS NULL OR
                  add6 = ?) AND (? = 1 AND tel IS NULL OR
                  tel = ?) AND (? = 1 AND fax IS NULL OR
                  fax = ?) AND (? = 1 AND email IS NULL OR
                  email = ?) AND (? = 1 AND capacity IS NULL OR
                  capacity = ?) AND (? = 1 AND AllianceMembNo IS NULL OR
                  AllianceMembNo = ?) AND (? = 1 AND AllianceInsPolicyNo IS NULL OR
                  AllianceInsPolicyNo = ?) AND (? = 1 AND photo IS NULL OR
                  photo = ?) AND (? = 1 AND BankName IS NULL OR
                  BankName = ?) AND (? = 1 AND BankAdd1 IS NULL OR
                  BankAdd1 = ?) AND (? = 1 AND BankAdd2 IS NULL OR
                  BankAdd2 = ?) AND (? = 1 AND BankAdd3 IS NULL OR
                  BankAdd3 = ?) AND (? = 1 AND BankAdd4 IS NULL OR
                  BankAdd4 = ?) AND (? = 1 AND BankPostCode IS NULL OR
                  BankPostCode = ?) AND (? = 1 AND AccountName IS NULL OR
                  AccountName = ?) AND (? = 1 AND SortCode IS NULL OR
                  SortCode = ?) AND (? = 1 AND AccountNumber IS NULL OR
                  AccountNumber = ?) AND (? = 1 AND Reference IS NULL OR
                  Reference = ?)

Connection Path:-
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\bin\Debug\CCAdb.mdb;Persist Security Info=True;Mode="Share Deny None"

Edited 6 Years Ago by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

>Because there are no errors I'm not sure where to look.

Try to change the path of .mdb in connection string and see what happens?

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\CCAdb.mdb;Persist Security Info=True;Mode="Share Deny None"

That put me onto a new track. I started looking into paths because database is in bin\debug folder and part of project. I noticed another bin\debug folder inside my original folder! I didnt put it there! Typing 'bin debug database' into google helped as well. It seems everytime the project runs even if you delete subfolders, it creates another bin\debug subfolder with a copy of the database. Changes were obviously being saved there instead, though they didnt appear on checking? Oh well life's to short!

The comments from google said that the assembly containing the database automatically makes a copy if it isnt present by default unless you set it not to. I set property - Copy to output folder - to do not copy. Problem went away and dataadapters all worked.

Looking at some of the comments it seems this problem has been around some time and its claimed its beneficial overall because it keeps the original database as clean copy. Out of interest I created deployment setup package of the unfixed solution and it did work on another PC, updating perfectly.

This feature is a hindrance in my opinion and isnt properly pointed out. If you arent an experienced vb.net developer you could spend weeks scratching your head and getting nowhere!

Hope this is of help to other people.

I have a similar problem, the issue being it will allow me to add a new entry and edit it, but all old entries cannot be edited. I checked for the issue you meantioned, but that doesn't seem to be the problem since I can change the database by adding a new entry. The line of code the debugger keeps getting stuck at is

Me.TableAdapterManager.UpdateAll(Me.DataSet1)

where TableAddapterManager was generated by the wizard and defined as

'''<summary>
    '''TableAdapterManager is used to coordinate TableAdapters in the dataset to enable Hierarchical Update scenarios
    '''</summary>
    <Global.System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "2.0.0.0"),  _
     Global.System.ComponentModel.DesignerCategoryAttribute("code"),  _
     Global.System.ComponentModel.ToolboxItem(true),  _
     Global.System.ComponentModel.DesignerAttribute("Microsoft.VSDesigner.DataSource.Design.TableAdapterManagerDesigner, Microsoft.VSD"& _ 
        "esigner, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"),  _
     Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapterManager")>  _
    Partial Public Class TableAdapterManager
        Inherits Global.System.ComponentModel.Component
        
        Private _updateOrder As UpdateOrderOption
        
        Private _cAITableAdapter As CAITableAdapter
        
        Private _backupDataSetBeforeUpdate As Boolean
        
        Private _connection As Global.System.Data.IDbConnection

If anyone could help me with this it would be greatly appreciated. I've been stuck on this issue for a couple days. Thanks in advance

Edited 6 Years Ago by Prometheus6626: n/a

Forgot to mention, up until the line of code

Me.TableAdapterManager.UpdateAll(Me.DataSet1)

The program appears to work fine, it can edit and delete data as well as add. But when I attempt to save it in the executable using:

Private Sub BindingNavigator1SaveItem_Click_
       (ByVal sender As System.Object, ByVal e As System.EventArgs)_
        Handles BindingNavigator1SaveItem.Click
      Me.Validate()
      Me.BindingSource1.EndEdit()
      Me.TableAdapterManager.UpdateAll(Me.DataSet1)
End Sub

it stops on the last line and reports an "InvalidOperationException was Unhandled" and then specifies "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows." if a row was deleted and then a save was attempted and "Update requires a valid UpdateCommand when passed DataRow collection with modified rows." if a row was edited and it attempts to be updated

Edited 6 Years Ago by Prometheus6626: n/a

After considerable trials I added the line

DataSet1.AcceptChanges()

which eliminated the error, but it still is not saving the changes back to the database.

This is also a problem for me.. which i really scratch my head.. i also read the reason in google and its a heck of beneficial reasoning.

Concerning Waldek's workaround:
I found that when I set the DB's property to 'Do Not Copy' and I run the program in debug mode, the DB is no longer in the debug folder. I am assuming this is because the entire debug folder is deleted and rewritten at each run/debug of the program, and because I have changed the property of the DB to not copy, it simply is not written in there. So now the DB is not reverting, but it isn't even there.

I have found a workaround that copies the database from the debug bin to the project folder … essentially overwriting the entire DB and thusly "updating it". This ensures that when I start the program again (and the DB is set to always copy) the debug copy will always be the most up to date, but this seems like a very bad practice for 2 reasons.
1) The code I use to update the project DB will likely fail when I deploy the program as the project folder will not be there to update. Now, as Waldek has stated the ‘overwrite issue’ does not exist for deployed projects, but my code is going to be riddled with this workaround and I do not want to have to comment everything in and out depending on if I am debugging or deploying.
2) The resources necessary to continue this type of update will increase drastically as the DB grows. Right now it is not an issue, but soon it will be. Not to mention this is going to be part of a multi-player game (usually 6 to 8 players at a time)needing to update very often and will thusly need a more efficient way of updating each players DB over a network or The Internets.

Now, before you say that I can simply change the DB property to ‘Copy if newer’ you must keep in mind that I am continuing to develop the DB and program in tandem, and that the data/content already in the DB is important. Setting the DB property to ‘copy if newer’ would only prevent data from begin overwritten if the DB itself was never altered again. The second I alter the DB file and debug the program, all data that was placed in the DB since the last altering of the DB file will be lost. This is not acceptable; however, the workaround I mentioned above also makes this a non-issue.

So, I guess my question is… How do I keep both the DB architecture AND the data/content in the DB the most current without copying the entire database from the debug folder every time I want to update?

HI ALL I FACED THE SAME PROPLEM I ADDED THE LINE
ds1.AcceptChanges()
BEFORE THE LINE
DA.UPDATE(DS."TABLE NAME")

  1. Do not use AcceptChanges. It turns off all modification marks in the dataset.
  2. Check if you have copied the mdb file to source files of the project when created the typed dataset. In this case, every start of your application rewrites the data file in the bin directory, and you may have an illussion that your application does not modify data at all. It modifies, but you data file rolls back to initial state at each application build. If you start your application by double-clicking the exe file, everything is OK.
This article has been dead for over six months. Start a new discussion instead.