config file question

In my SSIS packages I'm adding a config file that is storing only the connection strings to the database. Though most of my packages are reading or writing out flat files as well as connecting to a db. Do I need to put both db connection string and flat file connection strings in the config file? I'm currently adding only the db connection due to that will change depending which environment its ran in, unlike the flat file location will always remain the same.

I just want to be sure I don't need both for my package to run successfully.

You can add only 1 or as many configuration values as you want in a single file. As a good practice you should have both values set via configurations; just to cover a possible change in the flat file location. You could also create a separate config file for each property.

Conditionally adding a column to my custom component (part 2)

A month or so ago I instigated this thread- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=243117&SiteID=1 which talked about how to conditionally add a column to my component depending on the value of a custom property. If the custom property is TRUE then the column should appear in the output (and vice versa).

Bob Bojanic said I should use the SetComponentProperty() method to do this and that is working pretty well. However, it bothers me that SetComponentProperty() could be called, the column will then be added, and then the package developer could press 'Cancel'. In this instance the value of my custom property would be inconsistent with the presence of the extra column.

How do you get around that?

Thanks

Jamie

This is what component views are for. Call the GetComponentView method to create a view of the component. Then make all your changes to the component. If the user selects OK then call commit on the view. If the user selects cancel then call Cancel on the view. The view keeps track of all changes and commit saves them to the component and cancel discards all the changes.

Thanks,

Matt

|||

To get OK/Cancel functionaility in a component UI, for free, make sure you make all changes through the CManagedComponentWrapper instance of your component, not the component direct. I go on about this in the book, download the samples to see an example.

metaData is IDTSComponentMetaData90 normally passed on the form Ctor or your own setup method , comming from the UI class. Then call "CManagedComponentWrapper wrapper = metaData.Instantiate();" to get the wrapper.

Update - Matt are you sure about GetComponentView, was that not dropped or at least you no longer need to do it, sometime after beta 2 I think. The bool result from IDtsComponentUI.Edit is now sufficient to commit or rollback the changes made through the wrapper.

|||

Darren,

Yes, I am sure but the caveat here is that I always think in native code and everyone else thinks in managed code. For native code you need to use views. For managed code it does the views for you without your knowledge (please disregard the man behind the screen ).

Thanks,

Matt

sqlsql

Conditionally adding a column to my custom component

Hi,

I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'.

Depending on its setting, I want to include (or not include) a column in the output.

Any advice on how to go about doing this (with some sample code) would be much appreciated!

Here's how I'm declaring the property in ProvideComponentProperties()

IDTSCustomProperty90 IncludeErrorDesc = ComponentMetaData.CustomPropertyCollection.New(); IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; IncludeErrorDesc.Name = "Some Name"; IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName; IncludeErrorDesc.Value = Convert.ToBoolean(false);

Thanks in advance

-Jamie

Implement SetComponentProperty method in your component and if the property is set to true add your column, otherwise find it in the collection and remove it.

I do not have a time to build you a sample, but give it a try and let us know if it does not go well.

BTW, you do not need the following line from your sample:

IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName;

Thanks.

|||Hi Bob,
I nevre replied to this. Just wanted to say thanks for this - it worked a treat!
-Jamie|||

You are welcome, Jamie. I am glad it worked out.

Conditional formatting on drill down

I have a report that when the user drills down I need to change the formatting to include borders and/or colours. I tried adding the following statement to top border of the element without luck. Since I don't know when the detail is the last line of the drop down, I am uncertain how to try adding it there.

I do not want the underline to show when there is no expanded drill down.

