Currency Violation. Update command affect 0 records

[Geraldine Hobley]

Hello I'm getting the above mentioned error in my applicatio

I have a datagrid bound to a datasource like s

MyDatagrid.DataSource = Mydataset.Tables(Order) - this all works fine

However I have another field on the form which is a richtextbox and is bound to another field in the same datasourc
like s

Me.RtxtDialog.DataBindings.Add("text", MyDataset.Tables.Item("Orders"), "MyRichTextField") - this field accepts nulls in the databas

If you just want to add values to the grid and amend the grid it works fine however when you add new records to th
grid and associated values in the rtxDialog text field we get the error

Currency Violation. Update command affect 0 records - when you click on the next row

My Datagrid Currentcellchanged event is as follow

Private Sub MyDataGrid_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyDataGrid.CurrentCellChange
Dim IntRowNum As Integer = MyDataGrid.CurrentCell.RowNumbe
Dim MyGridfunctions As New ProjectNameSpace.Gridfunction
Dim IntNumRows As Integer = MyGridfunctions.NumRowsInGrid(MyDataGrid
Dim MyDataAccess As New DataAccess.DataAcces

If IntRowNum <= IntNumRows The
Call Updatedialogue(StrTypeOfdialogue
ElseIf IntRowNum = IntNumRows Then 'adding a new row
Call Updatedialogue(StrTypeOfdialogue
End I

End Su

Private Sub Updatedialogue(ByVal strtable As String
Dim MyDataAccess As New DataAccess.DataAcces

DirectCast(BindingContext(mydataset.Tables.Item(Or ders)), CurrencyManager).EndCurrentEdit(
If MyDataSet.HasChanges The
Call MyDataAccess.UpdateDataSet(MyDataset, MysqldataAdapter, Orders

End I

End Su

'THIS IS WHERE THE ERROR OCCUR

Public Sub UpdateDataSet(ByRef MyDataset As DataSet, ByVal Mysqldataadapter As SqlDataAdapter, ByVal strTable As String

Dim objCommandBuilder As New SqlCommandBuilder(Mysqldataadapter
Tr
Mysqldataadapter.Update(MyDataset, strTable
Catch e As Exceptio
MsgBox(e.Message
End Tr
End Su

Any ideas how to avoid this error, what I'm doing wrong or at least how I can see the actual update command that is been performed, including the values of the parameters. I've tried myadapter.updatecommand.parameters.item(0). etc and is says that the parameters have a value of nothing on both the successfull and non successfull updates

The error only occurs when adding a new row and then adding something in the non-datagrid field rtxtDialog and then clicking on the next row in the grid

Any help would be greatly appreciate

Ger

[William Ryan eMVP]

CommandBuilders have terribly functionality in regard to concurrency, so you
may want to just use an adapter and either use the configuration wizard or
roll your own logic. Bill Vaughn has a great discussion at www.betav.com ->
Articles -> MSDN weaning developer from the commandbuilder.

As far as your problem... Calling update on cellchanged is probably
overkill. You may want to try .EndCurrentEdit. Nothing about a new row
should cause this inherently, so you may also want to check .GetChanges and
see what those values are.. that will give you some insight in tracking down
the exception. Moreover, since it's concurrency, you know it's something
has perceived to have changed, so the answer will be in looking at the
proposed vs original values.

HTH,

Bill
"Geraldine Hobley" <an*******@discussions.microsoft.com> wrote in message
news:48**********************************@microsof t.com...

Hello I'm getting the above mentioned error in my application

I have a datagrid bound to a datasource like so

MyDatagrid.DataSource = Mydataset.Tables(Order) - this all works fine
However I have another field on the form which is a richtextbox and is bound to another field in the same datasource like so

Me.RtxtDialog.DataBindings.Add("text", MyDataset.Tables.Item("Orders"), "MyRichTextField") - this field accepts nulls in the database
If you just want to add values to the grid and amend the grid it works fine however when you add new records to the grid and associated values in the rtxDialog text field we get the error

Currency Violation. Update command affect 0 records - when you click on the next row.
My Datagrid Currentcellchanged event is as follows

Private Sub MyDataGrid_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyDataGrid.CurrentCellChanged Dim IntRowNum As Integer = MyDataGrid.CurrentCell.RowNumber
Dim MyGridfunctions As New ProjectNameSpace.Gridfunctions
Dim IntNumRows As Integer = MyGridfunctions.NumRowsInGrid(MyDataGrid) Dim MyDataAccess As New DataAccess.DataAccess

If IntRowNum <= IntNumRows Then
Call Updatedialogue(StrTypeOfdialogue)
ElseIf IntRowNum = IntNumRows Then 'adding a new row
Call Updatedialogue(StrTypeOfdialogue)
End If

End Sub

Private Sub Updatedialogue(ByVal strtable As String)
Dim MyDataAccess As New DataAccess.DataAccess

DirectCast(BindingContext(mydataset.Tables.Item(Or ders)), CurrencyManager).EndCurrentEdit() If MyDataSet.HasChanges Then
Call MyDataAccess.UpdateDataSet(MyDataset, MysqldataAdapter, Orders)
End If

End Sub

'THIS IS WHERE THE ERROR OCCURS

Public Sub UpdateDataSet(ByRef MyDataset As DataSet, ByVal Mysqldataadapter As SqlDataAdapter, ByVal strTable As String)
Dim objCommandBuilder As New SqlCommandBuilder(Mysqldataadapter)
Try
Mysqldataadapter.Update(MyDataset, strTable)
Catch e As Exception
MsgBox(e.Message)
End Try
End Sub
Any ideas how to avoid this error, what I'm doing wrong or at least how I can see the actual update command that is been performed, including the
values of the parameters. I've tried
myadapter.updatecommand.parameters.item(0). etc and is says that the
parameters have a value of nothing on both the successfull and non
successfull updates.
The error only occurs when adding a new row and then adding something in the non-datagrid field rtxtDialog and then clicking on the next row in the
grid.
Any help would be greatly appreciated

Geri

[Cor Ligthert]

Hi Geraldine,

I do not see the solution direct, however what you can try, what maybe
resolves your problem, is use a table style in your datagrid.

I assume that than that binded column from the richtextbox is not anymore
affected by the cell changes in the datagrid.

Another way can be to do a fill again after the update, however that can be
ofcourse be more time expensive.

However just some guesses

Cor

[CJ Taylor]

Check the sql code.. if you use optimistic concurrency it actually checks
EVERY field within your SQL table to see if ANY values have changed, if so,
then it won't find the corresponding row to update (Because it belives it
has changed, therefore it doesn't want to erase the work someone else may
have done).

At which point you should check if the database been changed?

The other thing is, does this happen EVERY time? or does it work right the
first time and die every time after that. Which is what I'm assuming right
now because on your update, you need to call acceptchanges...

peace

"Geraldine Hobley" <an*******@discussions.microsoft.com> wrote in message
news:48**********************************@microsof t.com...

Hello I'm getting the above mentioned error in my application

I have a datagrid bound to a datasource like so

MyDatagrid.DataSource = Mydataset.Tables(Order) - this all works fine
However I have another field on the form which is a richtextbox and is bound to another field in the same datasource like so

Me.RtxtDialog.DataBindings.Add("text", MyDataset.Tables.Item("Orders"), "MyRichTextField") - this field accepts nulls in the database
If you just want to add values to the grid and amend the grid it works fine however when you add new records to the grid and associated values in the rtxDialog text field we get the error

Currency Violation. Update command affect 0 records - when you click on the next row.
My Datagrid Currentcellchanged event is as follows

Private Sub MyDataGrid_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyDataGrid.CurrentCellChanged Dim IntRowNum As Integer = MyDataGrid.CurrentCell.RowNumber
Dim MyGridfunctions As New ProjectNameSpace.Gridfunctions
Dim IntNumRows As Integer = MyGridfunctions.NumRowsInGrid(MyDataGrid) Dim MyDataAccess As New DataAccess.DataAccess

If IntRowNum <= IntNumRows Then
Call Updatedialogue(StrTypeOfdialogue)
ElseIf IntRowNum = IntNumRows Then 'adding a new row
Call Updatedialogue(StrTypeOfdialogue)
End If

End Sub

Private Sub Updatedialogue(ByVal strtable As String)
Dim MyDataAccess As New DataAccess.DataAccess

DirectCast(BindingContext(mydataset.Tables.Item(Or ders)), CurrencyManager).EndCurrentEdit() If MyDataSet.HasChanges Then
Call MyDataAccess.UpdateDataSet(MyDataset, MysqldataAdapter, Orders)
End If

End Sub

'THIS IS WHERE THE ERROR OCCURS

Public Sub UpdateDataSet(ByRef MyDataset As DataSet, ByVal Mysqldataadapter As SqlDataAdapter, ByVal strTable As String)
Dim objCommandBuilder As New SqlCommandBuilder(Mysqldataadapter)
Try
Mysqldataadapter.Update(MyDataset, strTable)
Catch e As Exception
MsgBox(e.Message)
End Try
End Sub
Any ideas how to avoid this error, what I'm doing wrong or at least how I can see the actual update command that is been performed, including the
values of the parameters. I've tried
myadapter.updatecommand.parameters.item(0). etc and is says that the
parameters have a value of nothing on both the successfull and non
successfull updates.
The error only occurs when adding a new row and then adding something in the non-datagrid field rtxtDialog and then clicking on the next row in the
grid.
Any help would be greatly appreciated

Geri

[Cor Ligthert]

Hi CJ,

The other thing is, does this happen EVERY time? or does it work right the first time and die every time after that. Which is what I'm assuming right now because on your update, you need to call acceptchanges...

It is not true, check for that these pages, know that this only as far I
checked with an complete dataset not with something as dataset.getchanges

http://msdn.microsoft.com/library/de...pdateTopic.asp

This one has not to do with that, however also intresting about the refresh

http://msdn.microsoft.com/library/de...terDataSet.asp

Cor

[CJ Taylor]

I can't agree with the AcceptChanges being called at the end. I do that
based of experience.

When I run a datarow through an adapter, the Rowstate property always stays
as Modified/Inserted/Deleted...

Perhaps I've been doing something wrong all these years...Or perhaps this is
one of those *hidden* things of MS...

As far as getchanges, that is usually more of a hassel than I care to deal
with. Now for the Web. Why? Creates a copy of the dataset, so then you
still have to go backt o your original dataset and merge in all the changes.
Well, with autoincrementing field IDs, this because a REAL pain especially
when your SQL server assigns a different row id than what ADO.NET did. then
you have to manually do it...

That didnt make much sense... but I cannot believe that acceptchanges is
called, I just ran it.. rowstate stayed modified...

horsesh**

=)
"Cor Ligthert" <no**********@planet.nl> wrote in message
news:uj**************@TK2MSFTNGP10.phx.gbl...

Hi CJ,

The other thing is, does this happen EVERY time? or does it work right the

first time and die every time after that. Which is what I'm assuming

right

now because on your update, you need to call acceptchanges...

It is not true, check for that these pages, know that this only as far I
checked with an complete dataset not with something as dataset.getchanges

http://msdn.microsoft.com/library/de...pdateTopic.asp
This one has not to do with that, however also intresting about the refresh
http://msdn.microsoft.com/library/de...terDataSet.asp
Cor

[Cor Ligthert]

Hi CJ,

I have the same problem with it however (or I become crazy), with a full
original dataset it works.

But not when I make something as this sentence
If ds.haschanges
da.update(ds.getchanges)
end if

In this case the rowstates are not updated, however I do not know if this is
a bug or that it is specially done so. That getchanges are in my opinion not
more than references to actual datarows.

I thought that the dataset merge was an approach to make a three tier
application possible, however I do still (as I understand from you the same
as you) not see how to use that on a practical and simple way.

That different row Id you can overcome with that second link to a page I did
send you, that advice is good in my opinon to do a fill direct after a
correct updated dataset. (Of course in situations where that is needed)

Just as I thought is was, however can be wrong.

Cor

[CJ Taylor]

Hey Cor,

Hi CJ,

I have the same problem with it however (or I become crazy), with a full
original dataset it works.

I noticed this as well. However, I rarely use a full dataset for updating..
But not when I make something as this sentence
If ds.haschanges
da.update(ds.getchanges)
end if

In this case the rowstates are not updated, however I do not know if this is a bug or that it is specially done so. That getchanges are in my opinion not more than references to actual datarows.

That was my confusion as well. Also GetChanges is not references (as one
would expect them to be) however a result of a DataTable.Copy or
Dataset.Copy. This is one feature I DEFNATLY do not like...
I thought that the dataset merge was an approach to make a three tier
application possible, however I do still (as I understand from you the same as you) not see how to use that on a practical and simple way.

That different row Id you can overcome with that second link to a page I did send you, that advice is good in my opinon to do a fill direct after a
correct updated dataset. (Of course in situations where that is needed)

I've never used fill that way, should try it out.
Just as I thought is was, however can be wrong.

Cor

[Cor Ligthert]

Hi CJ,

Never thought of this

Dataset.GetChanges
Gets a copy of the Dataset containing all changes made to it since it was
last loaded, or since AcceptChanges was called.

This makes it all clear for me

Thanks for pointing me on this.

Cor

[mklapp]

I just resolved a similar situation and share it now, late though it may be.

Simply put, the SQL query was not checking enough fields to guarantee a
unique record. I modified the query (built by the data adapter config wiz)
to use all the fields needed to make a unique reference and the problem went
away.

"Geraldine Hobley" wrote:

Hello I'm getting the above mentioned error in my application

I have a datagrid bound to a datasource like so

MyDatagrid.DataSource = Mydataset.Tables(Order) - this all works fine

However I have another field on the form which is a richtextbox and is bound to another field in the same datasource
like so

Me.RtxtDialog.DataBindings.Add("text", MyDataset.Tables.Item("Orders"), "MyRichTextField") - this field accepts nulls in the database

If you just want to add values to the grid and amend the grid it works fine however when you add new records to the
grid and associated values in the rtxDialog text field we get the error

Currency Violation. Update command affect 0 records - when you click on the next row.

My Datagrid Currentcellchanged event is as follows

Private Sub MyDataGrid_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyDataGrid.CurrentCellChanged
Dim IntRowNum As Integer = MyDataGrid.CurrentCell.RowNumber
Dim MyGridfunctions As New ProjectNameSpace.Gridfunctions
Dim IntNumRows As Integer = MyGridfunctions.NumRowsInGrid(MyDataGrid)
Dim MyDataAccess As New DataAccess.DataAccess

If IntRowNum <= IntNumRows Then
Call Updatedialogue(StrTypeOfdialogue)
ElseIf IntRowNum = IntNumRows Then 'adding a new row
Call Updatedialogue(StrTypeOfdialogue)
End If

End Sub

Private Sub Updatedialogue(ByVal strtable As String)
Dim MyDataAccess As New DataAccess.DataAccess

DirectCast(BindingContext(mydataset.Tables.Item(Or ders)), CurrencyManager).EndCurrentEdit()
If MyDataSet.HasChanges Then
Call MyDataAccess.UpdateDataSet(MyDataset, MysqldataAdapter, Orders)

End If

End Sub

'THIS IS WHERE THE ERROR OCCURS

Public Sub UpdateDataSet(ByRef MyDataset As DataSet, ByVal Mysqldataadapter As SqlDataAdapter, ByVal strTable As String)

Dim objCommandBuilder As New SqlCommandBuilder(Mysqldataadapter)
Try
Mysqldataadapter.Update(MyDataset, strTable)
Catch e As Exception
MsgBox(e.Message)
End Try
End Sub
Any ideas how to avoid this error, what I'm doing wrong or at least how I can see the actual update command that is been performed, including the values of the parameters. I've tried myadapter.updatecommand.parameters.item(0). etc and is says that the parameters have a value of nothing on both the successfull and non successfull updates.

The error only occurs when adding a new row and then adding something in the non-datagrid field rtxtDialog and then clicking on the next row in the grid.

Any help would be greatly appreciated

Geri