Inserting NULL into DataTable

Question

Morning (in RSA at least),

I'm trying to create a data driven menu, using data from a self-referencing table to create a (2-level) hierarchical structure. Example Data is:

MenuID  ParentID  Text      Url      CSS
1       Null      Top                topCSS
2       Null      Second             secCSS
3       1         abc       z.aspx   abcCSS
4       1         def       y.aspx   abcCSS
5       2         ghi       x.aspx   defCSS

I'm using LINQ to Entities to get this data. Then I populate a DataTable, then populate a DataSet and then create a DataRelation before converting it to XML to use in a xmlDataSource where it gets transformed for use as the Menu's DataSource.

I must admit I've taken a lot of code from these Forums, and it should work. Except the transform requires a NULL value in ParentID to indicate a top-level menu item, but I can't insert a NULL into the DataTable. Code is below:

        using (var cntIuvo = new iuvocexi_dbEnts())
        {
            var b = (from a in cntIuvo.MenuNavs select a);
            DataTable myTB = new DataTable();
            myTB.Columns.Add("MenuID");
            myTB.Columns.Add("ParentID");
            myTB.Columns.Add("url");
            myTB.Columns.Add("CSS");
            DataRow myDR;

            foreach (var rec in b)
            {
                myDR = myTB.NewRow();
                myDR["MenuID"] = rec.MenuID;
                myDR["ParentID"] = rec.ParentID;  // error is generated here
                myDR["url"] = rec.url;
                myDR["CSS"] = rec.CSS;
                myTB.Rows.Add(myDR);
            }

            DataSet ds = new DataSet(); 
            ds.Tables.Add(myTB);
            ds.DataSetName = "Menus";
            ds.Tables[0].TableName = "Menu";
            DataRelation relation = new DataRelation("ParentChild", ds.Tables["Menu"].Columns["MenuID"], ds.Tables["Menu"].Columns["ParentID"], true);
            relation.Nested = true;
            ds.Relations.Add(relation);
            xmlDataSource1.Data = ds.GetXml();
            if (Request.Params["Sel"] != null)
                Page.Controls.Add(new System.Web.UI.LiteralControl("You selected " +
                  Request.Params["Sel"]));
        }

My question is: How do I insert NULL into the DataTable, or, failing that, how do I get the LINQ to Entities to populate a DataTable/DataSet, or, failing that how do I set up the Transform to allow for (say) a 0 instead of NULL.

Transform.xslt is below:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes" encoding="utf-8"/>

  <!-- Replace root node name Menus with MenuItems
   and call MenuListing for its children-->
  <xsl:template match="/Menus">
    <MenuItems>
      <xsl:call-template name= "MenuListing" />
    </MenuItems>
  </xsl:template>

  <!-- Allow for recursive child nodeprocessing -->
  <xsl:template name="MenuListing">
    <xsl:apply-templates select ="Menu" />
  </xsl:template>

  <xsl:template match="Menu">
    <MenuItem>
      <!-- Convert Menu child elements to MenuItem attributes -->
      <xsl:attribute name="Text">
        <xsl:value-of select="Text"/>
      </xsl:attribute>
      <xsl:attribute name="ToolTip">
        <xsl:value-of select="Text"/>
      </xsl:attribute>
      <xsl:attribute name="NavigateUrl">
        <xsl:text>?Sel=</xsl:text>
        <xsl:value-of select = "url"/>
      </xsl:attribute>

      <!-- Recursively call MenuListing forchild menu nodes -->
       <xsl:if test="count(Menu) >0">
         <xsl:call-template name="MenuListing" />
       </xsl:if>
    </MenuItem>
  </xsl:template>
</xsl:stylesheet>

Thank you very much for your attention so far!

Regards

Paul

score 2 · Answer 1 · answered Sep 11 '11 at 07:32

2

Is ParentID of cntIuvo.MenuNavs nullable?

myDR["ParentID"] = rec.ParentID ?? Convert.DBNull; // Replace null value to DBNull

answered Sep 11 '11 at 07:32

Viacheslav Ivanov

1,535
13
22

Thanks _Fred_, but DBNull is also not accepted into the table. I may have to resort to 'raw' SQL to get the data directly into a DataTable. Any conversions seem to halt whee Null is concerned. I can;t fill the original table with Itegers as I get a circular reference error urng the relationship build... But thanks for your help. Paul – Paul Innes Sep 11 '11 at 14:54
1

Is the "ParentID" column nullable (DataColumn.AlllowDBNull is true)? – Viacheslav Ivanov Sep 18 '11 at 04:59

score 0 · Answer 2 · answered Sep 11 '11 at 07:32

0

May be like this? myDR["ParentID"] = rec.ParentID == null ? DBNull.Value : rec.ParentID;

answered Sep 11 '11 at 07:32

mishau

582
4
11

Hi mishou, Thanks for your help, but as mentioned above Null and DBNull both cause problems. Cheers, Paul/ – Paul Innes Sep 11 '11 at 14:54
Have you tried step-by-step debuggng yet? What eaxctly rec.ParentID is in your menu record? Null or what? What is the type name of the exception? I tested your piece of code filling all the fields with DBNull.Value and it works! – mishau Sep 11 '11 at 23:47

Inserting NULL into DataTable

2 Answers2