jeevan
11/18/2007 4:32:00 AM
This problem is occurring when 50+ users are attempting to add/update rows
simultaneously then; not just one user? That's what I'm trying to discover.
And yes, please try wrapping your inserts and updates in single transactions
so we can determine if that helps alleviate the problem for you.
"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.
>>>
>>>
>>
>>
>
>