Inspecting the ADO

[M D]

cmd.CommandText = "SELECT TOP 1 * FROM " + target.Text;
conn.Open();
tableReader = cmd.ExecuteReader();
schemaTable = tableReader.GetSchemaTable();

where k is the primary key field:

schemaTable.Rows[k]["IsKey"]

does not show as true while

schemaTable.Rows[k]["AllowDBNull"]

does show as not nullable (and all the other schema values I've used are
also correct)

anyone know why?

the database is SQL2000
thx
md

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Boris]

M D wrote:

cmd.CommandText = "SELECT TOP 1 * FROM " + target.Text;
conn.Open();
tableReader = cmd.ExecuteReader();
schemaTable = tableReader.GetSchemaTable();

where k is the primary key field:

schemaTable.Rows[k]["IsKey"]

does not show as true while

schemaTable.Rows[k]["AllowDBNull"]

does show as not nullable (and all the other schema values I've used
are also correct)

anyone know why?

I had similar problems when playing around with ADO.NET and Microsoft
Access. As far as I remember Unique didn't work either if it wasn't set for
primary key columns.
My solution was to use DataTable.PrimaryKey and
OleDbConnection.GetOleDbSchemaTable() but I think the latter is OLE DB
specific and might not be available for SQL Server.

Boris

[Boris]

M D wrote:

cmd.CommandText = "SELECT TOP 1 * FROM " + target.Text;
conn.Open();
tableReader = cmd.ExecuteReader();
schemaTable = tableReader.GetSchemaTable();

where k is the primary key field:

schemaTable.Rows[k]["IsKey"]

does not show as true while

schemaTable.Rows[k]["AllowDBNull"]

does show as not nullable (and all the other schema values I've used
are also correct)

anyone know why?

I had similar problems when playing around with ADO.NET and Microsoft
Access. As far as I remember Unique didn't work either if it wasn't set for
primary key columns.
My solution was to use DataTable.PrimaryKey and
OleDbConnection.GetOleDbSchemaTable() but I think the latter is OLE DB
specific and might not be available for SQL Server.

Boris

[M D]

I think the only difference between
OleDbConnection.GetOleDbSchemaTable() and
OleDbDataReader.GetSchemaTable() is that the data reader already knows
what table it is scheming while the connection needs to be given a guid
of the table in question.

However, are you sure your solution worked?

The Get operations deliver a DataTable representing the schema of
another "DataTable" (eg name in target.Text.)

schemaTable.PrimaryKey yields the pkey of the schema not the target.
One must read the schemaTable DATA to locate information about the
target.

Do you mean that you turned the target table into a DataTable and got
its pkey? If that table had any size wasn't it expensive to load?

thx
md

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Boris]

M D wrote:

[...]
schemaTable.PrimaryKey yields the pkey of the schema not the target.
One must read the schemaTable DATA to locate information about the
target.
Ah, okay: I used OleDbDataAdapter.FillSchema() which initializes
DataTable.PrimaryKey.
Do you mean that you turned the target table into a DataTable and got
its pkey? If that table had any size wasn't it expensive to load?

I don't know how expensive it is to use FillSchema(). But as I couldn't find
any other way to get the primary key calling FillSchema() was the best I
could do?

Boris

[M D]

Yes, that is what I've got on the list to try next.

And, you've inspired me to the sql... group from the OleDb... group. I
want to see if the "IsKey" works if I go native.

thx
md

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[M D]

Here is the solution:

cmd.ExecuteReader(CommandBehavior.KeyInfo);

The Samaritan that shared this with me says the behavior is not included
in the default due to a performance hit.
thx
md

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[M D]

Here is the solution:

cmd.ExecuteReader(CommandBehavior.KeyInfo);

The Samaritan that shared this with me says the behavior is not included
in the default due to a performance hit.
thx
md

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Boris]

M D wrote:

Here is the solution:

cmd.ExecuteReader(CommandBehavior.KeyInfo);

The Samaritan that shared this with me says the behavior is not
included in the default due to a performance hit.

Ah, learnt something new. Thanks for sharing this information again. :)

Boris