Concurrencty violation with decimal type

  • Thread starter Thread starter hb
  • Start date Start date
H

hb

Situation:

Using a dataset's UpdateCommand, when a decimal type field changes from
integer to decimal, the update goes through. When the field changes from
decimal to integer or another decimal, I get a Concurrency violation: the
UpdateCommand affected 0 records error.

I have used trace to view the CommandText and all the source columns and
parameters. When I try to execute the update statement directly in the
database, the record updates. Just a code issue, apparently. I noticed in a
search of these forums someone else had a similar problem, and they solved
it, but never posted their fix.

Any suggestions? using .NET 1.0 framework w/ ODP.NET data provider.


[I have posted this to other newsgroups and forums, so apologies if you have
to read this more than once.]
 
It would be useful to see the UPDATE statement you're executing. If it's
generated by the CommandBuilder or one of the drag-and-drop or DataAdapter
Configuration wizard it could be that one of the columns don't match. I
think this approach is pretty (fill in your own adjective). I suggest using
a simpler approach--put a TimeStamp column in your table and use it to
determine if there are collisions. It's faster and easier to debug.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
William (Bill) Vaughn said:
It would be useful to see the UPDATE statement you're executing. If it's
generated by the CommandBuilder or one of the drag-and-drop or DataAdapter
Configuration wizard it could be that one of the columns don't match. I
think this approach is pretty (fill in your own adjective). I suggest using
a simpler approach--put a TimeStamp column in your table and use it to
determine if there are collisions. It's faster and easier to debug.
Click to expand...

Thank you for the reply, Bill. You are the first and only so far out of all
the ngs and forums I've asked.

The Update statement is generated by the CommandBuilder. Since the table
being updated contains almost 100 columns (designed by previous contractor
on project, ugh), we thought the CommandBuilder would be the way to go.

We do a bit of massaging before the update executes.

First, we call
protected void CleanTableForUpdating() {
ArrayList colStrings = new ArrayList();
foreach (DataColumn col in requestDataTable.Columns) {
String colName = col.ColumnName.ToLower();
if (colName != "control_yr" && colName != "control_nr"
&& requestDataTable.Rows[0][colName,
DataRowVersion.Original].Equals(requestDataTable.Rows[0][colName,
DataRowVersion.Current])) {
colStrings.Add( colName );
}
}
foreach ( string column in colStrings ) {
requestDataTable.Columns.Remove( column );
}
}

Note: Control_Yr and Control_Nr are the primary key of the table. We remove
any other columns that have not been changed from the table, so the update
only affects the changed columns. We did this to make, as you said,
debugging easier. After CleanTableForUpdating(), the Update() is called on
the data adapter.

Then we do a bit of tracing in the RowUpdating handler.
public void daUpdatingHandler( object sender, OracleRowUpdatingEventArgs e)
{
HttpContext.Current.Trace.Write( "Row_Updating", e.Command.CommandText );
foreach( OracleParameter oraParam in e.Command.Parameters ) {
HttpContext.Current.Trace.Write( oraParam.SourceColumn,
oraParam.Value.ToString() );
}
}

This outputs the statement and the columns/values.
Here is an example statement:
UPDATE "TECHNICAL_ASSISTANCE_REQUESTS" SET "CRRNT_ARFRM_HRS"=:1 WHERE
"CONTROL_NR"=:2 AND "CONTROL_YR"=:3 AND "CRRNT_ARFRM_HRS"=:4

The columns/values are:

CRRNT_ARFRM_HRS 3088.3
CONTROL_NR 329
CONTROL_YR 2004
CRRNT_ARFRM_HRS 3088.6

So, if I take this statement and merge in the values, I have:
UPDATE TECHNICAL_ASSISTANCE_REQUESTS SET CRRNT_ARFRM_HRS = 3088.3 WHERE
CONTROL_NR = 329 AND CONTROL_YR = 2004 AND CRRNT_ARFRM_HRS = 3088.6

I then login via TOAD and execute the statement. No problem. However, the
statement fails in code and gives the concurrency violation error.

I have changed the variables I store CRRNT_ARFRM_HRS from decimal to double,
and I still get the same error. I think today I will try to just store it
as a string, since I don't think the values will be used for any
calculations.

If you have further suggestions, other than rewriting my entire class, I
look forward to them.

Hillarie
 
I had problems posting this reply, so sorry if it shows up twice.

William (Bill) Vaughn said:
It would be useful to see the UPDATE statement you're executing. If it's
generated by the CommandBuilder or one of the drag-and-drop or DataAdapter
Configuration wizard it could be that one of the columns don't match. I
think this approach is pretty (fill in your own adjective). I suggest using
a simpler approach--put a TimeStamp column in your table and use it to
determine if there are collisions. It's faster and easier to debug.
Click to expand...

Thank you for the reply, Bill. You are the first and only so far out of all
the ngs and forums I've asked.

The Update statement is generated by the CommandBuilder. Since the table
being updated contains almost 100 columns (designed by previous contractor
on project, ugh), we thought the CommandBuilder would be the way to go.

