Concurrency Violation

[elziko]

I have a DataTable which I did use as the source for a DataGrid, teh data
table was popeulated form my database and when I made changes in the grid I
used the same DataAdapter to chnage the values in the database.

However, I now have the need (on a column by column basis) to show the
values in the grid as a multiple of what is in the underlying datatable.
Then if the user edits the grid I must then devide by the same value before
updating the database again.

My first effort for this was to have a DataTable representing the data in
the database and another DataTable representing the data in the DataGrid.
The before displaying any data in the grid or updating the database I would
execute a function that copies (using ImportRow) each row from the display
DataTable to the storage DataTable converting values as necessary as we go
along. This seemed OK and each row kept the expected RowState.

However, after I copy from the display DataTable to the storage DataTable
and use the DataAdapter.Update method on the storage DataTable to put the
changes back into the database I get the following error:

concurrency violation: the updatecommand affected 0 records

so it looks like I cannot accomplish what I am trying to do with two
DataTables.. or can I? Or is there an easier way to do this? I know it looks
stramge but I do have a valid requirement to do this.

TIA

 

[Ken Tucker [MVP]]

Hi,

Take a look at the datatables getchages method.

http://msdn.microsoft.com/library/d...rfsystemdatadatatableclassgetchangestopic.asp

Ken
------------------------
I have a DataTable which I did use as the source for a DataGrid, teh data
table was popeulated form my database and when I made changes in the grid I
used the same DataAdapter to chnage the values in the database.

However, I now have the need (on a column by column basis) to show the
values in the grid as a multiple of what is in the underlying datatable.
Then if the user edits the grid I must then devide by the same value before
updating the database again.

My first effort for this was to have a DataTable representing the data in
the database and another DataTable representing the data in the DataGrid.
The before displaying any data in the grid or updating the database I would
execute a function that copies (using ImportRow) each row from the display
DataTable to the storage DataTable converting values as necessary as we go
along. This seemed OK and each row kept the expected RowState.

However, after I copy from the display DataTable to the storage DataTable
and use the DataAdapter.Update method on the storage DataTable to put the
changes back into the database I get the following error:

concurrency violation: the updatecommand affected 0 records

so it looks like I cannot accomplish what I am trying to do with two
DataTables.. or can I? Or is there an easier way to do this? I know it looks
stramge but I do have a valid requirement to do this.

TIA

 

[elziko]

Take a look at the datatables getchages method.

Thanks for your reply but I don't understand how this helps me. Looking at
the RowStates there are a couple of rows that are modified and this would be
given in the DataTable that is returned from GetChanges. The problem is that
when the DataAdapter tries to update these changes to the database I get a
concurrency exception.

I think the reason for this is that the DataTable I am using for the
DataAdapter.Update isnt the same as the one I used for the DataAdapter.Fill.
It is actually a new DataTable containing imported rows from teh original.
Here is some code that describes it.

Firstly when getting data from the database:

da.Fill(dtStorage)
dtDisplay = New DataTable
intRowNumber = 0
For Each dr As DataRow In dtStorage.Rows
dtDisplay.ImportRow(dr)
dtDisplay.Rows(intRowNumber).Item(0) =
Convert.ToSingle(dtDisplay.Rows(intRowNumber).Item(0)) * 10
intRowNumber += 1
Next

That works fine but then when I try and update the database with any values
from the grid:

dtStorage = New DataTable
intRowNumber = 0
For Each dr As DataRow In dtDisplay.Rows
dtStorage.ImportRow(dr)
dtStorage.Rows(intRowNumber).Item(0) =
Convert.ToSingle(dtStorage.Rows(intRowNumber).Item(0)) / 10
intRowNumber += 1
Next
da.Update(dtStorage)

I get the concurremcy exception. As you see in my example the value for the
first column is always displayed as ten time slarger than whats in the
database. This is the value that the user sees and edits but before teh
database is updated the value is decreased by a factor of ten for storage.
Is there any way around my problem?

 

[Ken Tucker [MVP]]

Hi,

Not sure why you need 2 tables but. You forgot the begin and
end edit before you chaged the values in the loop.

dtStorage.Rows(intRowNumber).Item(0).beginedit
dtStorage.Rows(intRowNumber).Item(0) =
Convert.ToSingle(dtStorage.Rows(intRowNumber).Item(0)) / 10
dtStorage.Rows(intRowNumber).Item(0).endedit

http://msdn.microsoft.com/library/d...frlrfsystemdatadatarowclassbeginedittopic.asp

Ken
---------------------

Take a look at the datatables getchages method.

Thanks for your reply but I don't understand how this helps me. Looking at
the RowStates there are a couple of rows that are modified and this would be
given in the DataTable that is returned from GetChanges. The problem is that
when the DataAdapter tries to update these changes to the database I get a
concurrency exception.

I think the reason for this is that the DataTable I am using for the
DataAdapter.Update isnt the same as the one I used for the DataAdapter.Fill.
It is actually a new DataTable containing imported rows from teh original.
Here is some code that describes it.

Firstly when getting data from the database:

da.Fill(dtStorage)
dtDisplay = New DataTable
intRowNumber = 0
For Each dr As DataRow In dtStorage.Rows
dtDisplay.ImportRow(dr)
dtDisplay.Rows(intRowNumber).Item(0) =
Convert.ToSingle(dtDisplay.Rows(intRowNumber).Item(0)) * 10
intRowNumber += 1
Next

That works fine but then when I try and update the database with any values
from the grid:

dtStorage = New DataTable
intRowNumber = 0
For Each dr As DataRow In dtDisplay.Rows
dtStorage.ImportRow(dr)
dtStorage.Rows(intRowNumber).Item(0) =
Convert.ToSingle(dtStorage.Rows(intRowNumber).Item(0)) / 10
intRowNumber += 1
Next
da.Update(dtStorage)

I get the concurremcy exception. As you see in my example the value for the
first column is always displayed as ten time slarger than whats in the
database. This is the value that the user sees and edits but before teh
database is updated the value is decreased by a factor of ten for storage.
Is there any way around my problem?

 

[Cor Ligthert]

Elziko,

I think that you will have more benefit by using extra columns in your
datatable.
You can add as much columns as you wish and even have expressions for that,
only the correct named columns will be updated.

Just my thought,

Cor

 

[RPAHelp]

Try this

[font='Calibri','sans-serif']I just fixed my problem. I have a Database setup very similar. Basically an access database with a connection to Visual Studio 2005 project using C#. After analyzing my project I came to the conclusion the problem was with the communication between my primary key, ID row. So I changed my primary key to my first active row and deleted my ID (auto count). Hope this helps.

[/font]