cant get dataview to update back to dataset: when i delete a row in dataview i get Conncurrency Violation: sample code supplied

Posted on 2004-08-26
Last Modified: 2010-04-23

hi, i have a program which loads a job. this job has items called (phsyical) keys. for e.g.
job11 has 2 keys, which i entered previously.
the goal of the program, is to load a job, and then the user can add or delete keys from the job.

i have a table "KeyData"in my access database with for e.g. 2 fields:
field1      field2      

i have a dataadapter called daKeyData, and this dataadapter has the commands to update, delete, select and insert into

"Keydata" table.

i also generated a dataset "dsKeyData" from the dataadapter (having 2 fields)
now i added another field tot the bottom of the dataset because i need to display another descriptive item.
this other field is from the "MasterKeyData" table.

now the user selects a job and i use a typed dataadapter to fill the dataset:
 Dim da1 As New OleDbDataAdapter("SELECT KeyData.FIELD1, MasterKeyData.Description, KeyData.FIELD2 FROM KeyData INNER JOIN

MasterKeyData ON KeyData.KeyCodeID = MasterKeyData.KeyCodeID WHERE (Keydata.JOBID = 11)", myConnection)
 da1.Fill(DsKeyData1, "KeyData")

now the dataset is filled and i generate a dataview and connect it to a datagrid
  dv = New DataView(DsKeyData1.Tables("KeyData"))
  myDataGrid.DataSource = dv

now the data is displayed on the datagrid:
      FIELD1      FIELD2      Field3
Row1      Data1a      Data1b      Data1c
Row2      Data2a      Data2b      Data2c

now the user has the option to add and delete keys from the datagrid.
when the user adds a key:

  'allow for the dataview to add one record
   dv.AllowNew = True
   Dim NewKey As DataRowView = dv.AddNew
   dv.AllowNew = False

  'set the details of the new key, i know the jobid
   NewKey("Field1") = 1
   NewKey("Field2") = "X"

No problem so far, the problem comes when i try to save:
            daKeyData.Update(DsKeyDataCN, "KeyData")

i use a try block , im just displaying the working code for it. when i add a key, and save, it runs through the
code with out errors, but it never writes away to the database

now when i delete a row and try to save, it says "Concurrency violation: 0 rows affected"

i am dead sure its not a error in the dataadapters side, it generated without any errors

i am really at a loss here, i spent at least a day on this already.
ive got the whole project with comments at:

just click add row, and check the last item in the modal form, and press return.URL: (70k)
Question by:jxharding
  • 9
  • 7
LVL 25

Expert Comment

ID: 11902102
Hi jxharding,

You are filling your grid with da1

Dim da1 As New OleDbDataAdapter("SELECT KeyData.KeyID, MasterKeyData.KeySeries, MasterKeyData.Description, KeyData.KeyQty, KeyData.JobID FROM KeyData INNER JOIN MasterKeyData ON KeyData.KeyCodeID = MasterKeyData.KeyCodeID WHERE (Keydata.JOBID = 11)", cnKeyGen)
        da1.Fill(DsKeyDataCN, "KeyData")

        'here the dataview is set to the data in dsKeyDataCN
        dv = New DataView(DsKeyDataCN.Tables("KeyData"))
        grdKeys.DataSource = dv

but you are saving your data with a different dataadapter

daKeyData.Update(DsKeyDataCN, "KeyData")

Which hasn't even been filled yet

Author Comment

ID: 11902188
hi ronaldbiemans,
it makes more sense now, i thought both dataadapters would just refer to the dataset, and the dataset would contain everything.
do i have to completely discard of the first dataadapter,da1?
will this come down to me looping each record of the dataview, and adding it to the dataset?

Author Comment

ID: 11902215
initially i had the second dataadapter setup instead of da1, and i changed the selectcommand
daKeydata.selectcommand = " SELECT KEYDATA.KEYID..."

but  would that also cause the same problem?
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 25

Expert Comment

ID: 11902435
Hi Jxharding, I don't quite understand what you mean. As far as I can see both dataadapters are quite different
da1 contains data from more than one table (keydata masterkeydata) and daKeyData just from table (keydata).
Meaning you won't be able to use da1 to update just the table "Keydata"

I would suggest you make 2 dataadapters to get the tables keydata and masterkeydata
create a relationship between the 2 and update them seperately

Author Comment

ID: 11902879
hi ronald, in the past i've only added items to my programs so it was no problem because it was just a normal
and i knew there would be no problems because i did not have to check if any changes were made to existing data
but now i have to load items, change them, and then update them back.
what i thought of doing was taking for e.g. the dataset with the 3 columns, and then just dispose of column3 because that
would leave me with Field1 and Field2, which is exactly what i need for updating the original dataset.

so what i understand now is that i'll have 1 dataset with 2 src tables, and i set the relationship, and then i fill each one.
is this right? or should i also make a third src table which i can use for the datagrid style?
LVL 25

Expert Comment

ID: 11903512
Hi jxharding,

so what i understand now is that i'll have 1 dataset with 2 src tables, and i set the relationship, and then i fill each one.
is this right? Yes it is, you can then add the extra columns you need for grid to one of the tables (using the relationship)

Author Comment

ID: 11910470
hi ronald , i definitely was not aware that i could fill one datagridstyle from 2 tables,
how about this theory, i make a third table in the dataset, containing all the values i need off the other 2 tables, i then set the datasource , and datamember in the properties?
or should i keep it 2 tables?
LVL 25

Expert Comment

ID: 11910527
Hi JxHarding, I wouldn't do that because how will you get the changes you made in the third table back to the 2 dataadapters ? (you would only be able to do that by looping through all the changed records in the third and than make the changes again in the other 2.  I'll give you an example of how I usually do it. Just give me a few minutes
LVL 25

Accepted Solution

RonaldBiemans earned 500 total points
ID: 11910627

Here I'm adding extra columns to table 'jcontactpersonenContracten' using the relationship 'Contactpersonenjcontactpersonencontracten'

        RelatieDS.Tables("jcontactpersonenContracten").Columns.Add("Voornaam", GetType(String), "parent(Contactpersonenjcontactpersonencontracten).Voornaam")
        RelatieDS.Tables("jcontactpersonenContracten").Columns.Add("Achternaam", GetType(String), "parent(Contactpersonenjcontactpersonencontracten).achternaam")
        RelatieDS.Tables("jcontactpersonenContracten").Columns.Add("Relatie", GetType(String), "parent(RelatiesjContactpersonenContracten).naam")
        RelatieDS.Tables("jcontactpersonenContracten").Columns.Add("TypeContractID", GetType(String), "parent(ContractenjContactpersonenContracten).TypeContractID")

        DVjContactPersonenContracten = RelatieDS.Tables("jContactpersonenContracten").DefaultView

before I save the records back to the database I remove the added columns and after the save I add them again

Although I usually never use a datagrid for editing records and adding, just showing. I usually have textboxes for the editing and adding records and showing the results in a grid.

Author Comment

ID: 11910817
trying it now,  i think i got it set up,  danke!
LVL 25

Expert Comment

ID: 11910909
Danke! is that south african ? ;-)

