Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

ADO.NET Missing Field in a DataRow error driving me crazy


:P
On this page:

In my  West Wind Web Store I have an issue where about once a week or so I get an error that relates to a specific field. I blogged about this issue before a long while back and I have still problems with this one.

 

The error occurs against any direct access of an Item Url in the store. For example:

 

http://www.west-wind.com/wwstore/item.aspx?sku=WWHELP40

 

Give it a try and see if it fails for you . In most cases it won’t. The errors get logged so the more errors I see the more I can maybe see a pattern. If it fails, see if it fails more than once and with a different browser. Try switching your browser’s language (if you’re not using US-English).  I’d be interested to see what comes up.

 

Here’s what goes on where the error occurs. In my business object layer I have a simple entity mapping scheme that basically maps a DataRow to front end object that accesses the DataRow fields in order to provide strong typing. The use of this approach is essentially to get strong typing without all the mess and overhead that typed DataSets incur.

 

The problem occurs in this block of code which is used in the store to display an item. Basically the following code uses a business object to retrieve an inventory item by sku and then sets a few properties with various fixups to display the data. The C# code in the ASP.NET Codebehind page looks like this:

 

private void DisplayItem()

{

      // *** Allow item disply from URL query string

      string Qty = Request.QueryString["qty"];

      if (Qty != null)

            this.txtQty.Text = Qty;

      else

            this.txtQty.Text = "1";

 

      // *** If Action=Add was used force to Button_Click

      if (this.FirstAccess && Request.QueryString["Action"] == "Add") 

      {

            this.FirstAccess = false;

            this.btnSubmit_Click(this,null);

            return;

      }

           

      // Put user code to initialize the page here

      this.IvtItem = WebStoreFactory.GetbusItem();

 

      if (this.Sku == null)

      {

            // *** Can't display an item with out a sku

            Server.Transfer("default.aspx");

            return;

      }    

 

      // *** Retrieve the inventory item for checking stock and binding

      if (!this.IvtItem.GetItemBySku(this.Sku))

      {

            MessageDisplay.DisplayMessage("Invalid Item","The item you selected is no longer available. " +

                                                  "If you typed the Url in manually make sure the SKU is typed correctly.");

            return;

      }

 

      this.rowItem = this.IvtItem.GetTypedDataRow(false);

 

      if (this.rowItem.Redirect != "")

      {

            Response.Redirect( this.rowItem.Redirect );

            return;

      }

 

      // *** If we have a subitem show the parent item instead

      if (rowItem.Parentsku != null && rowItem.Parentsku != "" )

      {

            // *** We redirect here so our URL is correct -

            // *** otherwise we have issues picking up querystring

            Response.Redirect( "item.aspx?Sku=" + this.rowItem.Parentsku );

            return;

      }

     

      // *** We'll bind in code here because we need to fix up a few things

      this.lblTitle.Text = rowItem.Descript;

 

      // *** Insert a list of subitems if user has specified it

      string Description = rowItem.Ldescript.Replace("<%= InsertSubItemList() %>",IvtItem.SubItemHtmlList(this.Sku));

 

      // *** Force HTML linebreaks into the field text

      this.lblDescription.Text = this.DisplayMemo( Description );

     

      // *** Display price as Currency

      this.lblPrice.Text = rowItem.Price.ToString("C");

 

      // *** Display Item image only if image is not blank

-->   if (!wwUtils.Empty( rowItem.Itemimage ) )

            this.imgProduct.ImageUrl = "itemimages/" + rowItem.Itemimage;

      else

            this.imgProduct.Visible = false;

 

      if (rowItem.Url != null && rowItem.Url != "")

            this.lblMoreInfo.Text = "  target='_MoreInfo'>More Info";

 

      // *** Don't allow adding if stock is out

      if ( !IvtItem.CheckStock(1))

      {

            this.btnSubmit.Enabled=false;

            this.btnSubmit.Text = "Out of Stock";

      }

}

 

The failure occurs towards the end of the method with the code in bold. ItemImage is a field in the wws_Items table which is loaded up inside the GetItemBySku business object method. GetItemBySku is pretty simple – it basically runs a SELECT * FROM wws_items to retrieve all fields of the database table. The code looks like this:

 

