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

15 posts, 1 answers
  1. Jason
    Jason avatar
    13 posts
    Member since:
    Aug 2012

    Posted 14 Jul 2010 Link to this post

    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
  2. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 14 Jul 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Jason
    Jason avatar
    13 posts
    Member since:
    Aug 2012

    Posted 14 Jul 2010 Link to this post

    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)

  5. Answer
    Chavdar
    Admin
    Chavdar avatar
    898 posts

    Posted 15 Jul 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  6. Jason
    Jason avatar
    13 posts
    Member since:
    Aug 2012

    Posted 16 Jul 2010 Link to this post

    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
  7. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 16 Jul 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  8. Deepak Shakya
    Deepak Shakya avatar
    118 posts
    Member since:
    Aug 2007

    Posted 26 Jan 2011 Link to this post

    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.
  9. Deepak Shakya
    Deepak Shakya avatar
    118 posts
    Member since:
    Aug 2007

    Posted 26 Jan 2011 Link to this post

    Hi,

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

    Thanks.
  10. Tina
    Tina avatar
    2 posts
    Member since:
    Jan 2011

    Posted 14 Feb 2011 Link to this post

    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
  11. MikeWiese
    MikeWiese avatar
    41 posts
    Member since:
    Apr 2007

    Posted 25 May 2011 Link to this post

    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.
  12. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 27 May 2011 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  13. Joe Sugden
    Joe Sugden avatar
    52 posts
    Member since:
    Feb 2010

    Posted 23 Jan 2012 Link to this post

    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 !!
  14. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 25 Jan 2012 Link to this post

    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

    Q3’11 of Telerik Reporting is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  15. Tina
    Tina avatar
    2 posts
    Member since:
    Jan 2011

    Posted 09 Mar 2012 Link to this post

    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
  16. kahuna
    kahuna avatar
    6 posts
    Member since:
    Jun 2007

    Posted 23 May 2012 Link to this post

    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

Back to Top
DevCraft banner