MaxLength for varchar column

To store a string in a varchar field the maximum length of string is needed to avoid a data-truncation error.

The following code only returns -1 if the column is varchar or ntext:

dr.Table.Columns(Ind).MaxLength

where Ind is the index of the required column.

dr.Table.Columns(Ind).DataType.ToString only returns "System.String", if the column is varchar or ntext.

What is the equivalent of the VB6 Fd.DefinedSize (where Fd is ADODB.Field)?
MTechoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grayeCommented:
By default, ADO.Net the DataAdapter's Fill() method takes a "short cut" and only pulls the bare bones information required to populate the DataTable.  

To get the fully populated schema (which includes the column widths) just add the following:

           DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

MTechoAuthor Commented:
Thank you for that, Graye.

However, dr.Table.Columns(Ind).DataType.ToString still returns "System.String", if the column is varchar or ntext.

Is it possible to get the Data Type from the table in the SQL database?
grayeCommented:
From the perspective of the DataTable, the answer of "System.String" is absolutely correct.   The DataTable class (actually the DataColumn class, but who cares) does know much about the "native" datatypes in the underlying database.  The from ADO.Net's perspective there is no such thing as "varchar" or "ntext".

So, to get the underlying data type from the database, you have to ask for the "raw" schema.  This is typically a database-dependent operation.  For example, using SQL Server 2000, you'd issue the following query:

SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

The returned DataTable would contain the COLUMN_NAME and DATA_TYPE for the Customers table

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.