johnfnj
asked on
VB.NET Application Hierarchyid datatype returning error system.invalidOperationException: DataReader.GetFieldType() returned null
I have a desktop vb.net application developed in VS 2010 with a SqlServer 2008 R2 back end which utilizes the Microsoft.SqlServer.Types assembly as a reference (copy local). The application was working as expected. I have just migrated to VS 2015 and SQL Server 2016.
I have set the .net framework to 4.6 and corrected the References within the project to the newest versions. This would be Microsoft.SqlServer.Types (13.0) the old one was version 10.0. The forms that utilize the tables with the HierarchyID data types fail with the datareader.getfieldtype error.
So just for sanity I removed the version 13.0 assembly from the references and copied the older version 10 from another machine and added that one in as a reference where I had copied it (C: root directory). And the application worked as expected. Unfortunately, I don't want to have an older version of an assembly if I can avoid it. Additionally, with the old version the deployment fails with an error of looking for the new version of the assembly.
I have looked through many forums from VB to VS to SqlServer and although it seems folks have had similar problems, it does not appear there is a definitive answer to why the most current version of the Microsoft.SqlServer.Types is not functioning in the same manner as the version 10.
Any help would be greatly appreciated.
I have set the .net framework to 4.6 and corrected the References within the project to the newest versions. This would be Microsoft.SqlServer.Types (13.0) the old one was version 10.0. The forms that utilize the tables with the HierarchyID data types fail with the datareader.getfieldtype error.
So just for sanity I removed the version 13.0 assembly from the references and copied the older version 10 from another machine and added that one in as a reference where I had copied it (C: root directory). And the application worked as expected. Unfortunately, I don't want to have an older version of an assembly if I can avoid it. Additionally, with the old version the deployment fails with an error of looking for the new version of the assembly.
I have looked through many forums from VB to VS to SqlServer and although it seems folks have had similar problems, it does not appear there is a definitive answer to why the most current version of the Microsoft.SqlServer.Types is not functioning in the same manner as the version 10.
Any help would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi John,
Yes it will work. they are also doing the same thing like i mentioned above but with <<. Download and install “SQLSysClrTypes.msi” from following link>>
Then, you should make sure the “Microsoft.SqlServer.Types
Great ! Cheers.
Have you implemented this.
Yes it will work. they are also doing the same thing like i mentioned above but with <<. Download and install “SQLSysClrTypes.msi” from following link>>
Then, you should make sure the “Microsoft.SqlServer.Types
Great ! Cheers.
Have you implemented this.
ASKER
Hello Pawan,
Yes I have implemented and the forms utilizing the tables with the hierarchyid are now working.
I am facing yet another issue with the hierarchyid column in a class where I am utilizing linq.
I get an invalid cast exception but both the assignee and assignment columns are sqlhierarchyid type not sure why I am facing this issue.
Again same scenario worked prior to the upgrade now not working. Yet another battle.
Yes I have implemented and the forms utilizing the tables with the hierarchyid are now working.
I am facing yet another issue with the hierarchyid column in a class where I am utilizing linq.
I get an invalid cast exception but both the assignee and assignment columns are sqlhierarchyid type not sure why I am facing this issue.
Again same scenario worked prior to the upgrade now not working. Yet another battle.
Hi John,
Is this done :)?
Regards,
Pawan
Is this done :)?
Regards,
Pawan
ASKER
Hello Pawan,
Yes all complete. Thank you.
Yes all complete. Thank you.
Sorry last time incorrect comment was selected. Closing via proper comment. Provided what was asked.
ASKER
Thank you for the detailed instructions.
Unfortunately, the machine it will be deployed to will not have the dll in the above specified location as these are newer machines with clean builds. So the directory structure under the Sql Server directory no longer has the Assembly directory under 100 or 110 directory.
The dll is found in the both the 120 and 130 directory under SDK\Assembly respectively. The issue is with either of these two newer versions of the Microsoft.SqlServer.Types all forms that do not utilize tables that have a column defined as Hierarchyid type work. All forms that utilize the hierarchyid datatype from a given table fail.
Additionally, if I do utilize the older version (10.0) of the dll the application works in the development environment but the one click deployment fails.
It seems strange to me that the newer version of the dll would not allow the use of the hierarchyid, I see other posts on the web with similar issues but more to the spatial types within the dll. Any additional thoughts, suggestions or guidance would be greatly appreciated. Again thank you for your suggestion, advice and help.