Attention: We are retiring the ASP.NET Community Blogs. Learn more >

DataAdapter.Update Bug

Our Select from a sql server table returns 15 columns. Back on the client, we add 2 custom DataColumns that have expressions assigned to them. We databind to these custom DataColumns for display purposes only. 

I have been using Output params to retrieve timestamp and other fields on an Update\Insert using DataAdapter.Update.  I am testing selecting the row immediately after updating it (per page 217 in Essential ADO.NET) inside the sproc as I need more and more output params. For maintenance, I think it would be easier to just fetch the record after the update (lets put performance aside for a moment).

I am getting this:

"Cannot change ReadOnly property for the expression column."

when doing this.

I have since discovered this is a bug related to columns with expressions.

So, as a test I did this:

For Each col As DataColumn In dt.Columns

If Not (col.Expression.Trim = String.Empty) Then

expressions.Add(col.ColumnName, col.Expression)

col.Expression = String.Empty

End If

Next

where expressions is a hashtable.

I then issue the Update.

I then do this:

Dim myDE As DictionaryEntry

For Each myDE In expressions

If dt.Columns.Contains(myDE.Key.ToString) Then

dt.Columns(myDE.Key.ToString).Expression =

myDE.Value.ToString

End If

Next myDE

This code never runs as the update fails again. This time with:

Exception Type: System.NullReferenceException

Message: Object reference not set to an instance of an object.

TargetSite: Int32 Update(System.Data.DataRow[],

System.Data.Common.DataTableMapping)

HelpLink: NULL

Source: System.Data

We do not use TableMappings. Thinking this was the source, I added them.

Same exception.

How can I work around this bug and take advantage of this way of refreshing a DataTable?

My next guess is to remove the entire DataColumn prior to the Update then readd it.  I'll report back with the results.

Results:  You cannot remove the DataColumn from a DataTable with a Modifed DataRow.  So, long story short, this bug does not have a work aorund.

Update:

OK, so this works:

Dim columnsToRemoveForUpdate As New ArrayList

For Each col As DataColumn In dt.Columns

If Not (col.Expression.Trim = String.Empty) Then

columnsToRemoveForUpdate.Add(col)

End If

Next

For Each col As DataColumn In columnsToRemoveForUpdate

dt.Columns.Remove(col)

Next

' do the update

DataAdapter.Update(dt)

' add the DataColumns back

For Each col As DataColumn In columnsToRemoveForUpdate

dt.Columns.Add(col)

Next

Are you kidding me? Is this the only way to work around this bug?

6 Comments

  • Have you tried setting the "ReadOnly" property to True for the two custom DataColumns?

  • The workaround is obvious. Don't have datasets, recordsets, datatables, dataviews etc etc etc anywhere near the client or gui. If you only expose your own collections of objects you don't have these issues. We have an architecture that will not allow a dataset past the DAL layer...ever. Databinding to winform apps works great under this scenario btw.

  • Copying the table columns you want into <i>a new</i> table may be the only way out. We call it DataAdapter Hell and have found rolling your own adapter-like components to be the best way to work with data that isn't mapped directly to a database table. Maybe we'll get more flexibility in Whidbey?

  • I'd roll my own, however we are 1+ year into development. Thanks for the feedback.

  • Found another solution



    //store a local copy of the expression

    string exp = dt.Columns["colWithExpression"].Expression;

    //Set the expression to empty.

    dt.Columns["colWithExpression"].Expression = String.Empty;

    //Take a copy of itself - to force a refresh - if this is not

    //carried out you get an object reference not set

    dt= dt.Copy();

    this.DataAdapter.Update(dt);

    //then add the expression back in

    dt.Columns["colWithExpression"].Expression = exp;







    Ugly but works

  • Ugly bug, thanks for the article! Gwyn, consider what is happening if using tables with lot rows, and you copy it each time!

Comments have been disabled for this content.