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 = ?)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\bin\Debug\CCAdb.mdb;Persist Security Info=True;Mode="Share Deny None"