=iif((Previous(Level) > 0, "Dotted", "None")

g1 name1 total1

g1 name2 total2

d po#3a total3a

d po#3b total3b <<-- want to add underline between total3b and total3

g1 name3 total3

Thank you

Just a quick stab...but couldn't you include the "underline" as a border on the top of your "g1 name total3" line as opposed to on the bottom of the last subtotal? That way it looks like it's rendering on the lowest one, but in reality it's not.

|||

I'm sorry I wasn't clear enough. I need the correct statement to go on the border style TOP of the g1 entry so that it will only show when there is a drill down.

I have tried "=iff((previous(level)) > 0), "Dotted", "None")" however it did not work. Along with variations on a theme.

Concurrency violation adding records Why?

Hi,
I'm getting periodic concurrency violations while adding new records, can
someone please tell me how to resolve this. I have an application that is
running multi-user, approx 60+ users and for some reason my application is
throwing a concurrency violation when adding a new record, I have a
bindingsource that is bound to several fields, I then issue a EndEdit()
followed by an update. 95% of the time it works and the other 5% I get a
concurrency error.. This is very frustrating. the dataset being updated
contains an identity field which is an int, I set this identity field using
the datasets autoincrement feature and autostep -1. My insert statement
does NOT contain the identity field.
Thanks,If your clustered index column is going down by 1 (increment set to negative
one) then you do realize that SQL Server has to reorganize the entire table
after every single insert? If this is the case it might be at least
contributing to/exacerbating your issue.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm getting periodic concurrency violations while adding new records, can
> someone please tell me how to resolve this. I have an application that
> is running multi-user, approx 60+ users and for some reason my application
> is throwing a concurrency violation when adding a new record, I have a
> bindingsource that is bound to several fields, I then issue a EndEdit()
> followed by an update. 95% of the time it works and the other 5% I get a
> concurrency error.. This is very frustrating. the dataset being updated
> contains an identity field which is an int, I set this identity field
> using the datasets autoincrement feature and autostep -1. My insert
> statement does NOT contain the identity field.
>
> Thanks,
>|||the identity column never makes it to the sql, its only their to satisfy my
datatset. when the update method of my tableadapter is called it has no
mention of the identity column.
Do you have any other ideas? thank you for your help.
"Mike C#" <xyz@.xyz.com> wrote in messa ge
news:%234qPSXKKIHA.5468@.TK2MSFTNGP05.phx.gbl...
> If your clustered index column is going down by 1 (increment set to
> negative one) then you do realize that SQL Server has to reorganize the
> entire table after every single insert? If this is the case it might be
> at least contributing to/exacerbating your issue.
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.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], [D
escription],
[LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SELE
CT
AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.Access
Flag,
[Description] = @.Description, [LastUpdated] = getutcdate(), [Las
tUpdatedBy]
= @.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.|||Hi,
-1- I have winform that contains some textboxes, comboboxes etc these are da
tabound 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 th
e following:
try
{
this.CustomerBindingSource.EndEdit();
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy
= "XXX";
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLas
tEdited = DateTime.Now;
int ncount = this.customersTableAdapter.Update(this.dataSetCustomers1.Custom
ers);
}
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 whi
ch is an int and is also the primary key on the table, The insert command t
hat is generated by the vs2005 designer does NOT contain the identy field bu
t does a select after the insert inorder to repopulate the dataset with the
new id as send below:
INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.D
ate, @.ContactLastName);
SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID =
SCOPE_IDENTITY())
-6- I am getting some concurrency violations with updates but I am also gett
ing 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 u
pdating some records down to the database, however then the app was restarte
d 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.g
bl...
> 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 th
e
> 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 becaus
e
> the timestamp in the parent row has already changed.
>
> Sample SQL:
>
> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [
;Description],
> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SE
LECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
>
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.Acce
ssFlag,
> [Description] = @.Description, [LastUpdated] = getutcdate(), [L
astUpdatedBy]
> = @.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.
>
>|||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].DateLas
tEdited
= 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 (@.D
ate,
@.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);SE
LECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.Acce
ssFlag,
> [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.
>|||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].Added
By =
> "XXX";
> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateL
astEdited
> = 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...
>|||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...
>

Concurrency violation adding records Why?

