String or binary data would be truncated, insert to SQL Server table

Question

It's a runtime exception when I try to insert to a table, it was working before I added an update function.

    private void button1_Click(object sender, EventArgs e)
    {
        Random rndID = new Random();
        int id = rndID.Next(100, 1000);

        SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-IOHHC7K\SQLEXPRESS;Initial Catalog=CityAppDB;Integrated Security=True");

        SqlCommand command = new SqlCommand("insert into UsersTB(User_ID, Full_Name, Email_Address, Phone_Number, Password) 
             values('" + id.ToString() + "','" + textBox2.Text + "','" +
             textBox3.Text + "','" + textBox4.Text + "','" + 
             textBox5.Text + "')", connection);
        command.Connection.Open();

        if ((ISExisted(id)) && (CheckUserFullName(textBox2.Text)) && (IsValidEmail(textBox3.Text)) && (IsValidPhoneNumber(textBox4.Text)) && (IsValidPassword(textBox5.Text)))
        {
            command.ExecuteNonQuery();//String or binary data would be truncated
            MessageBox.Show("Added.");
            ShowAllUsers();
        }
        else
        {
            MessageBox.Show("Something wrong");
        }

        command.Connection.Close();
    }

What is datatype of User_Id and PhoneNumber in your database — parvezalam khan
– parvezalam khan, Commented Mar 24, 2017 at 7:46
Do you know that you can edit your question when you want to update something? And please, search about Sql Injection. Your code is a disaster waiting to happen — Steve
– Steve, Commented Mar 24, 2017 at 7:46

Koby Douek · Accepted Answer · 2017-03-24 07:53:42Z

2

This error means that you are trying to update a value which is too long for the database column definition.

In your database, check the length of your Full_Name, Email_Address,Phone_Number and Password fields, let's say they are definded as varchar[10], this error means that you are trying to update their value with a string longer than 10 characters,

Check the length of the values stored in one of your textboxes: textBox2.Text, textBox3.Text, textBox4.Text or textBox5.Text. One of them has a text which is longer than the database schema accepts.

Solution 1: Increase the columns size in the database.

Solution 2: Use Textbox.MaxLength to limit the user's input length.

edited Mar 24, 2017 at 7:53

answered Mar 24, 2017 at 7:46

Koby Douek

16.7k20 gold badges79 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Karam Haj Over a year ago

one second i want to check my values, thank you for helping

Koby Douek Over a year ago

Happy to help. Let me know if you need anything else.

Koby Douek Over a year ago

So increase the column size in the database and it will solve your broblem.

Karam Haj Over a year ago

if i used for example: textBox4.MaxLenght = 10; is also good for my situation?

Koby Douek Over a year ago

No Problem, happy to help. Accepting my answer as correct will be appreciated.

|

Rahul · Accepted Answer · 2017-03-24 08:02:03Z

Sole purpose of the error is: your column size is specified lesser than the value you are supplying which can only be known from your table structure.

Again stop passing values using string concatenation rather use parameterized query to avoid SQL Injection. Use SqlParameter class

Your parameterized INSERT statement should look like

    string query = "insert into UsersTB(User_ID,Full_Name,Email_Address,Phone_Number,Password) values(@User_ID,@Full_Name,@Email_Address,@Phone_Number,@Password)";
    SqlCommand command = new SqlCommand(query, connection);

    command.Parameters.Add("@User_ID",id.ToString());
    command.Parameters.Add("@Full_Name",textBox2.Text);
    command.Parameters.Add("@Email_Address",textBox3.Text);
    command.Parameters.Add("@Phone_Number",textBox4.Text);
    command.Parameters.Add("@Password",textBox5.Text);

Collectives™ on Stack Overflow

String or binary data would be truncated, insert to SQL Server table

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related