[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VB.net Problem with code to build DataTable: Value of type string cannaot be converted to System.Type

Posted on 2016-07-22
4
Medium Priority
?
73 Views
Last Modified: 2016-07-25
Hi. I  am trying to add columns to a DataTable where the column type is held in a variable called oDataType. With the following line I get the error: "Value of type string cannaot be converted to System.Type"

 dt.Columns.Add(oColumnName_InGrid, "typeof(" & oDataType & ")")
                 
I based my code on the following example
  'dt.Columns.Add("Row No", typeof(Int32))
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:it_saige
ID: 41725012
The second parameter of the Add method you are trying to use requires a Type, you are passing it a string.  So my first question is; What is oDataType and how is it declared?

Based on your answer we can proceed accordingly.

-saige-
0
 

Author Comment

by:Murray Brown
ID: 41725518
I get oDataType from the last column in the image below (DATA_TYPE). I loaded the grid with the SQL Table Schema of the database. Iwant to build the data table by looping through COLUMN_NAME and DATA_TYPE and using these variables

1
0
 
LVL 34

Accepted Solution

by:
it_saige earned 2000 total points
ID: 41727839
Sorry about the delay in response, it has been a long weekend.  Since the types you are dealing with are dbtypes and GetType() [the vb.net version of typeof()] deals strictly with CLR types, means that we must use another method in order to obtain the CLR type.  As such, people will generally write helper\mapper classes in order to map between the distinct types.  In your case, you also want to be able to find by string.  Here is a simple implementation that should fit the bill nicely:
''' <summary>Convert a base data type to another base data type; e.g. .NET Type to SqlDbType or .NET Type to DbType and vice-versa</summary>
''' <remarks>This class can be useful when you make conversion between types .The class supports conversion between .Net Type , SqlDbType and DbType.</remarks>
Public NotInheritable Class DbTypeMapper
	''' <summary>Struct DbTypeMapEntry</summary>
	Private Structure DbTypeMapEntry
		Private ReadOnly _type As Type
		Private ReadOnly _dbType As DbType
		Private ReadOnly _sqlDbType As SqlDbType

		''' <summary>Gets the Type.</summary>
		''' <value>The Type.</value>
		Public ReadOnly Property [Type]() As Type
			Get
				Return _type
			End Get
		End Property

		''' <summary>Gets the DbType.</summary>
		''' <value>The DbType.</value>
		Public ReadOnly Property [DbType]() As DbType
			Get
				Return _dbType
			End Get
		End Property

		''' <summary>Gets the SqlDbType.</summary>
		''' <value>The SqlDbType.</value>
		Public ReadOnly Property [SqlDbType]() As SqlDbType
			Get
				Return _sqlDbType
			End Get
		End Property

		''' <summary>Initializes a new instance of the <see cref="DbTypeMapEntry"/> struct.</summary>
		''' <param name="type">The Type.</param>
		''' <param name="dbType">The DbType.</param>
		''' <param name="sqlDbType">The SqlDbType.</param>
		Public Sub New([type] As Type, [dbType] As DbType, [sqlDbType] As SqlDbType)
			_type = [type]
			_dbType = [dbType]
			_sqlDbType = [sqlDbType]
		End Sub

		''' <summary>Returns a <see cref="System.String" /> that represents this instance.</summary>
		''' <returns>A <see cref="System.String" /> that represents this instance.</returns>
		Public Overrides Function ToString() As String
			Return String.Format("Type: {0}; DBType: {1}; SqlDbType: {2}", [Type].FullName, [DbType].ToString(), [SqlDbType].ToString())
		End Function
	End Structure

	Private Shared ReadOnly TypeMapEntries As List(Of Nullable(Of DbTypeMapEntry))

	Public Shared Sub PrintEntries()
		For Each entry In TypeMapEntries
			Console.WriteLine(entry)
		Next
	End Sub

#Region "Constructors"
	''' <summary>Initializes static members of the <see cref="DbTypeMapper" /> class.</summary>
	Shared Sub New()
		If TypeMapEntries Is Nothing Then
			TypeMapEntries = New List(Of Nullable(Of DbTypeMapEntry)) From _
				{ _
					New DbTypeMapEntry(GetType(Boolean), DbType.[Boolean], SqlDbType.Bit), _
					New DbTypeMapEntry(GetType(Byte), DbType.[Double], SqlDbType.TinyInt), _
					New DbTypeMapEntry(GetType(Byte()), DbType.Binary, SqlDbType.Image), _
					New DbTypeMapEntry(GetType(DateTime), DbType.DateTime, SqlDbType.DateTime), _
					New DbTypeMapEntry(GetType([Decimal]), DbType.[Decimal], SqlDbType.[Decimal]), _
					New DbTypeMapEntry(GetType(Double), DbType.[Double], SqlDbType.Float), _
					New DbTypeMapEntry(GetType(Guid), DbType.Guid, SqlDbType.UniqueIdentifier), _
					New DbTypeMapEntry(GetType(Int16), DbType.Int16, SqlDbType.SmallInt), _
					New DbTypeMapEntry(GetType(Int32), DbType.Int32, SqlDbType.Int), _
					New DbTypeMapEntry(GetType(Int64), DbType.Int64, SqlDbType.BigInt), _
					New DbTypeMapEntry(GetType(Object), DbType.[Object], SqlDbType.[Variant]), _
					New DbTypeMapEntry(GetType(String), DbType.[String], SqlDbType.VarChar),
					New DbTypeMapEntry(GetType(String), DbType.[String], SqlDbType.NVarChar) _
				}
		End If
	End Sub

	''' <summary>Prevents a default instance of the <see cref="DbTypeMapper"/> class from being created.</summary>
	Private Sub New()
	End Sub
#End Region

#Region "Methods"
	''' <summary>Convert db type to .Net data type</summary>
	''' <param name="dbType">The DbType to find.</param>
	''' <returns>Type.</returns>
	Public Shared Function ToNetType([dbType] As DbType) As Type
		Return Find([dbType]).Type
	End Function

	''' <summary>Convert TSQL type to .Net data type</summary>
	''' <param name="sqlDbType">The SqlDbType to find.</param>
	''' <returns>Type.</returns>
	Public Shared Function ToNetType([sqlDbType] As SqlDbType) As Type
		Return Find([sqlDbType]).Type
	End Function

	''' <summary>Convert TSQL type to .Net data type</summary>
	''' <param name="name">The name of the SqlDbType to find.</param>
	''' <returns>Type.</returns>
	Public Shared Function ToNetType(name As String) As Type
		Return Find(name).Type
	End Function

	''' <summary>Convert .Net type to Db type</summary>
	''' <param name="type">The type to find.</param>
	''' <returns>DbType.</returns>
	Public Shared Function ToDbType([type] As Type) As DbType
		Return Find([type]).DbType
	End Function

	''' <summary>Convert TSQL data type to DbType</summary>
	''' <param name="sqlDbType">The SqlDbType to find.</param>
	''' <returns>DbType.</returns>
	Public Shared Function ToDbType([sqlDbType] As SqlDbType) As DbType
		Return Find([sqlDbType]).DbType
	End Function

	''' <summary>Convert TSQL data type to DbType</summary>
	''' <param name="name">The name of the SqlDbType to find.</param>
	''' <returns>DbType.</returns>
	Public Shared Function ToDbType(name As String) As DbType
		Return Find(name).DbType
	End Function

	''' <summary>Convert .Net type to TSQL data type</summary>
	''' <param name="type">The type to find.</param>
	''' <returns>SqlDbType.</returns>
	Public Shared Function ToSqlDbType([type] As Type) As SqlDbType
		Return Find([type]).SqlDbType
	End Function

	''' <summary>Convert DbType type to TSQL data type</summary>
	''' <param name="dbType">The DbType to find.</param>
	''' <returns>SqlDbType.</returns>
	Public Shared Function ToSqlDbType([dbType] As DbType) As SqlDbType
		Return Find([dbType]).SqlDbType
	End Function

	''' <summary>Convert DbType type to TSQL data type</summary>
	''' <param name="name">The name of the DbType to find.</param>
	''' <returns>SqlDbType.</returns>
	Public Shared Function ToSqlDbType(name As String) As SqlDbType
		Return Find(name).SqlDbType
	End Function

	Private Shared Function Find([type] As Type) As DbTypeMapEntry
		Dim result = TypeMapEntries.FirstOrDefault(Function(x) x.HasValue AndAlso (x.Value.Type = (If(Nullable.GetUnderlyingType([type]), [type]))))
		If result Is Nothing Then
			Throw New ApplicationException("Referenced an unsupported Type")
		End If
		Return result
	End Function

	Private Shared Function Find([dbType] As DbType) As DbTypeMapEntry
		Dim result = TypeMapEntries.FirstOrDefault(Function(x) x.HasValue AndAlso (x.Value.DbType = [dbType]))
		If result Is Nothing Then
			Throw New ApplicationException("Referenced an unsupported Type")
		End If
		Return result
	End Function

	Private Shared Function Find([sqlDbType] As SqlDbType) As DbTypeMapEntry
		Dim result = TypeMapEntries.FirstOrDefault(Function(x) x.HasValue AndAlso (x.Value.SqlDbType = [sqlDbType]))
		If result Is Nothing Then
			Throw New ApplicationException("Referenced an unsupported Type")
		End If
		Return result
	End Function

	Private Shared Function Find(name As String) As DbTypeMapEntry
		Dim result = TypeMapEntries.FirstOrDefault(Function(x) x.HasValue AndAlso x.ToString().IndexOf(name, StringComparison.OrdinalIgnoreCase) > -1)
		If result Is Nothing Then
			Throw New ApplicationException("Referenced an unsupported Type")
		End If
		Return result
	End Function
#End Region
End Class

Open in new window

Your usage of the class above would be as such -
dt.Columns.Add(oColumnName_InGrid, DbTypeMapper.ToNetType(oDataType))

Open in new window


NOTE:  The preceeding mapping class does not include every single type for any and all types, dbtypes and/or sqldbtypes.  If you need other mappings, you will need to add them to the List.

-saige-
0
 

Author Comment

by:Murray Brown
ID: 41728851
Thanks very much. I appreciate the time taken to help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question