none
linq to dataset DBNull problem RRS feed

  • Question

  • Hi all, 

    i got a table in my access 2007 database that contains a few nullable columns
                var taskView =
                    from task in table
                    where task.Field<int>("TaskID") == TaskID
                    select new
                    {
                        task.TaskName,
                        task.Desc
                     };
                       
    the Desc column is nullable and it is null in the first row.

    when i execute this i got an error: "The value for column 'Desc' in table 'table' is DBNull."

    i am expecting DBNull as it is null in my database but i am surprised that i cant set a default/alternative value for it.

    does anyone knows how to say set a default value such as string.empty for this null column? or how to work with the nulls here?

    thanks a lot
    Wednesday, April 15, 2009 6:26 AM

Answers

  •  var taskView = table.AsEnumerable()
                    .Where(i => i.Field<int>("TaskID") == TaskID)
                    .Select(i => new              
                                {
                                    TaskName = i.TaskName,
                                    Desc = i.Desc
                                }).FirstOrDefault();


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Thursday, April 16, 2009 9:53 PM
    • Marked as answer by TheNewBy Tuesday, April 21, 2009 9:50 AM
    Wednesday, April 15, 2009 11:03 PM
  • Do you mean you want to select just two columns and create a new DataTable?

    DataView dv = new DataView(table, "Column1 NOT NULL", "Column1", DataViewRowState.CurrentRows);
    DataTable newTable = dv.ToTable(false, "TaskName", "Desc");

    Is Desc a column Name? You should change that because that is a SQL keyword
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by TheNewBy Tuesday, April 21, 2009 9:50 AM
    Thursday, April 16, 2009 1:07 PM
  • Try this:

     var taskView = table.AsEnumerable()
                    .Where(i => i.Field<int>("TaskID") == TaskID)
                    .Select(i => new              
                                {
                                    TaskName = i.TaskName,
                                    MyColumnName = i.Desc
                                });
    foreach (var item in taskView)
    {
        Console.WriteLine("{0}, {1}", item.TaskName, item.MyColumnName);
    }

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by TheNewBy Tuesday, April 21, 2009 9:50 AM
    Thursday, April 16, 2009 9:52 PM

All replies

  •  var taskView = table.AsEnumerable()
                    .Where(i => i.Field<int>("TaskID") == TaskID)
                    .Select(i => new              
                                {
                                    TaskName = i.TaskName,
                                    Desc = i.Desc
                                }).FirstOrDefault();


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Thursday, April 16, 2009 9:53 PM
    • Marked as answer by TheNewBy Tuesday, April 21, 2009 9:50 AM
    Wednesday, April 15, 2009 11:03 PM
  • Thanks John for your reply, that did work!

    what if i just want to do a simple select?

     var taskView =
                    from task in table
                    select new
                    {
                        task.TaskName,
                        task.Desc
                     };
    Thursday, April 16, 2009 7:44 AM
  • Do you mean you want to select just two columns and create a new DataTable?

    DataView dv = new DataView(table, "Column1 NOT NULL", "Column1", DataViewRowState.CurrentRows);
    DataTable newTable = dv.ToTable(false, "TaskName", "Desc");

    Is Desc a column Name? You should change that because that is a SQL keyword
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by TheNewBy Tuesday, April 21, 2009 9:50 AM
    Thursday, April 16, 2009 1:07 PM
  • yeah, i gave a bad example, Desc was the column name.

    i think i didnt ask the second question clearly. sorry about that

    what i tried to ask was if the linq query tends to select more than 1 rows and some columns in the rows might be null, and i still want to select those rows have null in one of the columns, which means i have to find some way to check if the column value is dbnull and if it is i want to assign a default value say "" to the return column

    with traditional way of doing things such as using sql quary + datareader or what ever i have no problem dealing with this issue, but since i am using linq, i want to do this in the "LINQ" way. 

    i can modify the code in the dataset so that it checks for null for every nullable columns but i thought there have to be some smarter ways 
    Thursday, April 16, 2009 9:20 PM
  • Try this:

     var taskView = table.AsEnumerable()
                    .Where(i => i.Field<int>("TaskID") == TaskID)
                    .Select(i => new              
                                {
                                    TaskName = i.TaskName,
                                    MyColumnName = i.Desc
                                });
    foreach (var item in taskView)
    {
        Console.WriteLine("{0}, {1}", item.TaskName, item.MyColumnName);
    }

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by TheNewBy Tuesday, April 21, 2009 9:50 AM
    Thursday, April 16, 2009 9:52 PM
  • no, it throws same exception as before:
    The value for column '' in table '' is DBNull.

    since the column is sitll empty
    Friday, April 17, 2009 12:31 PM
  • Did you try a DataView?:

    DataView dv = new DataView(table);
    dv.RowFilter = String.Format("TaskID = {0}", TaskID);
    DataTable newTable = dv.ToTable(false, "TaskName", "Desc1");
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, April 17, 2009 1:10 PM
  • that one seems worked, interesting...

    Thanks for you help John :)
    Tuesday, April 21, 2009 9:50 AM
  • i want all tasks which have null values from linq query
    Thursday, February 18, 2010 1:23 PM