public bool GetItemBySku(string Sku)

{

      int RecCount = this.Execute("select * from " + this.Tablename + " where sku=@Sku",this.Tablename,

                                        this.CreateParameter("@Sku",Sku));

 

      if (RecCount < 0)

      {

            this.SetError(this.Data.ErrorMessage);

            return false;

      }

      if (RecCount < 1)

      {

            this.SetError("Item not found.");

            return false;

      }

     

      this.DataRow = this.DataSet.Tables[this.Tablename].Rows[0];

 

      return true;

}

 

The method handles errors, so if an exception occurs in the Execute call the error is bubbled up and RecCount returns -1. So I’m certain that the ADO.NET code beneath is actually working in retrieving the data. The ASP.NET code checks the result from the method and shows an error if it fails. If this method succeeds an item was loaded.

 

When this code returns it ends up with a single DataRow that contains all the fields. If you look at the first block of code again, you can see there’s a GetTypedDataRow() method which returns the pseudo-entity object which behind the scenes maps the DataRow fields to properties.

 

A number of other fields/properties are accessed before the error occurs so it appears that code is working as it’s supposed to. But an error occurs when accessing the ItemImage field – about once a week.

 

As I mentioned all errors are logged by a custom WebErrorHandler which produces this output for the error:

 

/wwstore/item.aspx?sku=WWHELP40

 

Column 'Itemimage' does not belong to table wws_items.

 on 16.05.2005 03:22:45

 

--- Stack Trace ---

   at System.Data.DataRow.get_Item(String columnName)

   at Westwind.WebStore.wws_itemsRow.get_Itemimage()

   at Westwind.WebStore.Item.DisplayItem()

   at Westwind.WebStore.Item.Page_Load(Object sender, EventArgs e)

   at System.Web.UI.Control.OnLoad(EventArgs e)

   at System.Web.UI.Control.LoadRecursive()

   at System.Web.UI.Page.ProcessRequestMain()

 

--- Request Information ---

  Full Url: http://www.west-wind.com/wwstore/item.aspx?sku=WWHELP40

        IP: 85.96.199.220

   Referer: http://www.west-wind.com/wwHelp/download.asp

   Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; MyIE2; .NET CLR 1.0.3705; .NET CLR 1.1.4322)

    Locale: de-de

     Login: Anonymous

 

This is REALLY, REALLY odd, because 99.9% of the time this code works perfectly fine. In fact, I’ve never been able to duplicate this error at all. Itemimage is a varchar field in the database and does not contain any nulls. It holds a string to an image file that is used to display the item’s image. It’s something like wwhelp.gif for example. The actual implementation of the property looks like this:

 

public String Itemimage

{

      get

      {

            if (this.DataRow == null)

                  return this._Itemimage;

 

            if (this.UseColumns)

                  return (String) this.DataRow[this.ItemimageColumn];

            else

                  return (String) this.DataRow["Itemimage"];

      }

      set

      {

            if (DataRow != null)

                  this.DataRow["Itemimage"] = value;

           

            this._Itemimage = value;                             

      }

}

The actual line of code that fails is the DataRow access for the ItemImage field.

 

What’s really odd here is this: At this point of failure the code for this request has already accessed a number of the other fields/entity properties of the same object/DataRow without errors! So the DataRow is there. The other fields are there and the property accessors for them are working just fine.

 

What this error suggests is that the SELECT * FROM wws_items is not returning the ItemImage field even though it is part of the SQL Server Table it’s retrieved from. And remember it does work 99.9% of the time.

 

I noticed reviewing my logs that the errors always occur with foreign locales. I just today added logging the Locale information to possibly illuminate me on whether there some locale consistency to the failure but all the errors have dates in European formats. 16.05.2005. Just to be sure I tried switching the browser into German, but it didn’t seem to make a difference – I see no failure. Besides why would the thread locale setting cause the field not be retrieved when we’re dealing with a string field?

 

