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"
