Microsoft KB Archive/288444

= BUG: Problems Reading and Writing Dynamic Properties of ADOX Column When You Use SQLOLEDB =

Article ID: 288444

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q288444



SYMPTOMS
When you connect to SQL Server using the SQL Server OLE DB Provider, if you use ActiveX Data Objects Extensions for DDL and Security (ADOX), the ADOX Column object exposes six dynamic properties in its Properties collection: Autoincrement, Default, Fixed Length, Nullable, Primary Key, and Unique.

However, any attempt to read one of these properties on an existing Column in an existing Table generates the following error message:

Error 3251, &quot;Object or provider is not capable...&quot;

You may also encounter problems when you try to set these properties on a new Column unless you follow specific steps, which are described in the &quot;More Information&quot; section.



RESOLUTION
Because you cannot use ADOX to read these Column properties, you can use an ADO Connection or SQL Server Query Analyzer to run the equivalent T-SQL statements to query object properties; or you can use SQL Server Enterprise Manager to view the properties manually.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
According to the Microsoft Data Access Components (MDAC) Readme file, the properties of existing Column objects in existing Tables are read-only. However, as a result of the bug discussed in this article, the above-mentioned properties cannot be read for existing Columns. You may also encounter problems when you try to set these properties on a new Column, unless you follow the specific steps, as described below.

How to Read Properties from Existing Column in an Existing Table
 Create a new Standard EXE project in Visual Basic. Form1 is created by default. From the Project menu, click References, and then click '''Microsoft ADO Ext. 2.x for DDL and Security and Microsoft ActiveX Data Objects 2.x Library'''.  Paste the following code into a Form1, and adjust the SQL Server connection parameters as necessary.

Note You must change User ID = and password = to the correct values before you run this code. Make sure that  has the appropriate permissions to perform this operation on the database. Dim cn As ADODB.Connection Dim cat As ADOX.Catalog Set cn = New ADODB.Connection With cn   .Provider = &quot;SQLOLEDB&quot; .ConnectionString = &quot;Data Source=(local);Initial Catalog=Northwind;User ID=;Password= ;&quot; .Open End With Set cat = New ADOX.Catalog Set cat.ActiveConnection = cn Debug.Print cat.Tables(&quot;Customers&quot;).Columns(&quot;CustomerID&quot;).Properties(&quot;Primary Key&quot;).Value 

This sample attempts to determine whether the CustomerID column in the Customers table is part of the table's Primary Key and should return True. In fact, it causes the above-mentioned error message.

How to Set Properties on New Column in New Table
 Create a new Standard EXE project in Visual Basic. Form1 is created by default.</li> From the Project menu, click References, and then click '''Microsoft ADO Ext. 2.x for DDL and Security and Microsoft ActiveX Data Objects 2.x Library'''.</li>  Paste the following code into a Form1, and adjust the SQL Server connection parameters as necessary.

Note You must change User ID =<UID> and password = to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database. Dim cn As ADODB.Connection Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim col As ADOX.Column Set cn = New ADODB.Connection With cn   .Provider = &quot;SQLOLEDB&quot; .ConnectionString = &quot;Data Source=(local);Initial Catalog=Northwind;User ID=<UID>;Password= ;&quot; .Open End With Set cat = New ADOX.Catalog Set cat.ActiveConnection = cn Set tbl = New ADOX.Table tbl.Name = &quot;ADOXTest&quot; Set col = New ADOX.Column With col .Name = &quot;testing&quot; .Type = adInteger End With tbl.Columns.Append col tbl.ParentCatalog = cat    '<--- This is the important line! col.Properties(&quot;Default&quot;).Value = 1 cat.Tables.Append tbl </li></ol>

This sample specifies the Default value for new column. If you omit the line that specifies the table's ParentCatalog property, the last line of code generates the above-mentioned error message.

<div class="references_section">