Get field length from Database

[Guest]

How can i get the maximum allowed length for a field in a SQL database?

ie the value defined when creating a text field in a table, in a SQL database.

As i want to set the max length of a text field on an ASP.NET page to this
value

Cheers

 

[John Timney \( MVP \)]

you should be able to get it by reading the schema from the database, the
example below is for access2003 but should get you started

http://aspalliance.com/542

--
Regards

John Timney
ASP.NET MVP
Microsoft Regional Director

 

[Karl Seguin]

Check out:
http://msdn.microsoft.com/library/d...ientsqldatareaderclassgetschematabletopic.asp

DataTable dt = dr.GetSchemaTable()

Karl

 

[Guest]

Sorry, but im a bit of a newb with ASP.NET, how could i utilise this to get
the field length?

 

[Karl Seguin]

Dim connection As SqlConnection
Dim command As SqlCommand
Dim dr As SqlDataReader
Try
connection = New SqlConnection("Data Source=SERVER;Initial
Catalog=DATABASE;User Id=USER;Password=PASSWORD;")
command = New SqlCommand("SELECT Id, UserName FROM Users", connection)
connection.Open()
dr = command.ExecuteReader()
Dim dt As DataTable = dr.GetSchemaTable()

Finally
If Not connection Is Nothing Then
connection.Dispose()
End If
If Not command Is Nothing Then
command.Dispose()
End If
If Not dr Is Nothing Then
dr.Close()
End If

End Try


dt will have 2 rows (one fr Id and one for username), and it'll have 17
columns (as defined at
http://msdn.microsoft.com/library/d...emdataidatareaderclassgetschematabletopic.asp)

so if you wanted to know the size of the UserName field, you could do:

dt.Rows(1)(2) the 2nd row (Username) and the 3rd column (size)
or
dt.Rows(1)("ColumnSize")

Karl