Author Comment

ID: 11910927
no ,  i did a bit of german at school  ,and i live next to Volkswagen factory, and the company i work for imports some  german electrical products  :)

Author Comment

ID: 11928888
hi ronald, is it possible to have a one-to-one relationship in a dataset, i cant get any info on it,
The idea is

KeyData               MasterKeyData

KeyCode        |----KeyCodeID
KeyCodeID----|     Description

Each KeyCode in KeyData can only have 1 keycodeID, and each KeyCodeID has only one description.

Author Comment

ID: 11929066
hi ronald, i  added a column to the dataset, and now it displays on the datagrid.
also , my datasetd now saves perfectly,i can add and delete and change , no problem. thus the last problem is this

This is how i added the extra table
 DsKeyDataCN.Tables("KeyData").Columns.Add("Description", GetType(String))
this is how you recommended it, but the last section i can't get right
 'DsKeyDataCN.Tables("KeyData").Columns.Add("Description", GetType(String), "parent(MasterKeyData).Description")
i did try to look it up, but  got to .net 24-7 and most of them said it cant be done.
also should i rather set the relationship in code (the relationship KeyData.KeyCodeID =  MasterKeyData.KeyCodeID)
because i've just run into problems so far. could i then set a 1to1 relationship?

LVL 25

Expert Comment

ID: 11940113
Hi Jxharding,


about the one to one relationship this is what MSDN has to say about it

One-to-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:

Divide a table with many columns.
Isolate part of a table for security reasons.
Store data that is short-lived and could be easily deleted by simply deleting the table.
Store information that applies only to a subset of the main table.
The primary key side of a one-to-one relationship is denoted by a key  symbol. The foreign key side is also denoted by a key  symbol.


Author Comment

ID: 11940179
brilliant, thanks!
i rather changed my database to hold the information in 1 table.
i couldnt get any info on setting the datagrid to have more than one dataset table as source, but its working nice in the new method i tried.
it only struck me yesterday, "achternaam" is dutch, not german, sorry! :)


Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with Syntax 9 37
Vb. Net application freezes 9 47
Import a txt file into a DataGridView and TextBox 20 38
Create a datatable in dynamically 1 15
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question