I’ve tried all sorts of stuff since the last post. I changed the field name from Image to ItemImage thinking that maybe in some odd way Image interfered with the reserved word of the Image field type. Still have errors. I moved access of the ItemImage further down in the processing code so it’s now next to the last access. Still fails and still fails only on the ItemImage field. All the preceeding field accesses work just fine.

 

I also tried bypassing the Entity field altogether, going directly to the DataRow value. Same result – still have intermittent errors.

 

This is very, very odd and I am absolutely stumped? Anybody have any ideas what else to look at?

 


The Voices of Reason


 

Bob Archer
May 17, 2005

# re: ADO.NET Missing Field in a DataRow error driving me crazy

Is it possible that in some configuration or access path that the user accessing the db doesn't have rights to that field? I assume you are using NT Auth rather than a SQL Login?

If so, you may want to run SQL profiler to catch access to this command and see what user is accessing the db. Perhaps it doesn't have permission to the column?

You may want to also take the * out of the query and list the fields specifically...? Not sure if this will help, but you never know.

Rick Strahl
May 17, 2005

# re: ADO.NET Missing Field in a DataRow error driving me crazy

Thanks Bob, but i don't it's permissions. There's a specific account that's mapped to this database and that's used on the connection string - if that was bad it would always fail, not selectively.

I'm not a big fan of SELECT * usually, but this method is an extension of a generic business method that is meant to pull all the data in the current record. If I can't count on SELECT * working I'd have some serious issues in all of my apps. And then again why would this fail just sometimes <g>...

Craig McGuff
May 17, 2005

# re: ADO.NET Missing Field in a DataRow error driving me crazy

Are you renaming the columns in the DataSet at any point after/during retrieving the records?

I have seen this error when parsing out spaces etc. From memory I think there is a case sensitivity issue as well.

Rick Strahl
May 18, 2005

# re: ADO.NET Missing Field in a DataRow error driving me crazy

Hi Craig, nope. The code above basically gets the DataRow and it's accessed directly (well through the entity wrapper). The freaking thing is that it works 99.9% of the time. Anything like that would fail everytime.

Kevin Pirkl
May 18, 2005

# re: ADO.NET Missing Field in a DataRow error driving me crazy

I have no idea if this will help but we did run into a perhaps more complex SQL bug once and I threw in another suggestion for good measure. At least ideas may lead to different directions in thinking to provide alternate possibles.

We ran into a strange similar problem where a field just up and dissapeared once in a while but our resulting dataset was based on calling a complex view with lots of embedded case statements within it. One in a while a field would just not be ther. We solved the problem by providing explicit SQL Schema referencing like databasename.owner.table.field or something along those lines. Our case statement though having the field in it had the same field elsewhere as well and the explicit referencing worked to solve the problem.

Another idea is to do two SQL statements together and the first recordset with all fields but the ItemImage and the last recordset with only the ItemImage and to see if the recordset collection would work.

Just some thoughts.

Best of luck.

Rick Strahl's WebLog
May 23, 2005

# DataRows, String Indexes and case sensitivity with Turkish Locale

Here's another caveat of dynamic locale switching: String indexes in things like DataRows and Tables may not return the correct values if the target locale doesn't interpret upper and lower case values as the same thing. Turkish apparently assigns different meanings to upper and lower case I and i chars, and DataRow[

Lou Gallo
June 15, 2005

# re: ADO.NET Missing Field in a DataRow error driving me crazy

I found your blog while trying to find an answer to the very same problem. What I've observed is that the column is there and if refed by MyRow["theCol"].ToString() it does retrieve the value. BUT if you access it via the the Typed construct MyRow.theCol doesn't work.

Seems like the DataSet symbol table (or whatever) gest lost. Intellisense doesn't work for that DataSet.

I haven't got much more than that. I'll check back to see if you've solved the problem. If I find anything I'll post back too.

Thanks

Lou

Rick Strahl
June 15, 2005

# re: ADO.NET Missing Field in a DataRow error driving me crazy

The problem here was the Turkish locale where the upper case I and lower case i are not synomous.

JulB
November 15, 2010

# re: ADO.NET Missing Field in a DataRow error driving me crazy

Great post! We had similar problem with WinForms application running on Oracle and we already had bunch of all kind of crazy ideas and then I found your post. Thank you!

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024