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
39 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 32

Expert Comment

by:it_saige
Comment Utility
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
Comment Utility
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 32

Accepted Solution

by:
it_saige earned 500 total points
Comment Utility
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
Comment Utility
Thanks very much. I appreciate the time taken to help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now