none
getting MS Access DB column properties in code RRS feed

  • Question

  • I am trying to get attributes of a field in a MS Access DB with an ADO.net connection, however, the returned values are not correct. 

     

    After getting the DataColumn object from the DataTable, every time I call the .DefaultValue method, Data.DBNull is returned regardless of the actual default value.  Likewise, the .MaxLength will always return -1.

     

    Is there another way to get this information, or is there possibly something wrong with the code itself?

     

    'create DB connection

    mobjConnection = New Data.OleDb.OleDbConnection(mobjConnStrBuilder.ConnectionString)

    mobjConnection.Open()

     

    'configure adapter

    mobjAdapter = New Data.OleDb.OleDbDataAdapter("SELECT * FROM " & mstrTableName, mobjConnection)

     

    'fill data set

    mobjDataSet = New Data.DataSet()

    mobjAdapter.Fill(mobjDataSet, TableName)

     

    'set up Data Table

    Dim objDataTable As Data.DataTable

    objDataTable = mobjDataSet.Tables(TableName)

     

    Dim strDefaultValue As String = objDataTable.Columns(0).DefaultValue

    Wednesday, August 22, 2007 4:12 PM

Answers

  • If you want this information you will probably need to use GetOleDbSchemaTable instead. I've posted an example below:

     

    Code Snippet

    Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection

    Dim SchemaTable As DataTable

     

    DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\db1 XP.mdb"

     

    DatabaseConnection.Open()

     

    'Check specific column

    'SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _

    ' New Object() {Nothing, Nothing, "Table1", "ColumnName"})

     

    'Check all columns

    SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _

    New Object() {Nothing, Nothing, "Table1"})

     

    Dim RowCount As Int32

    For RowCount = 0 To SchemaTable.Rows.Count - 1

    Console.WriteLine(SchemaTable.Rows(RowCount)!CHARACTER_MAXIMUM_LENGTH.ToString)

    Console.WriteLine(SchemaTable.Rows(RowCount)!COLUMN_DEFAULT.ToString)

    Next RowCount

     

    'Shows what columns are available

    DataGrid1.DataSource = SchemaTable

     

    DatabaseConnection.Close()

     

     

     

    Wednesday, August 22, 2007 5:32 PM

All replies

  • Hi,

     

    Not sure if this works with Access (OLEDB) but there is a GetSchema() method that gets the properties of the tables in a database.

     

    Here is an example using SQLClient, shouldn't take much effort to change it to OLE, have a read on the GetSchemaTable() method on MSDN as there are a few options to control what gets returned, for example this code snippet might not pass back key information.

     

            Dim data As New DataTable
            Dim conn As New SqlConnection("Data Source=SQL2005;Initial Catalog=Northwind;Integrated Security=True")
            Dim sqlSelectCmnd As New SqlCommand("SELECT * FROM Categories", conn)
            Using conn
                conn.Open()
                Dim sqlReadr As SqlDataReader = sqlSelectCmnd.ExecuteReader(CommandBehavior.KeyInfo)
                Dim schema As DataTable = sqlReadr.GetSchemaTable()

     

                For Each row As DataRow In schema.Rows
                    Dim column As New DataColumn(row("BaseColumnName"), row("DataType"))
                    data.Columns.Add(column)
                Next
                data.Load(sqlReadr, LoadOption.OverwriteChanges)

                sqlReadr.Close()
                conn.Close()
            End Using

    Wednesday, August 22, 2007 5:25 PM
  • If you want this information you will probably need to use GetOleDbSchemaTable instead. I've posted an example below:

     

    Code Snippet

    Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection

    Dim SchemaTable As DataTable

     

    DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\db1 XP.mdb"

     

    DatabaseConnection.Open()

     

    'Check specific column

    'SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _

    ' New Object() {Nothing, Nothing, "Table1", "ColumnName"})

     

    'Check all columns

    SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _

    New Object() {Nothing, Nothing, "Table1"})

     

    Dim RowCount As Int32

    For RowCount = 0 To SchemaTable.Rows.Count - 1

    Console.WriteLine(SchemaTable.Rows(RowCount)!CHARACTER_MAXIMUM_LENGTH.ToString)

    Console.WriteLine(SchemaTable.Rows(RowCount)!COLUMN_DEFAULT.ToString)

    Next RowCount

     

    'Shows what columns are available

    DataGrid1.DataSource = SchemaTable

     

    DatabaseConnection.Close()

     

     

     

    Wednesday, August 22, 2007 5:32 PM
  •  

    This worked great, but I have one more question; in addition to the max length and default value, how would I determine if the field is required?

     

    Thanks,

    Thursday, August 23, 2007 1:42 PM