Creating a table with AllowZeroLength=True using adox in Net 2.0

[Roy Soltoff]

I am porting a project from VB6 to Net 2.0. One part of the project used
ADOX 2.7 to create a Jet 4.0 database and various tables. In some of the
tables, there are TEXT fields that are allowed to be empty. In VB6, a
statement of the form,
Col.Properties("Jet OLEDB:Allow Zero Length")=True
would do the job.

Using an imported ADOX reference, the Properties collection is ReadOnly and
no Add method is available. Thus, there appears to be no way to set a text
field to allow it to be empty.

Looking at the mechanism in using SQL and CREATE TABLE..., that also does
not seem to provide any means of setting the AllowZeroLength properyty of a
field to True.

Anyone come up with a DotNet way of accomplishing this? Following is an
exact partial set of statements used in the VB6 project - and obviously
without access to setting the properties, making a column AutoIncrement
would be impossible (JETAZL is a constant of the above mentioned string):

Set Tbl = New ADOX.Table
With Tbl
.Name = "Content"
Set .ParentCatalog = Cat
With .Columns
.Append "TrackingID", adInteger
With .Item(.Count - 1)
.Properties("AutoIncrement") = True
End With
.Append "RequestID", adVarWChar, 12
With .Item(.Count - 1)
.Properties(JETAZL) = True
End With

 

[Kevin Yu [MSFT]]

Hi Roy,

There is no additional methods in ADO.NET 2.0 to change the AllowZeroLength
property. The only way is to use ADOX. Although the Properties collection
is readonly, the items' values aren't. You can use .Value to change its
value. Here is an example.

cat.Tables("Table1").Columns("aaa").Properties("Jet OLEDB:Allow
Zero Length").Value = True

It's working fine on my machine. If anything is unclear, please feel free
to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Roy Soltoff]

That did it. But I couldn't set the property until after the column object
was added to the table and after the table was added to the catalog. In
other words, col.Properties(JETAZL).Value=True gave an error of the property
not in the collection until I moved the code to after I added the col to the
table and the table to the catalog and referenced the property though the
cat.tables(name).Properties(name).Value method that you suggested. Thanks
for the quick response.

I'm still thinking about converting the whole database functionality to SQL
Express 2005. But so far, my research into that has not shown a way to set
that kind of property for a column. Nullable, yes. But zero length strings,
no.

 

[Mike]

Roy,

You can assign to Properties after you set the column's parent catalog.

It looks like you've created a column object named col. So, all you have to
do is add the line
col.ParentCatalog = cat
then you can assign to Properties.

This precludes the need to add the column to the table and the table to the
catalog prior to setting Properties.

Of course, the column still needs to be added to the table and the table to
the catalog after setting Properties.

Mike Bennett

 

[Kevin Yu [MSFT]]

Hi Roy,

Yes, it is recommended to move to SQL Express 2005 from an Access database.
The SQL table allow zero-length string columns. You can just assign an
empty string to that column directly.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Hi,

I'm new to ADOX and having trouble getting this to work. Could you post the
code that you use to "set the property until after the column object
was added to the table and after the table was added to the catalog"? So
far I have:

ADOX.CatalogClass thisCatalog;
ADOX.TableClass thisTable;
try
{
thisCatalog = new ADOX.CatalogClass();
thisCatalog.ActiveConnection = strAccessConn + "Jet OLEDB:Engine
Type=5;"; // Type=5 => Access 2000 format

thisTable = new ADOX.TableClass();
thisTable.Name = "tblGroups";
thisTable.ParentCatalog = thisCatalog;

thisTable.Columns["UserIDs"].Properties["Jet OLEDB:Allow Zero
Length"].Value = true;

}
catch(Exception ex)
{
log.WriteToLog(log.FormatExceptionDescription("ADOX Error", ex));
}
finally
{
thisCatalog = null;
thisTable = null;
}