Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table
Getting DataTypeName of each column of table Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Go Back  Xtreme Visual Basic Talk > > > Getting DataTypeName of each column of table


Reply
 
Thread Tools Display Modes
  #1  
Old 10-13-2013, 01:01 PM
eawedat eawedat is offline
Newcomer
 
Join Date: Mar 2012
Posts: 8
Default Getting DataTypeName of each column of table


Hi there ,
I have this code :

Code:
 
  Dim query As String = "show columns from " & table_name & " from " & db_name

        Dim connection As New MySqlConnection(cons)
        Dim cmd As New MySqlCommand(query, connection)

        connection.Open()

        Dim reader As MySqlDataReader
        reader = cmd.ExecuteReader()
        MessageBox.Show(reader.FieldCount)

For i As Integer = 0 To reader.FieldCount - 1
       MessageBox.Show(reader.GetDataTypeName(5))
Next
I want to show the field's type of each column of the table , the problem is that FieldCount is incorrect , I do have fields more than VB.NET shows me.

any solutions ?

thanks.
Reply With Quote
  #2  
Old 10-15-2013, 05:39 AM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

Hi,
I have done this a while ago, you should call GetSchema from your connection and choose what do you want to get, "Columns or Tables".
This code sample gets both in case you have multiple tables in your database.
Code:
 'fill in your connection string here
        Dim conn As new SqlConnection
        '
        Dim tablesTable As DataTable = conn.GetSchema("Columns")
        Dim databaseTables As DataTable = conn.GetSchema("Tables")

For Each row As DataRow In databaseTables.Rows
            If row.Item(2) IsNot "sysdiagrams" Then
                
                Dim DataBaseName As String = row(0).ToString
                Dim SchemaName As String = row(1).ToString
                Dim tableName As String = row(2).ToString
                Console.WriteLine("TableName: " & tableName)
                Dim table As New DataTable
                table.TableName = tableName

                Dim sql As String = "SELECT * FROM [" & DataBaseName & "].[" & SchemaName & "].[" & tableName & "]"
                Dim cmd As New SqlCommand(sql, conn)
                Dim da As New SqlDataAdapter(sql, conn.ConnectionString)
                da.Fill(table)

        'initialize a string builder
        Dim strBuilder As New StringBuilder
        'loop through the columns and append constant declaration for each one
        For Each col As DataColumn In table.Columns
            strBuilder.AppendLine("Column Name:" & col.ColumnName & " AS " & col.DataType.FullName)
            End If
        Next
'do what you want with the string builder. or you can use a list to store your dataTypes
Next
Cheers
Reply With Quote
  #3  
Old 10-15-2013, 06:49 AM
eawedat eawedat is offline
Newcomer
 
Join Date: Mar 2012
Posts: 8
Default

sgm,
I am using Access(*.MDB) file
not SQL-Server
Reply With Quote
  #4  
Old 10-15-2013, 07:57 AM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

I thought that you where using MySql! that's why I posted an SQL code so that it might be close enough.
Anyway here is the code for OleDB
notice the slight changes in the GetSchema method from the SqlClient version.


Code:
'use your connection string here 
 Dim conn As New OleDBConnection
        
        Dim tablesTable As DataTable = conn.GetSchema("Columns")
        Dim databaseTables As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                                                                   New Object() {Nothing, Nothing, Nothing, "TABLE"})
   
        For Each row As DataRow In databaseTables.Rows
            Dim sbClass As New StringBuilder
            Dim tableName As String = row(2).ToString
            Console.WriteLine("Table Name: " & tableName)
            Dim table As New DataTable
            Dim sql As String = "SELECT * FROM [" & tableName & "]"
            Dim cmd As New OleDbCommand(sql, conn)
            Dim da As New OleDbDataAdapter(sql, conn.ConnectionString)
            da.FillSchema(table, SchemaType.Source)

 'initialize a string builder
        Dim strBuilder As New StringBuilder
        'loop through the columns and append constant declaration for each one
        For Each col As DataColumn In table.Columns
            strBuilder.AppendLine("Column Name:" & col.ColumnName & " AS " & col.DataType.FullName)
            
        Next
'do what you want with the string builder. or you can use a list to store your dataTypes
Next
Reply With Quote
  #5  
Old 10-15-2013, 03:02 PM
eawedat eawedat is offline
Newcomer
 
Join Date: Mar 2012
Posts: 8
Default

It gave me tablename only .. :\ :/

Code:
 'use your connection string here 
        Dim conn As New OleDbConnection(con)
        conn.Open()


        Dim tablesTable As DataTable = conn.GetSchema("Columns")
        Dim databaseTables As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

        For Each row As DataRow In databaseTables.Rows
            Dim sbClass As New StringBuilder
            Dim tableName As String = row(2).ToString
            MessageBox.Show("Table Name: " & tableName)
            Dim table As New DataTable
            Dim sql As String = "SELECT * FROM " & tableName
            Dim cmd As New OleDbCommand(sql, conn)
            Dim da As New OleDbDataAdapter(sql, conn.ConnectionString)
            da.FillSchema(table, SchemaType.Source)

            'initialize a string builder
            Dim strBuilder As New StringBuilder
            'loop through the columns and append constant declaration for each one
            For Each col As DataColumn In table.Columns
                strBuilder.AppendLine("Column Name:" & col.ColumnName & " AS " & col.DataType.FullName)

            Next
            'do what you want with the string builder. or you can use a list to store your dataTypes
        Next
Reply With Quote
  #6  
Old 10-15-2013, 05:37 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

the message box line will show you the table name.
the last For sentence with the stringBuilder will have the dataType you need.
you can use a comboBox or ListBox to see the dataTypes
Reply With Quote
  #7  
Old 10-15-2013, 05:48 PM
eawedat eawedat is offline
Newcomer
 
Join Date: Mar 2012
Posts: 8
Default

Opppps ;( sorry , I did not pay attention ..

I changed it to
Code:
TextBox2.Text = TextBox2.Text & "Column Name:" & col.ColumnName & " AS " & col.DataType.FullName & vbCrLf
now , for Memo and Text , it shows me System.String
is there a way to show/get "EXACT" dataType column/field ?

like these ... -> http://www.w3schools.com/ado/ado_datatypes.asp
Reply With Quote
  #8  
Old 10-15-2013, 06:18 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

the message box line will show you the table name.
the last For sentence with the stringBuilder will have the dataType you need.
you can use a comboBox or ListBox to see the dataTypes
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
 
Getting DataTypeName of each column of table
Getting DataTypeName of each column of table
 
-->