Cannot set Column 'myCol' to null. Please use DBNull instead

[Jason]

Hi,

The error below is shown in the web report viewer instead of the report:
An error has occured while processing Chart 'myChart':
Cannot set Column 'myColumn' to be null. Please use DBNull instead.

I am using a Telerik.Reporting.Chart bound to a Telerik,Reporting.DataSource. It uses a stored procedure to return a set of data. One of the columns it returns is nullable and when it returns a null I get the error above. However, this column is not actually used in the Chart.

If I change the sproc so that this column is NOT returned or it returns a 0 instead of NULL the report works fine. (I don't want to do this as this sproc is used elsewhere and I want to distinguish 0 from NULL).

I am using the 2010 Q1 release of Telerik Reporting (4.0.10.423).

Thanks,
Jason

[Steve]

Hi Jason,

Can you please post the stack trace of this problem? If it is not available in the InnerException, it should be present in the Output window in Visual Studio.

All the best,
Steve
the Telerik team

[Jason]

Certainly...

(I hope this looks okay for you - for some reason for me this block is displayed at the top of the web page,)

An exception has occurred while processing '' item:
System.ArgumentException: Cannot set Column 'myColumn' to be null. Please use DBNull instead.
   at System.Data.DataRow.set_Item(DataColumn column, Object value)
   at System.Data.DataRow.set_Item(String columnName, Object value)
   at Telerik.Reporting.Charting.DataHelper.CreateDataHelper(Object dataSource, String dataMember, Boolean isDesign)
   at Telerik.Reporting.Charting.DataManager.DataBind()
   at Telerik.Reporting.Processing.Chart.ProcessItem()
   at Telerik.Reporting.Processing.ReportItemBase.Process(DataMember data)

[Chavdar]

Hi Jason,

This is an issue with the chart's data binding when there is a nullable numeric column in the result. To resolve the problem you can proceed in two ways. First you can create a separate stored procedure only for the chart which does not return such columns especially if not needed. Second, you can fill a DataTable and pass it directly to the chart's data source in the NeedDataSource event of the chart as in the  example:

void chart1_NeedDataSource(object sender, System.EventArgs e)
        {
            var chart = (Telerik.Reporting.Processing.Chart)sender;

            var selectCommand = new SqlCommand();
            selectCommand.Connection = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True");
            selectCommand.CommandType = CommandType.Text;
            selectCommand.CommandText = "SELECT TOP 10 * FROM AdventureWorks.Production.Product WHERE Weight IS NULL";
            var adapter = new SqlDataAdapter(selectCommand);
            var dataTable = new DataTable();
            adapter.Fill(dataTable);
            chart.DataSource = dataTable;
        }

In order the NeedDataSource event to be fired make sure that the Chart item does not have an assigned data source (DataSource property should remain null) until the event is fired.

Hope this helps.

Greetings,
Chavdar
the Telerik team

[Jason]

Many thanks Chavdar. I am currently temporarily working around this by changing the sproc to using COALESCE to replace the NULLs with 0s. But thanks for the tip on using NeedDataSource, I will probably implement that is the long term solution.

Has this been identified as a bug that will be fixed, or do I need to log it in PITS?

Thanks,
Jason

[Steve]

Hello Jason,

Once we have a clear view on the problem we will publish it in PITS and then proceed with accepted fix.

We apologize for the inconvenience and appreciate your feedback and patience.

Kind regards,
Steve
the Telerik team

[Deepak Shakya]

Hi Steve,

The Solution works fine and the null values are plotted with dotted lines. And it seems that is is doing some kind of interpolation between the values that are available. Is there a way to not show these dotted lines in the chart?

Thanks.

[Deepak Shakya]

Hi,

Got it! Just setting the Line> Mode property to Zero

Thanks.

[Tina]

Hi,

Have you had a chance to fix this yet? I can't seem to find the issue in PITS...
(I am using Reporting Q3 2010.)

Thanks,


Tina

[MikeWiese]

It's not fixed in 5.0.11.510

I've logged it as a bug - I don't know whether these things show up in PITS.

To me it's a fundamental requirement of a charting object that it should be able to handle nulls. And that it should be able to differentiate between a null and a zero.

[Peter]

Hello MikeWiese,

We are aware of the current Chart item limitation to handle null values and this is logged in our bug tracking system. We will do our best to include null handling for the chart in subsequent Telerik Reporting release.

Regards,
Peter
the Telerik team

[Joe Sugden]

We just "updagraded" to 2011 Q3 SP1 and this issue is back again!!!
This is getting QUITE ridiculous, every time there is new release or even an SP, something breaks!!!
For example, radmaskbox in win forms has been broken for three consecutive releases !!

[Steve]

Hi Joe,

This problem hasn't been fixed, so we're surprised this worked for you in any version. We would appreciate if you attach a runnable sample that exhibits the problem in the support ticket you've opened. Once we review it, we would be able to provide more information.

Greetings,
Steve
the Telerik team

[Tina]

Hi Steve (& Joe),

I too am now using Q1 2011 SP1 and am able to confirm that the fix explained on Jul 15, 2010 NO LONGER WORKS. The error that is returned is the "Please use DBNull" error. (The Jul 2010 fix was to fill a DataTable and pass it directly to the chart's data source in the NeedDataSource event of the chart, which worked fine up until SP1.)

For my purposes, I need to differentiate between NULL values & zero values, so just setting a value to zero will not work. (NULL values on my chart indicate that we do not yet have a value available.)

Now, I have found a work around for this problem. Before binding the DataTable to the chart's datasource, pass it through a subroutine that will replace any of the nullable column values with an "empty" value. THE ONLY "empty" value that would work for me is: System.Double.NaN. (Note that I tried several different ways of doing this, but this is the only one that would NOT flip to a zero when the chart was drawn. Instead, this way the point & lines are not drawn at all, which is exactly what I need.)

See the example below:

Private Sub ResetNull(ByVal pTable As DataTable)
    'THIS IS A NEW SUB TO FIX A BUG WITH TELERIK REPORTING Q1 2011 SP1 (5.0.11.510)

    For Each oRow As DataRow In pTable.Rows

        'for each of the nullable columns, reset the value to an empty one

        If IsDBNull(oRow.Item("myCol")) Then
            oRow.Item("myCol") = System.Double.NaN
        End If

    Next

End Sub

Hope this helps!


Tina

[kahuna]

I got null-s working with Line and Bar charts (no need to use System.Double).

See explanation and download full example solution at: http://www.telerik.com/community/forums/reporting/telerik-reporting/solved-how-to-use-null-s-with-charts.aspx