We do a bit of massaging before the update executes.

First, we call
protected void CleanTableForUpdating() {
ArrayList colStrings = new ArrayList();
foreach (DataColumn col in requestDataTable.Columns) {
String colName = col.ColumnName.ToLower();
if (colName != "control_yr" && colName != "control_nr"
&& requestDataTable.Rows[0][colName,
DataRowVersion.Original].Equals(requestDataTable.Rows[0][colName,
DataRowVersion.Current])) {
colStrings.Add( colName );
}
}
foreach ( string column in colStrings ) {
requestDataTable.Columns.Remove( column );
}
}

Note: Control_Yr and Control_Nr are the primary key of the table. We remove
any other columns that have not been changed from the table, so the update
only affects the changed columns. We did this to make, as you said,
debugging easier. After CleanTableForUpdating(), the Update() is called on
the data adapter.

Then we do a bit of tracing in the RowUpdating handler.
public void daUpdatingHandler( object sender, OracleRowUpdatingEventArgs e)
{
HttpContext.Current.Trace.Write( "Row_Updating", e.Command.CommandText );
foreach( OracleParameter oraParam in e.Command.Parameters ) {
HttpContext.Current.Trace.Write( oraParam.SourceColumn,
oraParam.Value.ToString() );
}
}

This outputs the statement and the columns/values.
Here is an example statement:
UPDATE "TECHNICAL_ASSISTANCE_REQUESTS" SET "CRRNT_ARFRM_HRS"=:1 WHERE
"CONTROL_NR"=:2 AND "CONTROL_YR"=:3 AND "CRRNT_ARFRM_HRS"=:4

The columns/values are:

CRRNT_ARFRM_HRS 3088.3
CONTROL_NR 329
CONTROL_YR 2004
CRRNT_ARFRM_HRS 3088.6

So, if I take this statement and merge in the values, I have:
UPDATE TECHNICAL_ASSISTANCE_REQUESTS SET CRRNT_ARFRM_HRS = 3088.3 WHERE
CONTROL_NR = 329 AND CONTROL_YR = 2004 AND CRRNT_ARFRM_HRS = 3088.6

I then login via TOAD and execute the statement. No problem. However, the
statement fails in code and gives the concurrency violation error.

I have changed the variables I store CRRNT_ARFRM_HRS from decimal to double,
and I still get the same error. I think today I will try to just store it
as a string, since I don't think the values will be used for any
calculations.

If you have further suggestions, other than rewriting my entire class, I
look forward to them.

Hillarie
 
Ok, what backend are you using? The double quotes look problematic for SQL
Server unless you have quoted identifiers enabled. I'm not so sure you're
saving any time using the CB and your cleanup approach.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

hb said:
William (Bill) Vaughn said:
It would be useful to see the UPDATE statement you're executing. If it's
generated by the CommandBuilder or one of the drag-and-drop or DataAdapter
Configuration wizard it could be that one of the columns don't match. I
think this approach is pretty (fill in your own adjective). I suggest using
a simpler approach--put a TimeStamp column in your table and use it to
determine if there are collisions. It's faster and easier to debug.
Click to expand...

Thank you for the reply, Bill. You are the first and only so far out of all
the ngs and forums I've asked.

The Update statement is generated by the CommandBuilder. Since the table
being updated contains almost 100 columns (designed by previous contractor
on project, ugh), we thought the CommandBuilder would be the way to go.

We do a bit of massaging before the update executes.

First, we call
protected void CleanTableForUpdating() {
ArrayList colStrings = new ArrayList();
foreach (DataColumn col in requestDataTable.Columns) {
String colName = col.ColumnName.ToLower();
if (colName != "control_yr" && colName != "control_nr"
&& requestDataTable.Rows[0][colName,
DataRowVersion.Original].Equals(requestDataTable.Rows[0][colName,
DataRowVersion.Current])) {
colStrings.Add( colName );
}
}
foreach ( string column in colStrings ) {
requestDataTable.Columns.Remove( column );
}
}

Note: Control_Yr and Control_Nr are the primary key of the table. We remove
any other columns that have not been changed from the table, so the update
only affects the changed columns. We did this to make, as you said,
debugging easier. After CleanTableForUpdating(), the Update() is called on
the data adapter.

Then we do a bit of tracing in the RowUpdating handler.
public void daUpdatingHandler( object sender, OracleRowUpdatingEventArgs e)
{
HttpContext.Current.Trace.Write( "Row_Updating", e.Command.CommandText );
foreach( OracleParameter oraParam in e.Command.Parameters ) {
HttpContext.Current.Trace.Write( oraParam.SourceColumn,
oraParam.Value.ToString() );
}
}

This outputs the statement and the columns/values.
Here is an example statement:
UPDATE "TECHNICAL_ASSISTANCE_REQUESTS" SET "CRRNT_ARFRM_HRS"=:1 WHERE
"CONTROL_NR"=:2 AND "CONTROL_YR"=:3 AND "CRRNT_ARFRM_HRS"=:4

