J
John Bundy
I don't recall if it was the same error, but do you have a key defined both
in the database and in your program? Also, there is a setting under advanced
options in the table adapter configuration that lets you specify concurrent
updates etc.
--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.
in the database and in your program? Also, there is a setting under advanced
options in the table adapter configuration that lets you specify concurrent
updates etc.
--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.
John said:Hi
vs 2003. vb.net winform db app with ms access backend. I have created the
data access layer from the code created by the data adapter wizard.
Problem is when MyDataAdapter.Update(MyDataset.MyTable) is executed I get a
concurrency error. As the code is generated by wizard and not modified by me
what could be the problem? I vaguely remember that using
System.Data.OleDb.OleDbType.DBDate parameter type is problematic with MS
Access backend but am not sure.
The exception stack log is;
System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand
affected 0 records.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at Contacts.frmClients.DoUpdate() in C:\Events Manager\Contacts
Combined\frmClients.vb:line 3289
The second problem is that stack log does not give any detail information on
what data field etc it has problem with. Is there a way to get more specific
info on the problem?
Full relevant code is given below. Any ideas to fix this problem would be
appreciated.
Thanks
Regards
=========================================================
daCompanies = New System.Data.OleDb.OleDbDataAdapter
updcomCompanies = New System.Data.OleDb.OleDbCommand
daCompanies.UpdateCommand = updcomCompanies
updcomCompanies.Connection = dbConContacts
Table Mapping Code
daCompanies.TableMappings.AddRange(New System.Data.Common.DataTableMapping()
{New System.Data.Common.DataTableMapping("Table", "tblClients", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("Account_Reference_No",
"Account_Reference_No"), New System.Data.Common.DataColumnMapping("Address",
"Address"), New System.Data.Common.DataColumnMapping("Area", "Area"), New
System.Data.Common.DataColumnMapping("Blocked", "Blocked"), New
System.Data.Common.DataColumnMapping("Blocked_Date", "Blocked_Date"), New
System.Data.Common.DataColumnMapping("Blocked_Department",
"Blocked_Department"), New
System.Data.Common.DataColumnMapping("Blocked_Reason", "Blocked_Reason"),
New System.Data.Common.DataColumnMapping("Client_Criteria",
"Client_Criteria"), New System.Data.Common.DataColumnMapping("Client_Notes",
"Client_Notes"), New
System.Data.Common.DataColumnMapping("Client_Specifics",
"Client_Specifics"), New System.Data.Common.DataColumnMapping("Company",
"Company"), New
System.Data.Common.DataColumnMapping("Company_Registration_No",
"Company_Registration_No"), New
System.Data.Common.DataColumnMapping("Country", "Country"), New
System.Data.Common.DataColumnMapping("County", "County"), New
System.Data.Common.DataColumnMapping("Create_Date", "Create_Date"), New
System.Data.Common.DataColumnMapping("Create_User", "Create_User"), New
System.Data.Common.DataColumnMapping("Created_By", "Created_By"), New
System.Data.Common.DataColumnMapping("Date_Created", "Date_Created"), New
System.Data.Common.DataColumnMapping("Date_Modified", "Date_Modified"), New
System.Data.Common.DataColumnMapping("DB_Update_Date", "DB_Update_Date"),
New System.Data.Common.DataColumnMapping("Directions", "Directions"), New
System.Data.Common.DataColumnMapping("Elite_Client", "Elite_Client"), New
System.Data.Common.DataColumnMapping("EMail", "EMail"), New
System.Data.Common.DataColumnMapping("Event_Notes", "Event_Notes"), New
System.Data.Common.DataColumnMapping("Fax", "Fax"), New
System.Data.Common.DataColumnMapping("Fax_Masked", "Fax_Masked"), New
System.Data.Common.DataColumnMapping("Financial_Comments",
"Financial_Comments"), New System.Data.Common.DataColumnMapping("Group",
"Group"), New System.Data.Common.DataColumnMapping("ID", "ID"), New
System.Data.Common.DataColumnMapping("Image_List", "Image_List"), New
System.Data.Common.DataColumnMapping("Invoice_Address", "Invoice_Address"),
New System.Data.Common.DataColumnMapping("Invoice_Company",
"Invoice_Company"), New
System.Data.Common.DataColumnMapping("Invoice_Country", "Invoice_Country"),
New System.Data.Common.DataColumnMapping("Invoice_County",
"Invoice_County"), New
System.Data.Common.DataColumnMapping("Invoice_Postcode",
"Invoice_Postcode"), New System.Data.Common.DataColumnMapping("Map", "Map"),
New System.Data.Common.DataColumnMapping("Modified_By", "Modified_By"), New
System.Data.Common.DataColumnMapping("Modify_Date", "Modify_Date"), New
System.Data.Common.DataColumnMapping("Modify_User", "Modify_User"), New
System.Data.Common.DataColumnMapping("Nominal", "Nominal"), New
System.Data.Common.DataColumnMapping("Parent", "Parent"), New
System.Data.Common.DataColumnMapping("Payment_Terms", "Payment_Terms"), New
System.Data.Common.DataColumnMapping("People_Client", "People_Client"), New
System.Data.Common.DataColumnMapping("Postcode", "Postcode"), New
System.Data.Common.DataColumnMapping("Site_Update_Date",
"Site_Update_Date"), New System.Data.Common.DataColumnMapping("Sort",
"Sort"), New System.Data.Common.DataColumnMapping("Source", "Source"), New
System.Data.Common.DataColumnMapping("Source_Date", "Source_Date"), New
System.Data.Common.DataColumnMapping("Special_Requirments",
"Special_Requirments"), New
System.Data.Common.DataColumnMapping("Staff_Grade_1", "Staff_Grade_1"), New
System.Data.Common.DataColumnMapping("Staff_Grade_2", "Staff_Grade_2"), New
System.Data.Common.DataColumnMapping("Staff_Grade_3", "Staff_Grade_3"), New
System.Data.Common.DataColumnMapping("Staff_Grade_4", "Staff_Grade_4"), New
System.Data.Common.DataColumnMapping("StaffCategoryReport",
"StaffCategoryReport"), New
System.Data.Common.DataColumnMapping("Statement_Address",
"Statement_Address"), New System.Data.Common.DataColumnMapping("Status",
"Status"), New System.Data.Common.DataColumnMapping("Status_Date",
"Status_Date"), New System.Data.Common.DataColumnMapping("Supplier_No",
"Supplier_No"), New System.Data.Common.DataColumnMapping("Tel", "Tel"), New
System.Data.Common.DataColumnMapping("Tel_Masked", "Tel_Masked"), New
System.Data.Common.DataColumnMapping("TempID", "TempID"), New
System.Data.Common.DataColumnMapping("Type", "Type"), New
System.Data.Common.DataColumnMapping("Uniforms_Needed", "Uniforms_Needed"),
New System.Data.Common.DataColumnMapping("Uniforms_Needed_Elite",
"Uniforms_Needed_Elite"), New
System.Data.Common.DataColumnMapping("Uniforms_Needed_Tech",
"Uniforms_Needed_Tech"), New
System.Data.Common.DataColumnMapping("Venture_Debtor_No",
"Venture_Debtor_No"), New System.Data.Common.DataColumnMapping("Web",
"Web"), New System.Data.Common.DataColumnMapping("Web_Enabled",
"Web_Enabled"), New System.Data.Common.DataColumnMapping("Web_ID",
"Web_ID")})})
Update Command Text
updcomCompanies.CommandText = "UPDATE tblClients SET Account_Reference_No =
?, Address = ?, Area = ?, Blocked = " & _
"?, Blocked_Date = ?, Blocked_Department = ?, Blocked_Reason = ?,
Client_Criteria" & _
" = ?, Client_Notes = ?, Client_Specifics = ?, Company = ?,
Company_Registration_" & _
"No = ?, Country = ?, County = ?, Create_Date = ?, Create_User = ?,
Created_By = " & _
"?, Date_Created = ?, Date_Modified = ?, DB_Update_Date = ?, Directions = ?,
Elit" & _
"e_Client = ?, EMail = ?, Event_Notes = ?, Fax = ?, Fax_Masked = ?,
Financial_Com" & _
"ments = ?, [Group] = ?, Image_List = ?, Invoice_Address = ?,
Invoice_Company = ?" & _
", Invoice_Country = ?, Invoice_County = ?, Invoice_Postcode = ?, Map = ?,
Modifi" & _
"ed_By = ?, Modify_Date = ?, Modify_User = ?, Nominal = ?, Parent = ?,
Payment_Te" & _
"rms = ?, People_Client = ?, Postcode = ?, Site_Update_Date = ?, Sort = ?,
Source" & _
" = ?, Source_Date = ?, Special_Requirments = ?, Staff_Grade_1 = ?,
Staff_Grade_2" & _
" = ?, Staff_Grade_3 = ?, Staff_Grade_4 = ?, StaffCategoryReport = ?,
Statement_A" & _
"ddress = ?, Status = ?, Status_Date = ?, Supplier_No = ?, Tel = ?,
Tel_Masked = " & _
"?, TempID = ?, Type = ?, Uniforms_Needed = ?, Uniforms_Needed_Elite = ?,
Uniform" & _
"s_Needed_Tech = ?, Venture_Debtor_No = ?, Web = ?, Web_Enabled = ?, Web_ID
= ? W" & _
"HERE (ID = ?) AND (Account_Reference_No = ? OR ? IS NULL AND
Account_Reference_N" & _
"o IS NULL) AND (Address = ? OR ? IS NULL AND Address IS NULL) AND (Area = ?
OR ?" & _
" IS NULL AND Area IS NULL) AND (Blocked = ?) AND (Blocked_Date = ? OR ? IS
NULL " & _
"AND Blocked_Date IS NULL) AND (Blocked_Department = ? OR ? IS NULL AND
Blocked_D" & _
"epartment IS NULL) AND (Company = ? OR ? IS NULL AND Company IS NULL) AND
(Compa" & _
"ny_Registration_No = ? OR ? IS NULL AND Company_Registration_No IS NULL)
AND (Co" & _
"untry = ? OR ? IS NULL AND Country IS NULL) AND (County = ? OR ? IS NULL
AND Cou" & _
"nty IS NULL) AND (Create_Date = ? OR ? IS NULL AND Create_Date IS NULL) AND
(Cre" & _
"ate_User = ? OR ? IS NULL AND Create_User IS NULL) AND (Created_By = ? OR ?
IS N" & _
"ULL AND Created_By IS NULL) AND (DB_Update_Date = ? OR ? IS NULL AND
DB_Update_D" & _
"ate IS NULL) AND (Date_Created = ? OR ? IS NULL AND Date_Created IS NULL)
AND (D" & _
"ate_Modified = ? OR ? IS NULL AND Date_Modified IS NULL) AND (EMail = ? OR
? IS " & _
"NULL AND EMail IS NULL) AND (Elite_Client = ?) AND (Fax = ? OR ? IS NULL
AND Fax" & _
" IS NULL) AND (Fax_Masked = ? OR ? IS NULL AND Fax_Masked IS NULL) AND
([Group] " & _
"= ? OR ? IS NULL AND [Group] IS NULL) AND (Invoice_Address = ? OR ? IS NULL
AND " & _
"Invoice_Address IS NULL) AND (Invoice_Company = ? OR ? IS NULL AND
Invoice_Compa" & _
"ny IS NULL) AND (Invoice_Country = ? OR ? IS NULL AND Invoice_Country IS
NULL) A" & _
"ND (Invoice_County = ? OR ? IS NULL AND Invoice_County IS NULL) AND
(Invoice_Pos" & _
"tcode = ? OR ? IS NULL AND Invoice_Postcode IS NULL) AND (Modified_By = ?
OR ? I" & _
"S NULL AND Modified_By IS NULL) AND (Modify_Date = ? OR ? IS NULL AND
Modify_Dat" & _
"e IS NULL) AND (Modify_User = ? OR ? IS NULL AND Modify_User IS NULL) AND
(Nomin" & _
"al = ? OR ? IS NULL AND Nominal IS NULL) AND (Parent = ? OR ? IS NULL AND
Parent" & _
" IS NULL) AND (Payment_Terms = ? OR ? IS NULL AND Payment_Terms IS NULL)
AND (Pe" & _
"ople_Client = ?) AND (Postcode = ? OR ? IS NULL AND Postcode IS NULL) AND
(Site_" & _
"Update_Date = ? OR ? IS NULL AND Site_Update_Date IS NULL) AND (Sort = ? OR
? IS" & _
" NULL AND Sort IS NULL) AND (Source = ? OR ? IS NULL AND Source IS NULL)
AND (So" & _
"urce_Date = ? OR ? IS NULL AND Source_Date IS NULL) AND
(StaffCategoryReport = ?" & _
") AND (Staff_Grade_1 = ?) AND (Staff_Grade_2 = ?) AND (Staff_Grade_3 = ?)
AND (S" & _
"taff_Grade_4 = ?) AND (Statement_Address = ? OR ? IS NULL AND
Statement_Address " & _
"IS NULL) AND (Status = ? OR ? IS NULL AND Status IS NULL) AND (Status_Date
= ? O" & _
"R ? IS NULL AND Status_Date IS NULL) AND (Supplier_No = ? OR ? IS NULL AND
Suppl" & _
"ier_No IS NULL) AND (Tel = ? OR ? IS NULL AND Tel IS NULL) AND (Tel_Masked
= ? O" & _
"R ? IS NULL AND Tel_Masked IS NULL) AND (TempID = ? OR ? IS NULL AND TempID
IS N" & _
"ULL) AND (Type = ? OR ? IS NULL AND Type IS NULL) AND (Uniforms_Needed = ?
OR ? " & _
"IS NULL AND Uniforms_Needed IS NULL) AND (Uniforms_Needed_Elite = ? OR ? IS
NULL" & _
" AND Uniforms_Needed_Elite IS NULL) AND (Uniforms_Needed_Tech = ? OR ? IS
NULL A" & _
"ND Uniforms_Needed_Tech IS NULL) AND (Venture_Debtor_No = ? OR ? IS NULL
AND Ven" & _
"ture_Debtor_No IS NULL) AND (Web = ? OR ? IS NULL AND Web IS NULL) AND
(Web_Enab" & _
"led = ?) AND (Web_ID = ? OR ? IS NULL AND Web_ID IS NULL)"
Update Command Parameters
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Account_Reference_No",
System.Data.OleDb.OleDbType.VarWChar, 50, "Account_Reference_No"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address",
System.Data.OleDb.OleDbType.VarWChar, 255, "Address"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Area",
System.Data.OleDb.OleDbType.VarWChar, 50, "Area"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Blocked",
System.Data.OleDb.OleDbType.Boolean, 2, "Blocked"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Blocked_Date",
System.Data.OleDb.OleDbType.DBDate, 0, "Blocked_Date"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Blocked_Department",
System.Data.OleDb.OleDbType.VarWChar, 50, "Blocked_Department"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Blocked_Reason",
System.Data.OleDb.OleDbType.VarWChar, 0, "Blocked_Reason"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Client_Criteria",
System.Data.OleDb.OleDbType.VarWChar, 0, "Client_Criteria"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Client_Notes",
System.Data.OleDb.OleDbType.VarWChar, 0, "Client_Notes"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Client_Specifics",
System.Data.OleDb.OleDbType.VarWChar, 0, "Client_Specifics"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Company",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Company_Registration_No",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company_Registration_No"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Country",
System.Data.OleDb.OleDbType.VarWChar, 50, "Country"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("County",
System.Data.OleDb.OleDbType.VarWChar, 50, "County"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Create_Date",
System.Data.OleDb.OleDbType.DBDate, 0, "Create_Date"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Create_User",
System.Data.OleDb.OleDbType.VarWChar, 50, "Create_User"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Created_By",
System.Data.OleDb.OleDbType.VarWChar, 50, "Created_By"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Date_Created",
System.Data.OleDb.OleDbType.DBDate, 0, "Date_Created"))
Me.updcomCompanies.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Date_Modified",
System.Data.OleDb.OleDbType.DBDate, 0, "Date_Modified"))
Me.updcomCompanies.Parameters.Add(New