Odd Error Using HierarchyID with VB

Question

Ignore all this and see the Update below
In SS 2016 I have a simple table as follows:

ID int indentity
Code hierarchyid
Name varchar(50)

In my VB application I try to read the data as follows:

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Types

    Dim Nodes As New List(Of Node)
    Using conn As New SqlConnection(My.Settings.ConnectionString)
        com = New SqlCommand("GetInfo", conn)
        com.CommandType = CommandType.StoredProcedure
        conn.Open()
        Dim rdr As SqlDataReader = com.ExecuteReader
        While rdr.Read
            Nodes.Add(New Node With {.ID = CLng(rdr(0)), .Code = CType(rdr(1), SqlHierarchyId), .Name = CStr(rdr(2))})
        End While
    End Using

Where the query GetInfo just returns all the data in the SQL table and Node is just a class that holds the three columns in the SQL table.

When I run this I get an extremely long error message that begins like this:

System.IO.FileLoadException was unhandled
FileName=Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
FusionLog==== Pre-bind state information ===
LOG: DisplayName = Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
 (Fully-specified)

If I remove ".Code = CType(rdr(1), SqlHierarchyId)" from inside the While loop I get no error and the Nodes list is properly filled. My conclusion is that use of the HierarchId is somehow causing the problem.

But the error message makes no sense to me. The only clue I can offer is that my Project reference to Microsoft.SQLServer.Types is Version 13.0.0.0 while in the error message it is listed as Version 10.0.0.0 Could this be the problem and, if so, how would I go about fixing it. If not, where should I look for the issue?

I can post the whole error message if you think that might help.
Update The problem is indeed a mismatch in SQL Server Types. As noted here the default for Type System Version is "latest" which is obsolete. So I replaced "latest" with "Type System Version=SQL Server 2016" which gave an error that it was an invalid value. Eventually, I found that "Type System Version=SQL Server 2012" will compile OK but it now wants Microsoft.SqlServer.Types, Version=11.0.0.0 which is not on my system. I have Version 13. So I still have a version mismatch problem. Now the question is how do I specify my connection string to tell it to use Version 13.

score 0 · Accepted Answer · answered Jan 17 '17 at 05:34

I found the answer. I added the following to my app.config file:

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <dependentAssembly> 
            <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
            <bindingRedirect oldVersion="10.0.0.0" newVersion="13.0.0.0" /> 
        </dependentAssembly>
    </assemblyBinding>
</runtime>

Note the "newVersion" attribute. That is what tells Visual Studio which version of Microsoft.SqlServer.Types to use.

Odd Error Using HierarchyID with VB

1 Answers1