?
Solved

Get field names from MS Access table

Posted on 2004-10-13
4
Medium Priority
?
493 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
[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 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 400 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month10 days, 17 hours left to enroll

770 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