When I enter data that exceeds the column length it won't throw an exception in ASP.NET

Question

The question is simple.

I have a column in my database of data type NVARCHAR(20) .. so when I try to enter a data in this column that's for example contains 22 characters, it just ignores the last 2 characters instead of Throwing an exception!

Is this is normal ? .. and how to secure the database from such an issue ?

P.S: Of course I use validation controls and server validation, but how do I secure the database, is there's some kind of an advanced constraint more than just specifying the column's length, so it throws an exception and not accept the entered date ??

---

Edit

try
{
    using (SqlConnection conn = new SqlConnection(ConnStr))
    {
        string Command = "SET NOCOUNT ON; INSERT INTO [Countries] (CountryName, IsVisible) VALUES (@Name, @IsVisible);";

        using (SqlCommand comm = new SqlCommand(Command, conn))
        {
            comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 20);
            comm.Parameters["@Name"].Value = Name;

            comm.Parameters.Add("@IsVisible", System.Data.SqlDbType.Bit);
            comm.Parameters["@IsVisible"].Value = IsVisible;

            conn.Open();
            comm.ExecuteNonQuery();

            return "Successfully added " + Name + " to the countries.";

        }
    }
}
catch (SqlException SqlEx)
{
    string ErrorMessage = "";

    for (int i = 0; i < SqlEx.Errors.Count; i++)
    {
        ErrorMessage += SqlEx.Errors[i].Number + " : " + SqlEx.Errors[i].Message + "\n";
    }
    return ErrorMessage;
}

Well this is the the code I'm using, and btw I just tried to insert a bigger data than the column's length directly from the Sql Management Studio and it actually displayed the message you were just describing!

Answer 1

The problem is between these two lines.

   comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 20);
   comm.Parameters["@Name"].Value = Name;

SQL Server NEVER gets to see more than 20 chars. .Net is doing the truncation.

I can't remember the documentation for SQLParameter, a Google search was faster.

http://www.eggheadcafe.com/software/aspnet/30873895/sqlparameter-question.aspx

Now if you DID specify the SqlParam's length to be 40, then .NET will automatically truncate the string to 40 characters for you.. and no error will be raised (but you may loose data and not know it).

Answer 2

There are several areas that will truncate the data prior to it reaching the table without generating errors.

As @Richard pointed out for your specific case it is being truncated by ADO.

If you called a stored procedure that had a varchar(20) parameter and passed it 22 characters of data, the parameter processing engine in SQL server would truncate it as well.

However, if you wrote an insert state that tried to directly stuff 22 characters into a varchar(20) column, then you would see the error.

Basically, the parameter processing piece is silently "fixing" it. Whereas if you get rid of the parameters then it will fail. Which is why this question really is an exact duplicate of the one @gbs referenced.

Obviously, getting rid of the parameters is FAR from ideal.