I was creating a simple tool to work with database table. When I coded a part of updating table records I got some errors. One of them was:

Fixing C# and GridView error - String or binary data would be truncated. The statement has been terminated

The code of updating is rather simple in the void GridView1_RowUpdating:

            GridViewRow row = GridView1.Rows[e.RowIndex];

            int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
            string name = (row.FindControl("lblName") as TextBox).Text;
            string country = (row.FindControl("lblCountry") as TextBox).Text;

            string sql = "UPDATE tbl_UserEmp SET Name=@Name, Country=@Country WHERE id=@id";
            using (SqlConnection con = new SqlConnection(connstr))
            {

                using (SqlCommand cmd = new SqlCommand(sql))
                {
                    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name;
                    cmd.Parameters.Add("@Country", SqlDbType.VarChar, 50).Value = country;
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                    try
                    {
                        cmd.Connection = con;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                    catch (Exception ex)
                    {
                        errLabel.Text = ex.Message.ToString() + "<br />"  + sql;
                    }
                }
            }
            GridView1.EditIndex = -1;

 

But when I pressed “Update” button, I got the error on screen:

String or binary data would be truncated. The statement has been terminated.

I spent a lot of time, but solution was easier. I used to check the maximum allowed length of my columns. So, when I created table, I have forgotten to set varchar lengths for Name and Country fields. And their lengths where "1", that’s why I couldn’t update my records. Of course, if I haven’t entered only 1 symbol.

Here’s the code for creating table:

CREATE TABLE [dbo].[tmp_ms_xx_tbl_UserEmp] (
    [id]      INT          IDENTITY (1, 1) NOT NULL,
    [Name]    VARCHAR (50) NULL,
    [Country] VARCHAR (50) NULL
);