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
57 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:murbro
  • 2
  • 2
4 Comments
 
LVL 33

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:murbro
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 33

Accepted Solution

by:
it_saige earned 500 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:murbro
ID: 41728851
Thanks very much. I appreciate the time taken to help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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