字符串或二进制数据将被截断，插入到SQL Server表中

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();
    }

2 个解决方案

#1

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,

在您的数据库中，检查Full_Name，Email_Address，Phone_Number和Password字段的长度，假设它们被定义为varchar [10]，此错误意味着您尝试使用长度超过10个字符的字符串更新其值，

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.

检查存储在其中一个文本框中的值的长度：textBox2.Text，textBox3.Text，textBox4.Text或textBox5.Text。其中一个文本的长度比数据库模式接受的长。

Solution 1: Increase the columns size in the database.

解决方案1：增加数据库中的列大小。

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

解决方案2：使用Textbox.MaxLength限制用户的输入长度。

#2

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

再次停止使用字符串连接传递值，而不是使用参数化查询来避免SQL注入。使用SqlParameter类

Your parameterized INSERT statement should look like

您的参数化INSERT语句应如下所示

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

#1