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

JeremyHigginsonAsked:
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.

Shannon_LowderCommented:
I think the following may help.  If you took the following query, and loaded it into a two dimensional array, you could then do a comparison in your vb code.  What you could then do is compare spin through the array, and match the first column against your field name.

When you find a match, you could compare the length of your form element's value against the last column.  If the length of the value is greater than that number, you can throw an exception.  

You can embed that logic inside your function, and I think you would be good to go.

Also, the second column could be used to detect when someone has entered characters that would not be stored in the database.  Since nvarchar would allow Greek, Cyrillic, etc. characters and varchar would not, it may be worth while to do that check too.


---
Shannon Lowder
Database Engineer
http://toyboxcreations.net
SELECT 
	--TABLE_CATALOG,
	--TABLE_SCHEMA,
	--TABLE_NAME,
	COLUMN_NAME,
	DATA_TYPE,
	CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
	table_name = 'TableName'
	and DATA_TYPE IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR');

Open in new window

0

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
JeremyHigginsonAuthor Commented:
Brilliant - thanks - once I removed the double dashes from the first three columns (which initially caused a read error within VB) it worked fine.
0
JeremyHigginsonAuthor Commented:
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

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ElrondCTCommented:
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.
0
JeremyHigginsonAuthor Commented:
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

0
ElrondCTCommented:
Thanks for providing the rest of the story.
0
JeremyHigginsonAuthor Commented:
Glad to be of help for once, rather than asking for it!!
0
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.