locked
Exception when assigning an SqlHierarchyId to its corresponding field in a DataRow RRS feed

  • Question

  • Hi All,

    I've been familiarising myself with the Sql Server HierarchyId data type and its ADO.NET equivalent the SqlHierarchyId structure and have written a small WPF program to access a test Db containing a hierarchy defined by a HierarchyId field. However, I've run into two problems:

    Firstly, if I try to retrieve the HierarchId field from a DataRow which I've done with the following code:

    public SqlHierarchyId HId {get { return dataRow["Level"]; }}

    I get an 'InvalidCast' exception. The only way round this one that I could find was with the following code:

    public SqlHierarchId Hid { get { return SqlHierarchyId.Parse(dataRow["Level"].ToString()); } }

    Which is obviously nuts.

    Secondly, When I try to assign an SqlHierarchyId structure to its corresponding field in a DataRow, as with the following code:

    SqlHierarchyId newId = _parent.GetDescendant(_child1, _child2);
    
    dataRow["Level"] = newId;

    I get an 'ArgumentException' with the following message:

    "Type of value has a mismatch with column typeCouldn't store </1/1/2/> in Level Column.  Expected type is SqlHierarchyId."

    Now, 'newId' is defined as an SqlHierarchyId and so it the 'Level' field in the DataRow so what's causing the problem?

    I've even tried the following assignment code (out of desperation):

     dataRow["Level"] = SqlHierarchyId.Parse( newId.ToString());

    but I get exactly the same exception. The relevant bit of the stack trace for the exception is as follows:

       at System.Data.DataColumn.set_Item(Int32 record, Object value)
       at System.Data.DataRow.set_Item(DataColumn column, Object value)
       at System.Data.DataRow.set_Item(String columnName, Object value)
       at WpfDbHierarchyTest1.Models.DbAccess.AddChild(SqlHierarchyId _parent, SqlHierarchyId _child1, SqlHierarchyId _child2, String _location, Int32 _locationTypeId) in C:\Development\VS2015\WPF\WpfDbHierarchyTest1\Models\DbAccess.cs:line 160
       at WpfDbHierarchyTest1.Models.DataM..ctor(SqlHierarchyId _parent, SqlHierarchyId _child1, SqlHierarchyId _child2, String _location, Int32 _locationTypeId) in C:\Development\VS2015\WPF\WpfDbHierarchyTest1\Models\DataM.cs:line 24
       at WpfDbHierarchyTest1.ViewModels.DataVM..ctor(DataVM _parent, DataVM _child1, DataVM _child2, String _location, Int32 _locationTypeId) in C:\Development\VS2015\WPF\WpfDbHierarchyTest1\ViewModels\DataVM.cs:line 28
       at WpfDbHierarchyTest1.ViewModels.MainVM.AddNewItem(ItemAddType _param) in C:\Development\VS2015\WPF\WpfDbHierarchyTest1\ViewModels\MainVM.cs:line 373
       at WpfDbHierarchyTest1.Commands.DelegateCommand`1.Execute(Object parameter) in C:\Development\VS2015\WPF\WpfDbHierarchyTest1\Commands\DelegateCommand.cs:line 35

    Has anyone got any ideas about what's happening here?

    Any info will be gratefully received.

    Kind regards,


    RobDev

    Monday, October 17, 2016 3:17 PM

All replies

  • Hi,

    1). For code public SqlHierarchId Hid { get { return SqlHierarchyId.Parse(dataRow["Level"].ToString()); } }, as you would like to return a SqlHierarchId type data, you have to use the SqlHierarchyId.Parse function to convert string value. 

    2). To assign data to dataRow["Level"], you need a string value. So, you need to modify your code to be:

     dataRow["Level"] = newId.ToString();



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 18, 2016 6:02 AM
  • Hi Vicky,

    many thanks for your reply. Regarding "2)." above, I tried what you suggested, as follows:

    dataRow["Level"] = newId.ToString();

    but when it executed I got exactly the same exception (i.e. "ArgumentException" with exactly the same error message "Type of value has a mismatch with column typeCouldn't store </1/1/2/> in Level Column.  Expected type is SqlHierarchyId."). What I don't understand is why is the message saying that "Expected type is SqlHierarchyId", which is what I originally passed it, and why won't it accept a string?

    Am I missing something here? Do you have any ideas as to what's going on?

    Kind regards,


    RobDev

    Tuesday, October 18, 2016 11:04 AM
  • Hi,

    This issue seems very strange to me. I'm trying to involve other engineers to check it. Will post here for any updates. 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 20, 2016 1:52 AM
  • Hi Vicky,

    many thanks for your reply. I have some more info regarding this problem. If I create a DataTable in memory with a column defined as an SqlHierarchId then I can successfully create a DataRow, assign an SqlHierarchyId to the relevant column and add the row to the table, as follows:

    DataTable dtTest = new DataTable("TestHId");
    DataColumn dc = new DataColumn("HId", typeof(SqlHierarchyId));
    dtTest.Columns.Add(dc);
    
    dc = new DataColumn("TestString", typeof(string));
    dtTest.Columns.Add(dc);
    
    DataRow drTest = dtTest.NewRow();
    
    drTest["TestString"] = "The quick Brown Fox";
    
    drTest["HId"] = SqlHierarchyId.Parse("/1/1/2/");   // This Works
    
    dtTest.Rows.Add(drTest);

    However, when I retrieve my table from my Sql Server Db, create a new DataRow for it and try to assign my new SqlHierarchId I get the "Argument Exception" as described above. I have a simple Sql Server Db containing two tables, the one containing the HierarchId is called SimpleDemo and is defined as shown in the following screen shot:

    SimpleDemo Table

    Now, my test program accesses this table, creates a new DataRow from it and assigns a SqlHierarchyId, which causes the "ArgumentException". My little test program containing both my examples are shown in the following code block:

    class Program
    {
        public const string FLD_ID = "Id";
        public const string FLD_LEVEL = "Level";
        public const string TBL_PLACES = "tbl_Places";
    
    
        private static DataSet dataSet;
        static void Main(string[] args)
        {
            DataTable dtTest = new DataTable("TestHId");
            DataColumn dc = new DataColumn("HId", typeof(SqlHierarchyId));
            dtTest.Columns.Add(dc);
    
            dc = new DataColumn("TestString", typeof(string));
            dtTest.Columns.Add(dc);
    
            DataRow drTest = dtTest.NewRow();
            drTest["TestString"] = "The quick Brown Fox";
            drTest["HId"] = SqlHierarchyId.Parse("/1/1/2/");   // This Works
    
            dtTest.Rows.Add(drTest);
    
            dataSet = new DataSet();
            FillDataSet();
    
    
            DataTable dt = dataSet.Tables[TBL_PLACES];
    
            DataRow dr = dt.NewRow();
            dr[FLD_LEVEL] = SqlHierarchyId.Parse("/1/1/2/");   // This causes the exception
        }
    
        private static void FillDataSet()
        {
            try
            {
                using (SqlConnection conn = 
                    new SqlConnection(ConfigurationManager.ConnectionStrings["HierarchyTest1ConnectionString"].ConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("SELECT * FROM SimpleDemo order by Level;", conn);
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    
                    adp.Fill(dataSet, TBL_PLACES);
    
    
                    conn.Close();
                    cmd.Dispose();
                    adp.Dispose();
                }
            }
            catch (Exception exc)
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("Exception: {0}", exc.Message);
                System.Diagnostics.Debug.WriteLine(sb.ToString());
                System.Diagnostics.Debugger.Break();
            }
        }
    }
    

    Now, I might have missed something obvious but this can't be right, surely.

    Kind regards,


    RobDev

    Thursday, October 20, 2016 2:02 PM
  • Hi,

    Unfortunately this is going to require more debugging. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

    Regards,

    Tuesday, December 13, 2016 10:18 AM
  • Hi Molly,

    many thanks for your reply. I have created a 'feedback' issue about this problem with Microsoft Connect (link: https://connect.microsoft.com/VisualStudio/feedback/details/3107845/ado-net-exception-when-assigning-an-sqlhierarchyid-to-its-corresponding-field-in-a-datarow). However, as yet, I have received no response from anyone there except the usual acknowledgement of receipt.

    As regards opening a support case, I afraid I don't have that kind of money at the moment but, if this problem is as fundamental as I think it is, then I'm surprised that Microsoft isn't looking into it more urgently.

    Please let me know if you hear anything regarding a possible fix for this problem.

    Kind regards,


    RobDev

    Tuesday, December 13, 2016 1:21 PM