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

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)
Who is Participating?
RonaldBiemansConnect With a Mentor Commented:

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.
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
jxhardingAuthor Commented:
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?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

jxhardingAuthor Commented:
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?
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
jxhardingAuthor Commented:
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?
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)
jxhardingAuthor Commented:
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?
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
jxhardingAuthor Commented:
trying it now,  i think i got it set up,  danke!
Danke! is that south african ? ;-)
jxhardingAuthor Commented:
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  :)
jxhardingAuthor Commented:
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.
jxhardingAuthor Commented:
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?

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.

jxhardingAuthor Commented:
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! :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.