The columns/values are:

CRRNT_ARFRM_HRS 3088.3
CONTROL_NR 329
CONTROL_YR 2004
CRRNT_ARFRM_HRS 3088.6

So, if I take this statement and merge in the values, I have:
UPDATE TECHNICAL_ASSISTANCE_REQUESTS SET CRRNT_ARFRM_HRS = 3088.3 WHERE
CONTROL_NR = 329 AND CONTROL_YR = 2004 AND CRRNT_ARFRM_HRS = 3088.6

I then login via TOAD and execute the statement. No problem. However, the
statement fails in code and gives the concurrency violation error.

I have changed the variables I store CRRNT_ARFRM_HRS from decimal to double,
and I still get the same error. I think today I will try to just store it
as a string, since I don't think the values will be used for any
calculations.

If you have further suggestions, other than rewriting my entire class, I
look forward to them.

Hillarie

in
Click to expand...
Click to expand...
 
William (Bill) Vaughn said:
Ok, what backend are you using? The double quotes look problematic for SQL
Server unless you have quoted identifiers enabled. I'm not so sure you're
saving any time using the CB and your cleanup approach.
Click to expand...

Bill, thanks for your attention to my problem.

Today, I changed the datatype on the column to varchar2 (Oracle 9i DB). We
don't need to perform calculations on the data, so I decided to not use
number(9,1). The two airframe hours columns were the only columns set to
accept decimals, and they were the only two columns causing this error. I
know the double quotes looked problematic, but it is working with them.
Changing the db column type has resolved my problem. I can update the hours
without any errors now. I wish I knew why number(9,1) did not work.
Columns of type number(5), for instance, do work. Just that pesky decimal.

Shrug. At least I have a working update now.

Thanks,
Hillarie
hb said:
William (Bill) Vaughn said:
It would be useful to see the UPDATE statement you're executing. If it's
generated by the CommandBuilder or one of the drag-and-drop or DataAdapter
Configuration wizard it could be that one of the columns don't match. I
think this approach is pretty (fill in your own adjective). I suggest using
a simpler approach--put a TimeStamp column in your table and use it to
determine if there are collisions. It's faster and easier to debug.
Click to expand...

Thank you for the reply, Bill. You are the first and only so far out of all
the ngs and forums I've asked.

The Update statement is generated by the CommandBuilder. Since the table
being updated contains almost 100 columns (designed by previous contractor
on project, ugh), we thought the CommandBuilder would be the way to go.

We do a bit of massaging before the update executes.

First, we call
protected void CleanTableForUpdating() {
ArrayList colStrings = new ArrayList();
foreach (DataColumn col in requestDataTable.Columns) {
String colName = col.ColumnName.ToLower();
if (colName != "control_yr" && colName != "control_nr"
&& requestDataTable.Rows[0][colName,
DataRowVersion.Original].Equals(requestDataTable.Rows[0][colName,
DataRowVersion.Current])) {
colStrings.Add( colName );
}
}
foreach ( string column in colStrings ) {
requestDataTable.Columns.Remove( column );
}
}

Note: Control_Yr and Control_Nr are the primary key of the table. We remove
any other columns that have not been changed from the table, so the update
only affects the changed columns. We did this to make, as you said,
debugging easier. After CleanTableForUpdating(), the Update() is called on
the data adapter.

Then we do a bit of tracing in the RowUpdating handler.
public void daUpdatingHandler( object sender, OracleRowUpdatingEventArgs e)
{
HttpContext.Current.Trace.Write( "Row_Updating", e.Command.CommandText );
foreach( OracleParameter oraParam in e.Command.Parameters ) {
HttpContext.Current.Trace.Write( oraParam.SourceColumn,
oraParam.Value.ToString() );
}
}

This outputs the statement and the columns/values.
Here is an example statement:
UPDATE "TECHNICAL_ASSISTANCE_REQUESTS" SET "CRRNT_ARFRM_HRS"=:1 WHERE
"CONTROL_NR"=:2 AND "CONTROL_YR"=:3 AND "CRRNT_ARFRM_HRS"=:4

The columns/values are:

CRRNT_ARFRM_HRS 3088.3
CONTROL_NR 329
CONTROL_YR 2004
CRRNT_ARFRM_HRS 3088.6

So, if I take this statement and merge in the values, I have:
UPDATE TECHNICAL_ASSISTANCE_REQUESTS SET CRRNT_ARFRM_HRS = 3088.3 WHERE
CONTROL_NR = 329 AND CONTROL_YR = 2004 AND CRRNT_ARFRM_HRS = 3088.6

I then login via TOAD and execute the statement. No problem. However, the
statement fails in code and gives the concurrency violation error.

I have changed the variables I store CRRNT_ARFRM_HRS from decimal to double,
and I still get the same error. I think today I will try to just store it
as a string, since I don't think the values will be used for any
calculations.

If you have further suggestions, other than rewriting my entire class, I
look forward to them.

Hillarie
Click to expand...
Click to expand...
 
Back
Top