Solved

Get field names from MS Access table

Posted on 2004-10-13
4
441 Views
Last Modified: 2010-04-23
Dear All

I wish to construct a function whereby I pass the table name and it retreives the field names, so that I can verify if a particular field name is existent in that particular table.

I found this piece of code on the net :-

        conn.Open()
        Dim SchemaTable As DataTable

        'Retrieve schema information about Table1 Primary Keys.
        SchemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, _
        New Object() {Nothing, Nothing, tableName})

        Dim RowCount As Int32
        For RowCount = 0 To SchemaTable.Rows.Count - 1
            Trace.Write(SchemaTable.Rows(RowCount)!PK_NAME.ToString)
            Trace.Write(SchemaTable.Rows(RowCount)!COLUMN_NAME.ToString)
        Next RowCount

        conn.Close()

however it was not retreiving any field names.  Sorry but I am quite new to VB.NET

Can you help me out please?

Thanks for your help and time

Johann
0
Comment
Question by:monfu
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:checoo
ID: 12295784
one way of achieveing this would be to use a SQL as follows

SELECT * FROM yourtablename WHERE 1=0

this query would return no results but if you create a dataset from it you can get the structure of the table in the dataset, then from the dataset you can obtain the columns
0
 

Author Comment

by:monfu
ID: 12295882
Hello Checoo

I tried this :-

    Function get_fields(ByVal tableName As String) As Boolean
        Dim comp As New Components
        Dim dst As New DataSet
        Dim strSQL As String = "SELECT * FROM " & tableName & " WHERE 1=0"

        dst = comp.get_dataset(strSQL)

        If dst.Tables("ds").Rows.Count > 0 Then
            Dim i As Integer
            For i = 0 To dst.Tables("ds").Rows.Count - 1
                If dst.Tables("ds").Rows(i)("categoryID") Then
                    Return True
                End If
            Next
        End If

    End Function

but its not working
0
 
LVL 9

Accepted Solution

by:
checoo earned 100 total points
ID: 12296485
the following code works for me, replace the appropriate connectionstring for your case

    Private Sub getcolumns(ByVal strTablename As String)
        Dim conn As New System.Data.OleDb.OleDbConnection
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        Dim strcolname As String
        Dim strsql As String = "SELECT * FROM " & strTablename & " WHERE 1=0"
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\StudentDetails.mdb;Persist Security Info=False"
        conn.Open()
        da = New System.Data.OleDb.OleDbDataAdapter(strsql, conn)
        da.Fill(ds, strTablename)
        For i = 0 To ds.Tables(strTablename).Columns.Count - 1
            strcolname = strcolname & ds.Tables(strTablename).Columns(i).ColumnName & " "
        Next
        MessageBox.Show("List of columns : " & strcolname)
    End Sub
0
 

Author Comment

by:monfu
ID: 12296589
Hey Checoo

Thanks, this code worked perfectly.

Thanks for your help

Johann
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

21 Experts available now in Live!

Get 1:1 Help Now