How can I resolve System.Data.SqlClient.SqlException: String or binary data would be truncated

Question

When I execute the code this error message occurs:

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(str);

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Companyregister";
    cmd.Parameters.AddWithValue("@Company_name", txtname.Text);
    cmd.Parameters.AddWithValue("@Register_no", txtreg_no.Text);
    cmd.Parameters.AddWithValue("@Type", DropDownList1.Text);
    cmd.Parameters.AddWithValue("@Address", txtadrs.Text);
    cmd.Parameters.AddWithValue("@Email", txtemail.Text);
    cmd.Parameters.AddWithValue("@Contact_no", txtphone.Text);

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    Response.Write("Register succesful");
}

Error:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: String or binary data would be truncated.

The statement has been terminated.

What is wrong in my code or my stored procedure that causes this exception and how do I solve that?

Your one of the parameter must have more length than specified in the stored procedure. — Sagar, Sep 06 '16 at 06:46
Check the max length of your columns, you might be trying to save a string with 11 characters to a column that takes 10 characters — Dumisani, Sep 06 '16 at 06:47
Possible duplicate of [string or binary data would be truncated error message](http://stackoverflow.com/questions/23491683/string-or-binary-data-would-be-truncated-error-message) — rene, Sep 06 '16 at 06:53
Possible duplicate of [SQL Server String or binary data would be truncated](https://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated) — spottedmahn, Sep 10 '19 at 21:02

Jeroen van Langen · Answer 1 · 2016-09-06T06:52:59.143

One or more input values are longer than the specified column length. Either a column is too small or your input is too long.

You could fix this two ways:

Pass the input always trucated to the SQL

cmd.Parameters.AddWithValue("@Company_name", txtname.Text.Substring(0, COLUMN_LENGTH));
Limit your input fields.(textbox max length) which I prefer, because the user inputs is never changed when the data is stored. Also the user is alerted on the maximum input size.

score 0 · Answer 2 · answered Sep 06 '16 at 06:47

0

You are trying to insert a data (string or binary) longer than the maximum length possible for the target field.

answered Sep 06 '16 at 06:47

salik latif

265
2
5

You can increase field maximum length from database or can set textbox maximum length similar to database field maximum length – salik latif Sep 06 '16 at 06:58
Nice one. Here's a +1 for you :) – MickyD Sep 06 '16 at 08:56

score -1 · Answer 3 · answered Sep 06 '16 at 06:46

-1

Check the size of columns/fields in the database table. I guess, one of the table fields is not defined big enough to hold the passed values.

answered Sep 06 '16 at 06:46

Rohit Garg

483
2
11

How can I resolve System.Data.SqlClient.SqlException: String or binary data would be truncated

3 Answers3