asked on

Validate text length on form field based on underlying table column size

I want to ensure that the text that a user enters in a form field does not exceed the current maximum size of the column in the SQL table where it will be stored. The column length may change over time but I do not want to have to change the source code each time; also I am intending to use this to validate several different fields. I have therefore decided to use a function that identifies the column length of a given column within a given table. However, I am not sure of the precise way of doing this as I do not understand enough about the structure of the SchemaTable object. I have provided some basic code with a couple of possible loops to get the ColumnSize value, but I know that neither of these works. There are quite a lot of references to the ScemaTable on various forums, but nothing that actually gives a clear solution to my problem. Also, I don't actually need to retrieve any rows from the table itself, I just need to know the max column size, but I can't see how to do this without utilising a standard SQL SELECT statement for the target table. Can someone please help me out?

Public Function GetColumnLength(ByVal targetTable As String, ByVal targetColumn As String) As Integer
        ' Comments  : Identify the maximum length of a specified column in a specified table.
        ' Parameters: targetTable  - the name of the table containing the column
        '             targetColumn - the name of the column within the target table
        ' Returns   : column length as integer value (if column is not a varchar, value is zero)
        ' Created   : V00.01.04 
        ' Modified  : 
        '
        ' --------------------------------------------------

        Dim dbCommand As SqlClient.SqlCommand
        Dim dbScan As SqlClient.SqlDataReader
        Dim sqlText As String
        Dim schemaTable As New DataTable
        Dim schemaRow As DataRow
        Dim columnName As DataColumn
        Dim targetColumnSize As Integer = 0

        sqlText = "SELECT * FROM " & targetTable
        dbCommand = New SqlClient.SqlCommand(sqlText, DbSqlConnection)
        dbScan = dbCommand.ExecuteReader()
        schemaTable = dbScan.GetSchemaTable
        dbCommand = Nothing

        For i = 0 To schemaTable.Columns.Count - 1
            If schemaTable.Columns(i).ColumnName = targetColumn Then
                targetColumnSize = schemaTable.Columns(i).columnsize
            End If
        Next

        For Each columnName In schemaTable.Columns
            If columnName.ColumnName = targetColumn Then
                targetColumnSize = columnName.Size
            End If
        Next

    Return targetColumnSize

    End Function

Open in new window

ASKER CERTIFIED SOLUTION

Shannon_Lowder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JeremyHigginson

ASKER

Brilliant - thanks - once I removed the double dashes from the first three columns (which initially caused a read error within VB) it worked fine.

JeremyHigginson

ASKER

Final code below:

Public Function GetColumnLength(ByVal targetTable As String, ByVal targetColumn As String) As Integer
        ' Comments  : Identify the maximum length of a specified column in a specified table.
        ' Parameters: targetTable  - the name of the table containing the column
        '             targetColumn - the name of the column within the target table
        ' Returns   : column length as integer value (if column is not a varchar, value is zero; if varchar is 
        '             'MAX', value is -1)
        ' Created   : V00.01.04 
        ' Modified  : 
        '
        ' --------------------------------------------------

        Dim sqlText As String
        Dim dbInfo As SqlClient.SqlDataReader = Nothing
        Dim targetColumnSize As Integer = 0

        sqlText = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, " & _
            "CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS " & _
            "WHERE TABLE_NAME = " & DBText(targetTable) & " " & _
            "AND DATA_TYPE IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR')"
        Debug.WriteLine(sqlText)
        dbInfo = CreateReader(sqlText)
        Do While dbInfo.Read
            If CStr(dbInfo!COLUMN_NAME) = targetColumn Then
                targetColumnSize = CInt(dbInfo!CHARACTER_MAXIMUM_LENGTH)
            End If
        Loop
        dbInfo.Close()
        dbInfo = Nothing

        Return targetColumnSize

    End Function

Open in new window

ElrondCT

This code is incomplete.

dbInfo = CreateReader(sqlText)

won't work. All three CreateReader methods that I see (at least in VB 2008), SqlXml.CreateReader, XmlSerializer.CreateReader, and XNode.CreateReader, don't take any arguments, and they don't return a SqlDataReader.

Further, DBText (line 18) is not defined. I'm not sure what its purpose is anyway; put a single quote on either side of the table name?

Too bad. I was hoping to use this code for a similar need I have.

JeremyHigginson

ASKER

CreateReader is a simple function that I use to perform the SQL read statement (see attached code). You can obviuosly substitute your own read command statements for the call to the function if you wish.

DBText is similarly a function that does what you suggest. Again, code attached.

Hope this helps.

Public Function DBText(ByVal origText As String) As String
        ' Comments  : Prepare a string for insertion in a SQL statement.
        ' Parameters:
        ' Returns   : SQL compliant text.
        ' Created   : V00.00.01 
        ' Modified  : 
        '
        ' --------------------------------------------------

        If (Trim(origText) = "") Then
            Return "NULL"
        Else
            Return "'" & Replace(origText, "'", "''") & "'"
        End If

    End Function

Open in new window

Public Function CreateReader(ByVal sqlText As String) As SqlClient.SqlDataReader
        ' Comments  : Given a SQL statement, return a data reader.
        ' Parameters:
        ' Returns   : Result of read.
        ' Created   : V00.00.01 
        ' Modified  : 
        '
        ' --------------------------------------------------

        Dim dbCommand As SqlClient.SqlCommand
        Dim dbScan As SqlClient.SqlDataReader

        'Try to run the statement. Note that no error trapping is done here. It is up to the calling 
        'routine to set up error checking.
        dbCommand = New SqlClient.SqlCommand(sqlText, DBSQLConnection)
        If Not (HoldTransaction Is Nothing) Then dbCommand.Transaction = HoldTransaction
        dbScan = dbCommand.ExecuteReader()
        dbCommand = Nothing
        Return dbScan

    End Function

Open in new window

ElrondCT

Thanks for providing the rest of the story.

JeremyHigginson

ASKER

Glad to be of help for once, rather than asking for it!!