Retrieve Data Type Length

[Chase]

Hi,

I need to get the size/length of a data type of a field declared in SQL (e.g. varchar(500)) because I'm creating textboxes dynamically and i want the size of the textbox to correspond to the size of the datatype.

The farthest I've gotten is DataTable.Columns.DataType and it returns the datatype in VB.net(System.String), not in SQL(varchar).

Thanks in advance, appreciate it!

 

[zekeman]

Have you tried something like this:

            Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(selstr, dBaseConnection)
            Dim dBaseDataReader As System.Data.OleDb.OleDbDataReader = dBaseCommand.ExecuteReader(CommandBehavior.SingleResult)
            Dim schemaTable As DataTable = dBaseDataReader.GetSchemaTable()

 

[Chase]

Thanks for the reply zekeman.

Im trying it out right now but I can't seem to find a property which would return the size/length of the datatype.

Thanks again! Your help is very much appreciated.

 

[zekeman]

Private Sub GetTableSchema()
        
        [COLOR="Green"]'below is the only way I know[/COLOR]  
        Dim selstr As String = "SELECT * FROM NOTES WHERE NOTEID = 1"
        Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(selstr, ADODATAFILENEW)
        Dim dBaseDataReader As System.Data.OleDb.OleDbDataReader = dBaseCommand.ExecuteReader(CommandBehavior.SingleResult)
        Dim schemaTable As DataTable = dBaseDataReader.GetSchemaTable()
        Dim row As DataRow
        Dim column As DataColumn
        ListBox1.Items.Clear()
        Dim intCol As Integer

        For Each row In schemaTable.Rows
            intCol = 0
            For Each column In schemaTable.Columns

                If intCol = 2 Then
                    ListBox1.Items.Add("String Len is: " & row(column).ToString)
                Else
                    ListBox1.Items.Add(row(column).ToString)
                End If
                intCol += 1
            Next
        Next
    End Sub

hope this helps

 

[Chase]

Hi zekeman thanks for the help.

Yesterday we were working on this and we were able to get the length of varchar datatypes only by using FillSchema then using MaxLength. Other datatypes would return something weird, like int would return -1, and text would return 245754 something.

We will try to use your code and see how it would fair. Thanks again!

 

[cjard]

Hi,

I need to get the size/length of a data type of a field declared in SQL

What RDBMS?

The farthest I've gotten is DataTable.Columns.DataType and it returns the datatype in VB.net(System.String), not in SQL(varchar).

That's because a datatable is nothing to do with a database

 

[cjard]

Incidentally, I had a similar trick in one of my apps, whereby when the form is opened, I loop through all the bound controls, looking for textboxes,and I set their length to the MaxLength of the underlying datatable column to which they are bound.. I have this code available if you would like to see it

 

[Chase]

yes i would like to view your code. it would be of great help!

 

[cjard]

    Public Sub AutoSetBoundControlsMaxLength(ByVal bs As BindingSource, ByVal baseDT As DataTable)
        For Each binding As Binding In bs.CurrencyManager.Bindings
            If TypeOf binding.Control Is TextBoxBase Then
                DirectCast(binding.Control, TextBoxBase).MaxLength = baseDT.Columns(binding.BindingMemberInfo.BindingField).MaxLength
            End If
        Next binding
    End Sub