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!

Normally this would throw an exception saying the data would be truncated. You should post your code & schema and maybe someone can help point out the problem. — Brook, Mar 29 '11 at 01:33
@Brook. Looks like the problem with the code! .. please check my edit! — Mazen Elkashef, Mar 29 '11 at 01:41
dup: http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures — gbs, Mar 29 '11 at 01:41
@gbs, but I have the parameter length the same as the column length in the database! .. I don't think it's exactly the same..! — Mazen Elkashef, Mar 29 '11 at 01:45
Maybe the fact that you are specifying the max lenght at the parameter is, for some reason, supressing the exception. Have you already tried to insert the same data not using parameters? — Andre Pena, Mar 29 '11 at 01:47
@Ciwee. Myabe! .. I'll try but if this is the issue I still dunno what to do ! =) — Mazen Elkashef, Mar 29 '11 at 01:49
@Ciwee. You was right, and I didn't I could just remove the length from the Parameter.Add() method. — Mazen Elkashef, Mar 29 '11 at 01:55
Just Add validation...before saving an information to database... — Crimsonland, Mar 29 '11 at 01:58
@Crimsonland. of course I add validations both on the client and server side. but for a best practice I was advised to let the database to be able to defend it self if somehow the data was directly added to the database (for example you might after a year forget that your database isn't protected and just add some data through the sql server directly or add a new page that deals with the same table and forgot to validate it properly). — Mazen Elkashef, Mar 29 '11 at 02:08

score 9 · Accepted Answer · answered Mar 29 '11 at 01:49

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).

Wow, I knew there's a simple explanation to what's happening. I just removed the column length from the parameter in the ado.net code and it just worked. it through the exception and everything :D — Mazen Elkashef, Mar 29 '11 at 01:54

score 2 · Answer 2 · edited Mar 29 '11 at 11:31

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.

edited Mar 29 '11 at 11:31

Martin Smith

438,706
87
741
845

answered Mar 29 '11 at 01:55

NotMe

87,343
27
171
245

@Chris Lively. +1 Thanks for painting a whole picture of the situation .. and of course I won't sacrifice the security and get rid of the parameters. but I just used this >> "comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar);" what do you think ? – Mazen Elkashef Mar 29 '11 at 02:00
@IKashef: I think that's a perfectly valid solution. – NotMe Mar 29 '11 at 02:11
It was @gb **s** that referenced the dupe, which was answered by @gb **n**! – Martin Smith Mar 29 '11 at 11:35
@Martin. The two questions and answers talks about the same subject and the answers and the questions are so close. but StackOverflow only consider a question is a duplicate if it's an exact duplicate and neither the question nor the answer. do you to tell me that a noob would get a single benefit from the refered question by @gb!!? .. I'm sorry no :) .. I'm not that good and I really didn't get a single word that could benefit me in my ADO.NET Code adjustment to avoid the mistake. and it was provided here in the most simple way ever plus a couple of answers that pointed out great points! – Mazen Elkashef Mar 29 '11 at 12:26
@Martin: Thanks for fixing that. I got him confused with another member here. – NotMe Mar 29 '11 at 14:18

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

2 Answers2