Hi,
I'm getting periodic concurrency violations while adding new records, can
someone please tell me how to resolve this. I have an application that is
running multi-user, approx 60+ users and for some reason my application is
throwing a concurrency violation when adding a new record, I have a
bindingsource that is bound to several fields, I then issue a EndEdit()
followed by an update. 95% of the time it works and the other 5% I get a
concurrency error.. This is very frustrating. the dataset being updated
contains an identity field which is an int, I set this identity field using
the datasets autoincrement feature and autostep -1. My insert statement
does NOT contain the identity field.
Thanks,If your clustered index column is going down by 1 (increment set to negative
one) then you do realize that SQL Server has to reorganize the entire table
after every single insert? If this is the case it might be at least
contributing to/exacerbating your issue.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm getting periodic concurrency violations while adding new records, can
> someone please tell me how to resolve this. I have an application that
> is running multi-user, approx 60+ users and for some reason my application
> is throwing a concurrency violation when adding a new record, I have a
> bindingsource that is bound to several fields, I then issue a EndEdit()
> followed by an update. 95% of the time it works and the other 5% I get a
> concurrency error.. This is very frustrating. the dataset being updated
> contains an identity field which is an int, I set this identity field
> using the datasets autoincrement feature and autostep -1. My insert
> statement does NOT contain the identity field.
>
> Thanks,
>|||the identity column never makes it to the sql, its only their to satisfy my
datatset. when the update method of my tableadapter is called it has no
mention of the identity column.
Do you have any other ideas? thank you for your help.
"Mike C#" <xyz@.xyz.com> wrote in messa ge
news:%234qPSXKKIHA.5468@.TK2MSFTNGP05.phx.gbl...
> If your clustered index column is going down by 1 (increment set to
> negative one) then you do realize that SQL Server has to reorganize the
> entire table after every single insert? If this is the case it might be
> at least contributing to/exacerbating your issue.
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I'm getting periodic concurrency violations while adding new records, can
>> someone please tell me how to resolve this. I have an application that
>> is running multi-user, approx 60+ users and for some reason my
>> application is throwing a concurrency violation when adding a new record,
>> I have a bindingsource that is bound to several fields, I then issue a
>> EndEdit() followed by an update. 95% of the time it works and the other
>> 5% I get a concurrency error.. This is very frustrating. the dataset
>> being updated contains an identity field which is an int, I set this
>> identity field using the datasets autoincrement feature and autostep -1.
>> My insert statement does NOT contain the identity field.
>>
>> Thanks,
>>
>|||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.|||This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C82894.5AB596A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 ==3D "XXX";
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLast=Edited =3D DateTime.Now;
int ncount =3D =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 =3D 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 =3D SCOPE_IDENTITY())
> > UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] =3D =@.AccessFlag, > [Description] =3D @.Description, [LastUpdated] =3D getutcdate(), =[LastUpdatedBy] > =3D @.LastUpdatedBy WHERE (([AccessFlagLookupID] =3D > @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) =3D =@.Original_TS));SELECT > CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE > (AccessFlagLookupID =3D @.AccessFlagLookupID)
> > That "(CAST(TS AS INT) =3D @.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.
> >
--=_NextPart_000_000B_01C82894.5AB596A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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 =3D "XXX";this.dataSetCustomers1.Customers[CustomerBindingSource.Position=].DateLastEdited =3D DateTime.Now;int ncount =3D 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 =3D 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" 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 =3D SCOPE_IDENTITY())> > UPDATE [AgencyNET].[AccessFlagLookup] =SET [AccessFlag] =3D @.AccessFlag, > [Description] =3D @.Description, =[LastUpdated] =3D getutcdate(), [LastUpdatedBy] > =3D @.LastUpdatedBy WHERE (([AccessFlagLookupID] =3D > @.Original_AccessFlagLookupID) AND =(CAST(TS AS INT) =3D @.Original_TS));SELECT > CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE > (AccessFlagLookupID =3D @.AccessFlagLookupID)> > That "(CAST(TS AS INT) =3D =@.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.> >

---

--=_NextPart_000_000B_01C82894.5AB596A0--|||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.
>|||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.
>>
>|||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.
>>
>>
>

Concurrency violation adding records Why?

Hi,
I'm getting periodic concurrency violations while adding new records, can
someone please tell me how to resolve this. I have an application that is
running multi-user, approx 60+ users and for some reason my application is
throwing a concurrency violation when adding a new record, I have a
bindingsource that is bound to several fields, I then issue a EndEdit()
followed by an update. 95% of the time it works and the other 5% I get a
concurrency error.. This is very frustrating. the dataset being updated
contains an identity field which is an int, I set this identity field using
the datasets autoincrement feature and autostep -1. My insert statement
does NOT contain the identity field.
Thanks,
If your clustered index column is going down by 1 (increment set to negative
one) then you do realize that SQL Server has to reorganize the entire table
after every single insert? If this is the case it might be at least
contributing to/exacerbating your issue.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm getting periodic concurrency violations while adding new records, can
> someone please tell me how to resolve this. I have an application that
> is running multi-user, approx 60+ users and for some reason my application
> is throwing a concurrency violation when adding a new record, I have a
> bindingsource that is bound to several fields, I then issue a EndEdit()
> followed by an update. 95% of the time it works and the other 5% I get a
> concurrency error.. This is very frustrating. the dataset being updated
> contains an identity field which is an int, I set this identity field
> using the datasets autoincrement feature and autostep -1. My insert
> statement does NOT contain the identity field.
>
> Thanks,
>
|||the identity column never makes it to the sql, its only their to satisfy my
datatset. when the update method of my tableadapter is called it has no
mention of the identity column.
Do you have any other ideas? thank you for your help.
"Mike C#" <xyz@.xyz.com> wrote in messa ge
news:%234qPSXKKIHA.5468@.TK2MSFTNGP05.phx.gbl...
> If your clustered index column is going down by 1 (increment set to
> negative one) then you do realize that SQL Server has to reorganize the
> entire table after every single insert? If this is the case it might be
> at least contributing to/exacerbating your issue.
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.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.
|||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[CustomerBindingSo urce.Position].AddedBy = "XXX";
this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].DateLastEdited = DateTime.Now;
int ncount = this.customersTableAdapter.Update(this.dataSetCust omers1.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.
>
|||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[CustomerBindingSo urce.Position].AddedBy =
"XXX";
this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].DateLastEdited
= DateTime.Now;
int ncount =
this.customersTableAdapter.Update(this.dataSetCust omers1.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.
>
|||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[CustomerBindingSo urce.Position].AddedBy =
> "XXX";
> this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].DateLastEdited
> = DateTime.Now;
> int ncount =
> this.customersTableAdapter.Update(this.dataSetCust omers1.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...
>
|||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...
>

Concatenating Numeric Fields

Friends,

I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.

SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles

Returns the sum of vehFacID & vehID. Doh!

J.H.

I think I found it...Something like this works..

SELECT *, CAST(vehFacID AS VARCHAR(4)) + '-' + CAST(vehID AS VARCHAR(10)) AS vehCombo FROM Vehicles

Is this the right way to do this?

J.H.

|||

If the vehFacId and vehId are numbers, then this is the way to go.

<stuff you can ignore if you want>

A bit nasty with the column names, I hope for your sake you don't have 3 letter abbreviations in every column (but not in your table name.) That must be hard to follow.

</stuff you can ignore if you want>

|||

Are you referring to the "veh" abbreviation? If so, why would you say it would be hard to follow? A small sample of my tables is like:

Vehicles, Departments, Facilities, Customers, etc...I use the 3 (or 4 sometimes) letter abbreviation to determine which table the field came from. I am open to hearing a better suggestion if you have one.

J.H.

|||

You know what else I am curious about is the casting. My numeric columns in this case are smallInt and can hold up to 5 digits. Is the recommendation to cast them to varchar(5) in this case?

J.H.

|||

I don't see any problem even if you cast to varchar(25), that way down the road if you happen to change the datatype from smallint to int, you don't have to worry about T-SQL code like this in various stored procs and functions.

As far as database naming conventions goes there isn't a standard. I wish Microsoft would have suggested something on MSDN.

I kind of agree with a article on aspfaq: http://www.aspfaq.com/show.asp?id=2538

|||

A little bit for the veh abbreviation. I would prefer to see vehicleId, and vehicleFaciltiyId, etc, which is easier to follow for the uninitiated (and in fact good finger exercises :)

The vehFacId was what kind of concerned me. I got this flash of:

select vehId, mak, modYr, numWhl, vehIdNum...etc.

There were a lot of these sorts of naming conventions back when names could only be 30 characters (funny how many times we hit 30, but rarely do I go over it now...) I don't like to see something that might be an issue and not say something. (hence the: <stuff you can ignore if you want> tags) Like the link to aspfaq says, it is a matter of taste, but the more clear it is, the more clear it is.

If a new person or contractor or newsgroup helper can read it and understand it, your job of naming is done right.