locked
Reading columns from Access table into a list box RRS feed

  • Question

  • User356467727 posted

     Hello,

    Can anyone help me with this. i'm trying to pull all the column names from an Access table into a list box.

    Any suggestions?

    Thanks

    Elliott

     

    Tuesday, April 7, 2009 6:23 AM

Answers

  • User-821857111 posted

    DataTable dt;
    string connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=myDb.mdb;";
    using(OleDbConnection conn = new OleDbConnection(connect))
    {
        string[] restrictions = new string[4] { null, null, "<Name_Of_Table>", null };
        conn.Open();
        dt = conn.GetSchema("Columns", restrictions);
    }

    One of the Columns in the DataTable will be COLUMN_NAME.  That's the one you want to bind to a ListView. The Default Order will be alphabetical.  You can sort it on ORDINAL_POSITION ASC instead.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 7, 2009 6:43 AM

All replies

  • User1913767972 posted

    Not an expert with access as datasource, also didn't test the following solution, but can you please try this:

    1. Get one row from the table (or even zero rows) with query. Use DataAdapter and DataSet to achieve this.

    2. The DataSet should contain a datatable.

    3. Iterate through DataSet.Tables[0].Columns and get each column name (as far as I remember the Columns property is of type DataColumn and DataColumn should have property ColumnName), so

    foreach(DataColumn dc in dsMyDataSet.Tables[0].Columns)

    {

    string columnName = ds.ColumnName;

    }

    should work.

    4. Store the column names in List of string or whatever type you like.

     

    Hope I hepled out.

     

    P.S. in MSSQL you can use "select * from syscolumns" or something like that. It will be a good idea to search if there is a similar functionality for Access databases.

     

    Tuesday, April 7, 2009 6:39 AM
  • User-821857111 posted

    DataTable dt;
    string connect = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=myDb.mdb;";
    using(OleDbConnection conn = new OleDbConnection(connect))
    {
        string[] restrictions = new string[4] { null, null, "<Name_Of_Table>", null };
        conn.Open();
        dt = conn.GetSchema("Columns", restrictions);
    }

    One of the Columns in the DataTable will be COLUMN_NAME.  That's the one you want to bind to a ListView. The Default Order will be alphabetical.  You can sort it on ORDINAL_POSITION ASC instead.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 7, 2009 6:43 AM
  • User356467727 posted

     Cheers guys,

     

    i'm going to have a look at this tomorrow as something just come up. I'll be sure to give you my feedback .

    Thanks

    Elliott

    Tuesday, April 7, 2009 7:09 AM
  • User1913767972 posted

    I suggest you to use Mikesdotnetting solution, as mine was more like oldschool hack. While his was professional way to do thigs.

    Wednesday, April 8, 2009 4:08 AM