Click here to Skip to main content
Click here to Skip to main content

Dynamically set TextBox.MaxLength based on SQL column size

, 25 Jan 2007
Rate this:
Please Sign up or sign in to vote.
Dynamically set TextBox.MaxLength based on the SQL column size.

Introduction

Have you ever tried to update a varchar in a SQL Server table when the size of the string is longer than the column size? It does not work… How is it normally solved? You specify:

myEditBox.MaxLength = 100;

What if you SQL developer changes the column size but does not let you know?

Solution

public class SQLHelper
{
    static public int GetSize(string csConnectionString, string csTableName, 
                              string csColumnName, int iDefault)
    {
        int iSize = iDefault;
        String csCommand =
            @"select CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = @TABLE_NAME AND COLUMN_NAME = @COLUMN_NAME";
        try
        {
            SqlConnection pConn = new SqlConnection(csConnectionString);
            pConn.Open();

            SqlCommand pCommand = new SqlCommand(csCommand, pConn);
            pCommand.Parameters.AddWithValue("@TABLE_NAME", csTableName);
            pCommand.Parameters.AddWithValue("@COLUMN_NAME", csColumnName);
            SqlDataReader pReader = pCommand.ExecuteReader();
            if (pReader.Read())
            {
                object pSize = pReader.GetValue(0);
                if (pSize != DBNull.Value)
                {
                    iSize = Convert.ToInt32(pSize);
                }
            }
            pReader.Close();
            pConn.Close();
        }
        catch (Exception em)
        {
            MessageBox.Show(em.Message, "SQL Error");
        }
        return iSize;
    }


    static public int GetSize(string csConnectionString, 
                      string csTableName, string csColumnName)
    {
        return GetSize(csConnectionString, csTableName, csColumnName, -1);
    }
}

Usage

myTextBox.MaxLength = SQLHelper.GetSize(ConnectionString, TableName, ColumnName);

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Fiwel
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
GeneralUsing DataAdapter.FillSchema() Pinmemberloudenvier3-Jul-07 9:30 
Generalan idea... PinmemberESTANNY25-Jan-07 10:26 
GeneralRe: an idea... PinmemberAlexandru Lungu25-Jan-07 11:47 
GeneralRe: an idea... PinmemberFiwel25-Jan-07 12:26 
GeneralRe: an idea... PinmemberDBuckner25-Jan-07 14:05 
GeneralRe: an idea... PinmemberFiwel26-Jan-07 5:27 
GeneralRe: an idea... Pinmemberloudenvier3-Jul-07 8:00 
GeneralRe: Using caching PinmemberJcmorin25-Jan-07 15:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 25 Jan 2007
Article Copyright 2007 by Fiwel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid