ADO.NET & ODBC.NET : Bugs???

[Mythran]

Sorry bout the xpost, was told to post here instead
---------------------------------------------------


Consider the following code, test and try it out:

Dim Conn As OdbcConnection
Dim Reader As OdbcDataReader
Dim Cmd As OdbcCommand
Dim Adapter As OdbcDataAdapter
Dim Table As System.Data.DataTable

'
' Create all of the objects to use.
Conn = New OdbcConnection()
Cmd = New OdbcCommand()
Adapter = New OdbcDataAdapter()
Data = New System.Data.DataSet()

'
' The connection object.
Conn.ConnectionString = "DSN=dbTest"
Conn.Open()

'
' The command object.
Cmd.Connection = Conn
Cmd.CommandType = CommandType.Text
Cmd.CommandText = "SELECT * FROM [Test]"

'
' Get the table's schema.

Reader = Cmd.ExecuteReader(CommandBehavior.SchemaOnly)
Table = Reader.GetSchemaTable()
Reader.Close()

'
' Set the data source for the data grid.
dgInfo.DataSource = Table

'
' Close and destroy all of the data access objects.
Conn.Close()
Table = Nothing
Reader = Nothing
Cmd = Nothing
Conn = Nothing



Now, what I have done is set up a few columns in this microsoft access database.
These columns are mapped as follows:

AutoNumberfield - AutoNumber - Primary key has been set on this field.
Textfield - Text
Memofield - Memo
Numberfield - Number
DateTimefield - Date/Time
Currencyfield - Currency
YesNofield - Yes/No
OLEObjectfield - OLE Object
Hyperlinkfield - Hyperlink
Decimalfield - Number - Field Size property set to Byte.


Now, when the code above runs on this table, the schema returned is not complete
or invalid altogether (maybe just incomplete).

The AutoNumberfield column should show in the IsKey property as True (or checked)
but it doesn't. I noticed this problem or SQL Server primary key's as well, the
primary keys are not checked in IsKey at all! How can I determine if the
specified field is or is part of the primary key for the specified table?

The Hyperlinkfield returns the exact same information as the memo field. How can
I tell these apart? I have checked the ExtendedProperties property while
running some tests using a data adapter and dataset but the extendedproperties
are NEVER set so I can't query those to find out.

The Decimalfield and Currencyfield both return the same datatype (Numeric) and
the Numberfield returns the property datatype (Integer). But notice that the
Decimalfield is in fact a Number while the field size has been set to Byte (or
decimal). Why does this return Number? How can I tell these two apart (Number
with Field Size set to something)?

Ok, enough questions already

Thanks in advance...

Mythran

 

[David Sceppa]

Mythran,

To retrieve the additional schema information (key columns,
base column names, etc.), you need to include
CommandBehavior.KeyInfo in your call to ExecuteReader:

Reader = Cmd.ExecuteReader(CommandBehavior.SchemaOnly Or _
CommandBehavior.KeyInfo)

I'd strongly recommend using the OLE DB .NET Data Provider
in conjunction with the Jet OLE DB Provider to work with an
Access database. Are you seeing the same issues with the
hyperlink and numeric fields if you connect to your Access
database that way?

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Mythran]

Mythran,

To retrieve the additional schema information (key columns,
base column names, etc.), you need to include
CommandBehavior.KeyInfo in your call to ExecuteReader:

Reader = Cmd.ExecuteReader(CommandBehavior.SchemaOnly Or _
CommandBehavior.KeyInfo)

I'd strongly recommend using the OLE DB .NET Data Provider
in conjunction with the Jet OLE DB Provider to work with an
Access database. Are you seeing the same issues with the
hyperlink and numeric fields if you connect to your Access
database that way?

Thanks David,

Aye, I do see the same issues, it even says I would be getting the same results
in the documentation (doesn't say exactly, but the documentation states the same
values for an Access 2000 database in both documentation screens).

Mythran

 

[Mythran]

I'm not sure what documentation you're referencing, but
based on your description it sounds like the behavior is by
design.

Did using KeyInfo help fetch the additional schema
information?

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

Aye, it worked Thanks so much.

Now, onto the next question I had...

How can I determine between Memo fields and Hyperlink fields as well as Currency
fields and Number fields that have the format of Decimal? (both return Provider
types of Decimal)...I can get really close but not completely there by checking
the size of the field (Currency is always 8 unless changed and Decimal is not
unless user specifically specifies 8).

Thanks,

Mythran