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

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

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;

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

CREATE TABLE [dbo].[tmp_ms_xx_tbl_UserEmp] (

[id] INT IDENTITY (1, 1) NOT NULL,

[Name] VARCHAR (50) NULL,

[Country] VARCHAR (50) NULL

);

23 June 2015, admin
C#
Comments: (0)

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

No Comments »

Leave a comment

New posts