GetSchemaTable Does not return correct information ASP.Net / VB.Net

  • Thread starter Thread starter George
  • Start date Start date
G

George

All,

I am trying to get some schema information about columns in my MS
Access table. I attempt to use the GetSchemaTable method as
illustrated below:

Dim lobj_Connection As OleDbConnection
Dim lobj_DataReader As OleDbDataReader
Dim lobj_Command As OleDbCommand
Dim lobj_DataTable As DataTable
Dim lobj_Row As DataRow
Dim lobj_Column As DataColumn

Try
'Get the field information
lobj_Connection = New OleDbConnection


lobj_Connection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""d:\Documents and
Settings\gmcgd\My Documents\Softball\League2003.mdb"";"
lobj_Connection.Open()

lobj_Command = New OleDbCommand
lobj_Command.Connection = lobj_Connection
lobj_Command.CommandText = "Select * From League_Info"

lobj_DataReader =
lobj_Command.ExecuteReader(CommandBehavior.KeyInfo)

'Create the datatable
lobj_DataTable = lobj_DataReader.GetSchemaTable()

For Each lobj_Row In lobj_DataTable.Rows
For Each lobj_Column In lobj_DataTable.Columns
la_Fields(li_IX) = lobj_Column.ColumnName
la_FieldTypes(li_IX) = lobj_Column.GetType
la_FieldLengths(li_IX) = lobj_Column.MaxLength
Next
Next

--------------------------------------
Every time I run this the value of lobj_Column.ColumnName is
'ColumnName' and not the name of the column from my table. Any ideas
what I am doing wrong?

Thanks
George
 
Hi George:
George said:
All,

I am trying to get some schema information about columns in my MS
Access table. I attempt to use the GetSchemaTable method as
illustrated below:

Dim lobj_Connection As OleDbConnection
Dim lobj_DataReader As OleDbDataReader
Dim lobj_Command As OleDbCommand
Dim lobj_DataTable As DataTable
Dim lobj_Row As DataRow
Dim lobj_Column As DataColumn

Try
'Get the field information
lobj_Connection = New OleDbConnection


lobj_Connection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""d:\Documents and
Settings\gmcgd\My Documents\Softball\League2003.mdb"";"
lobj_Connection.Open()

lobj_Command = New OleDbCommand
lobj_Command.Connection = lobj_Connection
lobj_Command.CommandText = "Select * From League_Info"

lobj_DataReader =
lobj_Command.ExecuteReader(CommandBehavior.KeyInfo)

'Create the datatable
lobj_DataTable = lobj_DataReader.GetSchemaTable()

For Each lobj_Row In lobj_DataTable.Rows
For Each lobj_Column In lobj_DataTable.Columns
la_Fields(li_IX) = lobj_Column.ColumnName
la_FieldTypes(li_IX) = lobj_Column.GetType
la_FieldLengths(li_IX) = lobj_Column.MaxLength
Next
Next

--------------------------------------
Every time I run this the value of lobj_Column.ColumnName is
'ColumnName' and not the name of the column from my table. Any ideas
what I am doing wrong?

Thanks
George
Click to expand...

Try an inner loop that walks through the columns collection..
http://support.microsoft.com/default.aspx?kbid=310107
 
William,

I believe there already is an inner loop that goes through the
columns collection... :) :)
See the
For Each lobj_Column In lobj_DataTable.Columns
la_Fields(li_IX) = lobj_Column.ColumnName
la_FieldTypes(li_IX) = lobj_Column.GetType
la_FieldLengths(li_IX) = lobj_Column.MaxLength
Click to expand...
Next

loop inside the row loop :) Any other ideas?


Thanks
George
 
On 13 Apr 2004 12:21:04 -0700, (e-mail address removed) (George) wrote:

¤ All,
¤
¤ I am trying to get some schema information about columns in my MS
¤ Access table. I attempt to use the GetSchemaTable method as
¤ illustrated below:
¤
¤ Dim lobj_Connection As OleDbConnection
¤ Dim lobj_DataReader As OleDbDataReader
¤ Dim lobj_Command As OleDbCommand
¤ Dim lobj_DataTable As DataTable
¤ Dim lobj_Row As DataRow
¤ Dim lobj_Column As DataColumn
¤
¤ Try
¤ 'Get the field information
¤ lobj_Connection = New OleDbConnection
¤
¤
¤ lobj_Connection.ConnectionString =
¤ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""d:\Documents and
¤ Settings\gmcgd\My Documents\Softball\League2003.mdb"";"
¤ lobj_Connection.Open()
¤
¤ lobj_Command = New OleDbCommand
¤ lobj_Command.Connection = lobj_Connection
¤ lobj_Command.CommandText = "Select * From League_Info"
¤
¤ lobj_DataReader =
¤ lobj_Command.ExecuteReader(CommandBehavior.KeyInfo)
¤
¤ 'Create the datatable
¤ lobj_DataTable = lobj_DataReader.GetSchemaTable()
¤
¤ For Each lobj_Row In lobj_DataTable.Rows
¤ For Each lobj_Column In lobj_DataTable.Columns
¤ la_Fields(li_IX) = lobj_Column.ColumnName
¤ la_FieldTypes(li_IX) = lobj_Column.GetType
¤ la_FieldLengths(li_IX) = lobj_Column.MaxLength
¤ Next
¤ Next
¤
¤ --------------------------------------
¤ Every time I run this the value of lobj_Column.ColumnName is
¤ 'ColumnName' and not the name of the column from my table. Any ideas
¤ what I am doing wrong?

Each row in your DataTable represents the column information for a specific column of your table
schema.

You need to reference a specific row and column object to retrieve the value. The following will
display each one of the column property names and the associated values:

For Each lobj_Row In lobj_DataTable.Rows
For Each lobj_Column In lobj_DataTable.Columns
'Display the prop name and value.
Console.WriteLine(lobj_Column.ColumnName & " = " &
lobj_Row(lobj_Column).ToString())
Next
Console.WriteLine()
Next


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top