"Rob Dob" <robdob20012002@yahoo.com> wrote in message
news:ubpeJ5LKIHA.484@TK2MSFTNGP06.phx.gbl...

"Jim Rand" <jimrand@ix.netcom.com> wrote in message
news:uTgwKOLKIHA.5980@TK2MSFTNGP04.phx.gbl...
> I'm going to make several assumptions.
>
> 1) You use identity keys in the database.
> 2) You have figured out how to get the keys back to replace negative keys
> in
> your dataset that existed prior to update.
> 3) You are using timestamps for concurrency (that's a big assumption).
> 4) You process the dataset updates in chunks using the
> dataset.GetChanges(Deleted | Modified | Added)
> 5) You process the deletes first up the hierarchy. Then, you process the
> adds down the parent chain. Finally, you process the modifies.
>
> Let's say the user modified the parent row and added new child rows.
>
> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
> the
> added rows as you would expect. It also includes any modified parents. If
> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
> rows AND will also update the parent row if it had been modified. Fine so
> far. Here is what happens next. You do the
> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
> because
> the timestamp in the parent row has already changed.
>
> Sample SQL:
>
> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
> [LastUpdatedBy]) VALUES (@AccessFlag, @Description, @LastUpdatedBy);SELECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
>
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
> [Description] = @Description, [LastUpdated] = getutcdate(),
> [LastUpdatedBy]
> = @LastUpdatedBy WHERE (([AccessFlagLookupID] =
> @Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @Original_TS));SELECT
> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
> (AccessFlagLookupID = @AccessFlagLookupID)
>
> That "(CAST(TS AS INT) = @Original_TS));" works with the first update with
> the added rows (modified parent) but fails when it gets updated again when
> the modified rows get processed.
>
> The way I found this was through getting unexpected concurrency errors.
> Stepping through the code and monitoring changes in the database timestamp
> values spotted the problem.
>
> Nasty.
>
>

"Mike C#" <xyz@xyz.com> wrote in message
news:%23lsjlpNKIHA.3400@TK2MSFTNGP03.phx.gbl...
> Do you have more information concerning usage patterns? I.e., is there
> more than one person attempting to update/add rows simultaneously? Or is
> this all happening with only a single user? Also, what is your PK on the
> Customers table? What happens if you wrap the INSERT INTO...SELECT in a
> BEGIN TRANSACTION...COMMIT TRANSACTION?
>
> "Rob Dob" <robdob20012002@yahoo.com> wrote in message
> news:ubpeJ5LKIHA.484@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> -1- I have winform that contains some textboxes, comboboxes etc these are
> databound to a BindingSource
> -2- if the user decides to add a new record I call BindingSource.AddNew()
> -2- The BindingSource is bound to my dataset.
> -4- when the user clicks on the Save Record button my function looks like
> the following:
>
> try
> {
> this.CustomerBindingSource.EndEdit();
> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy =
> "XXX";
> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
> = DateTime.Now;
> int ncount =
> this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
> }
> catch (DBConcurrencyException oEx )
> {
> MessageBox.Show("Concurrency Erro: " + oEx.Message);
> }
> catch (System.Exception ex)
> {
> MessageBox.Show("Other Error: " + ex.Message);
> }
>
> -5- The Dataset that is bound to the BindingSource has an identity field
> which is an int and is also the primary key on the table, The insert
> command that is generated by the vs2005 designer does NOT contain the
> identy field but does a select after the insert inorder to repopulate the
> dataset with the new id as send below:
> INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@Date,
> @ContactLastName);
> SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
> = SCOPE_IDENTITY())
>
> -6- I am getting some concurrency violations with updates but I am also
> getting the odd concurrency violation with Adding new records is this
> possible? also it appeard that for some reason today the update command
> was also not updating some records down to the database, however then the
> app was restarted and for some reason it started to work..
> Any help would be greatly appreciated.
> Thanks,
>
>
> "Jim Rand" <jimrand@ix.netcom.com> wrote in message
> news:uTgwKOLKIHA.5980@TK2MSFTNGP04.phx.gbl...
>> I'm going to make several assumptions.
>>
>> 1) You use identity keys in the database.
>> 2) You have figured out how to get the keys back to replace negative keys
>> in
>> your dataset that existed prior to update.
>> 3) You are using timestamps for concurrency (that's a big assumption).
>> 4) You process the dataset updates in chunks using the
>> dataset.GetChanges(Deleted | Modified | Added)
>> 5) You process the deletes first up the hierarchy. Then, you process the
>> adds down the parent chain. Finally, you process the modifies.
>>
>> Let's say the user modified the parent row and added new child rows.
>>
>> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
>> the
>> added rows as you would expect. It also includes any modified parents.
>> If
>> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
>> rows AND will also update the parent row if it had been modified. Fine so
>> far. Here is what happens next. You do the
>> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
>> because
>> the timestamp in the parent row has already changed.
>>
>> Sample SQL:
>>
>> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
>> [LastUpdatedBy]) VALUES (@AccessFlag, @Description,
>> @LastUpdatedBy);SELECT
>> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
>> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
>>
>> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
>> [Description] = @Description, [LastUpdated] = getutcdate(),
>> [LastUpdatedBy]
>> = @LastUpdatedBy WHERE (([AccessFlagLookupID] =
>> @Original_AccessFlagLookupID) AND (CAST(TS AS INT) =
>> @Original_TS));SELECT
>> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
>> (AccessFlagLookupID = @AccessFlagLookupID)
>>
>> That "(CAST(TS AS INT) = @Original_TS));" works with the first update
>> with
>> the added rows (modified parent) but fails when it gets updated again
>> when
>> the modified rows get processed.
>>
>> The way I found this was through getting unexpected concurrency errors.
>> Stepping through the code and monitoring changes in the database
>> timestamp
>> values spotted the problem.
>>
>> Nasty.
>>
>>
>
>

