Why an empty cell throws an error while executing an SQL stored procedure

Question

SELECT CAST ([content_html] AS XML).query('/root/Physicians/specialty/a') AS [Specialty1] , CAST ([content_html] AS XML).query('/root/Physicians/specialty2/a') AS [Specialty2] , CAST ([content_html] AS XML).query('/root/Physicians/specialty3/a') AS [Specialty3] , CAST ([content_html] AS XML).query('/root/Physicians/specialty4/a') AS [Specialty4] , CAST ([content_html] AS XML).query('/root/Physicians/specialty5/a') AS [Specialty5] , CAST ([content_html] AS XML).query('/root/Physicians/specialty6/a') AS [Specialty6] FROM [db].[dbo].[content] WHERE [folder_id] = '188' AND (content_status = 'A') ORDER BY [content_title] 

ASP.net Repeater (partially):

 <asp:Label ID="lblSpec1" runat="server"><%# Eval("Specialty1").ToString() + DisplayMultipleValues(Eval("Specialty2").ToString()) + DisplayMultipleValues(Eval("Specialty3").ToString()) + DisplayMultipleValues(Eval("Specialty4").ToString()) + DisplayMultipleValues(Eval("Specialty5").ToString()) + DisplayMultipleValues(Eval("Specialty6").ToString()) %></asp:Label> 

C # (code that processes multiple entries and adds , ):

 public string DisplayMultipleValues(string strValue) { return (NonBlankValueOf(strValue)); } public string NonBlankValueOf(string source) { return (string.IsNullOrEmpty(source)) ? "" : ", " + source; } 

When I run the query in SQL, it works fine (if there is no value for the column, then this is an empty cell), but running it through the code behind, the following error occurs:

 Server Error in '/' Application. DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Specialty3'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Specialty3'. Source Error: Line 96: </div> Line 97: <div class="optionRight"> Line 98: <asp:Label ID="lblSpec1" runat="server"><%# Eval("Specialty1").ToString() + DisplayMultipleValues(Eval("Specialty2").ToString()) + DisplayMultipleValues(Eval("Specialty3").ToString()) + DisplayMultipleValues(Eval("Specialty4").ToString()) + DisplayMultipleValues(Eval("Specialty5").ToString()) + DisplayMultipleValues(Eval("Specialty6").ToString()) %></asp:Label> Line 99: </div> Line 100: </div> Source File: c:\er.aspx Line: 98 Stack Trace: [HttpException (0x80004005): DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Specialty3'.] System.Web.UI.DataBinder.GetPropertyValue(Object container, String propName) [phone] System.Web.UI.DataBinder.Eval(Object container, String[] expressionParts) +142 ASP.find_provider_aspx.__DataBind__control22(Object sender, EventArgs e) in c:\er.aspx:98 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +304 System.Web.UI.Control.DataBindChildren() [phone] System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +321 System.Web.UI.Control.DataBindChildren() [phone] System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +321 System.Web.UI.WebControls.Repeater.CreateItem(Int32 itemIndex, ListItemType itemType, Boolean dataBind, Object dataItem) +183 System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +659 System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +164 find_provider.Search() in c:\er.aspx.cs:294 find_provider.Page_Load(Object sender, EventArgs e) in c:\er.aspx.cs:37 System.Web.UI.Control.LoadRecursive() +71 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3178 

What is the best way to solve it?

I tried the following:

 <asp:Label ID="lblspec1" runat="server"><%# If(Eval("Specialty1").ToString() Is DBNull.Value, "", Eval("Specialty1").ToString()) + If(Eval("Specialty2").ToString() Is DBNull.Value, "", DisplayOffices(Eval("Specialty2").ToString())) + If(Eval("Specialty3").ToString() Is DBNull.Value, "", DisplayOffices(Eval("Specialty3").ToString())) + If(Eval("Specialty4").ToString() Is DBNull.Value, "", DisplayOffices(Eval("Specialty4").ToString())) + If(Eval("Specialty5").ToString() Is DBNull.Value, "", DisplayOffices(Eval("Specialty5").ToString())) + If(Eval("Specialty6").ToString() Is DBNull.Value, "", DisplayOffices(Eval("Specialty6").ToString())) %></asp:Label> 

Answer 1

You can add something like this to the code behind the page:

 protected object MyEval(string expression) { object o = null; try { o = DataBinder.Eval(this.GetDataItem(), expression); } catch { o = System.String.Empty; } return o; } 

and then replace all Evals with "MyEval":

 <asp:Label ID="lblSpec1" runat="server"><%# MyEval("Specialty1").ToString() + DisplayMultiplMyEvalues(MyEval("Specialty2").ToString()) + DisplayMultiplMyEvalues(MyEval("Specialty3").ToString()) + DisplayMultiplMyEvalues(MyEval("Specialty4").ToString()) + DisplayMultiplMyEvalues(MyEval("Specialty5").ToString()) + DisplayMultiplMyEvalues(MyEval("Specialty6").ToString()) %></asp:Label> 

Answer 2

If your problem is caused by NULL values, you can solve your problem with sth, for example:

 public string DisplayMultipleValues(object strValue) { if (strValue == null) return ""; else return (NonBlankValueOf(strValue.ToString())); } 

Your Eval statements should look like this:

 <%# Eval("Specialty1") == null ? "" : Eval("Specialty1").ToString() + DisplayMultipleValues(Eval("Specialty2")) + etc ... 

Answer 3

As stated by Herbney in the comments. DBNull will not be selected String.IsNullOrEmpty. DBNull is handled differently. to express it in terms of code.

 DBNull.Value != null 

So basically you need to check if the DBNull element is before trying to display or perform any action on it.

Essentially

  <%# Eval("Specialty1") != DBNull.Value ? Eval("Specialty1").ToString() : String.Empty + DisplayMultipleValues(Eval("Specialty2") != DBNull.Value ? Eval("Specialty2").ToString() : String.Empty) + etc... %> 

Answer 4

The trace message and the error message that you indicated exactly indicate why it is not working. It says:

 DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Specialty3'. 

which comes out

 ... System.Web.UI.DataBinder.Eval(Object container, String[] expressionParts) +142 ... 

which means error when you make Eval("Specialty3") .

What it all boils down to is that the dataset coming back from your database does not contain a column named Specialty3. This may be due to a spelling error, from updating an update, or for a number of other reasons.