• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

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

0
JeremyHigginson
Asked:
JeremyHigginson
  • 4
  • 2
1 Solution
 
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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now