"Rob Dob" <robdob20012002@yahoo.com> wrote in message
news:O0cFq5SKIHA.5328@TK2MSFTNGP05.phx.gbl...
> Thank you, I will try using the BeginTransaction .. Commit Transaction,
>
> yes this is a multi-user application, about 50+ users,
>
> Thank you, I will report back.
>
> "Mike C#" <xyz@xyz.com> wrote in message
> news:%23lsjlpNKIHA.3400@TK2MSFTNGP03.phx.gbl...
>> Do you have more information concerning usage patterns? I.e., is there
>> more than one person attempting to update/add rows simultaneously? Or is
>> this all happening with only a single user? Also, what is your PK on the
>> Customers table? What happens if you wrap the INSERT INTO...SELECT in a
>> BEGIN TRANSACTION...COMMIT TRANSACTION?
>>
>> "Rob Dob" <robdob20012002@yahoo.com> wrote in message
>> news:ubpeJ5LKIHA.484@TK2MSFTNGP06.phx.gbl...
>> Hi,
>>
>> -1- I have winform that contains some textboxes, comboboxes etc these are
>> databound to a BindingSource
>> -2- if the user decides to add a new record I call BindingSource.AddNew()
>> -2- The BindingSource is bound to my dataset.
>> -4- when the user clicks on the Save Record button my function looks like
>> the following:
>>
>> try
>> {
>> this.CustomerBindingSource.EndEdit();
>> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy
>> = "XXX";
>> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
>> = DateTime.Now;
>> int ncount =
>> this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
>> }
>> catch (DBConcurrencyException oEx )
>> {
>> MessageBox.Show("Concurrency Erro: " + oEx.Message);
>> }
>> catch (System.Exception ex)
>> {
>> MessageBox.Show("Other Error: " + ex.Message);
>> }
>>
>> -5- The Dataset that is bound to the BindingSource has an identity field
>> which is an int and is also the primary key on the table, The insert
>> command that is generated by the vs2005 designer does NOT contain the
>> identy field but does a select after the insert inorder to repopulate the
>> dataset with the new id as send below:
>> INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@Date,
>> @ContactLastName);
>> SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
>> = SCOPE_IDENTITY())
>>
>> -6- I am getting some concurrency violations with updates but I am also
>> getting the odd concurrency violation with Adding new records is this
>> possible? also it appeard that for some reason today the update command
>> was also not updating some records down to the database, however then the
>> app was restarted and for some reason it started to work..
>> Any help would be greatly appreciated.
>> Thanks,
>>
>>
>> "Jim Rand" <jimrand@ix.netcom.com> wrote in message
>> news:uTgwKOLKIHA.5980@TK2MSFTNGP04.phx.gbl...
>>> I'm going to make several assumptions.
>>>
>>> 1) You use identity keys in the database.
>>> 2) You have figured out how to get the keys back to replace negative
>>> keys in
>>> your dataset that existed prior to update.
>>> 3) You are using timestamps for concurrency (that's a big assumption).
>>> 4) You process the dataset updates in chunks using the
>>> dataset.GetChanges(Deleted | Modified | Added)
>>> 5) You process the deletes first up the hierarchy. Then, you process the
>>> adds down the parent chain. Finally, you process the modifies.
>>>
>>> Let's say the user modified the parent row and added new child rows.
>>>
>>> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
>>> the
>>> added rows as you would expect. It also includes any modified parents.
>>> If
>>> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
>>> rows AND will also update the parent row if it had been modified. Fine
>>> so
>>> far. Here is what happens next. You do the
>>> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
>>> because
>>> the timestamp in the parent row has already changed.
>>>
>>> Sample SQL:
>>>
>>> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
>>> [LastUpdatedBy]) VALUES (@AccessFlag, @Description,
>>> @LastUpdatedBy);SELECT
>>> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM
>>> AgencyNET.AccessFlagLookup
>>> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
>>>
>>> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
>>> [Description] = @Description, [LastUpdated] = getutcdate(),
>>> [LastUpdatedBy]
>>> = @LastUpdatedBy WHERE (([AccessFlagLookupID] =
>>> @Original_AccessFlagLookupID) AND (CAST(TS AS INT) =
>>> @Original_TS));SELECT
>>> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
>>> (AccessFlagLookupID = @AccessFlagLookupID)
>>>
>>> That "(CAST(TS AS INT) = @Original_TS));" works with the first update
>>> with
>>> the added rows (modified parent) but fails when it gets updated again
>>> when
>>> the modified rows get processed.
>>>
>>> The way I found this was through getting unexpected concurrency errors.
>>> Stepping through the code and monitoring changes in the database
>>> timestamp
>>> values spotted the problem.
>>>
>>> Nasty.
>>>
>>>
>>
>>
>
>