Error in Updating record

[sania khan]

//When i click the update button to update record in database,i get following error : " String or binary data would be truncated. The statement has been terminated." plz help me to solve this problem
using System;
using System.Collections;
using System.Configuration;
using System.Web.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;

public partial class UpdateCatA : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {


        if (!this.IsPostBack)
        {
            FillList();
        }
    }
    private void FillList()
    {
        DropDownList1.Items.Clear();


        // Define the Select statement. // Three pieces of information are needed: the unique id and the first and last name.
        string selectSQL = "SELECT Ref#  FROM CatA";
        // Define the ADO.NET objects.

        SqlConnection myConn = new SqlConnection();

        myConn.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
        myConn.Open();
        SqlCommand cmd = new SqlCommand(selectSQL, myConn);
        SqlDataReader reader;
        try
        {
            myConn.Open();
            reader = cmd.ExecuteReader();
            // For each item, add the author name to the displayed list box text, and store the unique ID in the Value property.


            while (reader.Read())
            {


                ListItem newItem = new ListItem();
                string abc = reader["Serial_No"].ToString();

                newItem.Text = abc;


                DropDownList1.Items.Add(newItem);
            }
            reader.Close();

        }
        catch (Exception err)
        {
            //  lblresult.Text = "Error reading list of References. ";
            // lblresult.Text += err.Message;

        }
        finally
        {
            myConn.Close();
        }
    }

    protected void calEventDate_SelectionChanged1(object sender, EventArgs e)
    {
        txtEventDate.Text = calEventDate.SelectedDate.ToString();
        this.calEventDate.Visible = false;
    }
    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {

        if (this.calEventDate.Visible == false)

            this.calEventDate.Visible = true;

        else

            this.calEventDate.Visible = false;
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selectSQL;
        selectSQL = "SELECT * FROM CatA ";
        selectSQL += "WHERE Serial_No='" + DropDownList1.SelectedItem.Text + "'";
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
        SqlCommand cmd = new SqlCommand(selectSQL, con);
        SqlDataReader reader;
        // Try to open database and read information.
        try
        {
            con.Open();
            reader = cmd.ExecuteReader();
            reader.Read();
            TextBox17.Text = reader["Equipment_Name"].ToString();
            TextBox18.Text = reader["Description"].ToString();
            TextBox21.Text = reader["Vendor_Name"].ToString();
            TextBox22.Text = reader["Bill_No"].ToString();
            txtEventDate.Text = reader["Date_Of_Purchase"].ToString();
            Label5.Text = reader["Serial_No"].ToString();
            TextBox23.Text = reader["Price"].ToString();
            TextBox19.Text = reader["Tendor_No"].ToString();
            Label1.Text = reader["Type_Of_Purchase"].ToString();
            TextBox24.Text = reader["Qty"].ToString();



            //string result = reader["Type_Of_Purchase"].ToString();// place your retrieved column here
            if (Label1.Text == "Spot Purchase")
            {
                RadioButtonList1.ClearSelection();
                RadioButtonList1.Items[0].Selected = true;
            }
            else if (Label1.Text == "Tendor Purchase")
            {
                RadioButtonList1.ClearSelection();
                RadioButtonList1.Items[1].Selected = true;
            }




            reader.Close();

            lblStatus.Text = "";

        }
        catch (Exception err)
        {
            lblStatus.Text = "Error in displaying data. ";
            lblStatus.Text += err.Message;
        }
        finally
        {
            con.Close();
        }

    }
    protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
    {
       //  TextBox19.Text = TextBox19.Text.Replace(" ", "");// for removal of space in tendor numbr column else it will not work properly in checking code 328 to 339

        if (TextBox17.Text == "" || TextBox23.Text == "" || txtEventDate.Text == "" || TextBox24.Text == "" )
        {
            //lblStatus.Text = "***You have missed any field*** ";
            Response.Write("<script Language=javascript> alert('***You have missed any field***') </script>");
            // lblStatus.Text = "Records require an Ref_no,Description,Purchase Price,Sale Price,Quantity,Warning Quantity,Expiry Date,Vendor.";
            return;

        }



        else if (RadioButtonList1.Text == "Tendor Purchase" && TextBox19.Text == "")
        {
            Response.Write("<script Language=javascript> alert('***Please enter Tendor Number***') </script>");
            return;
        }
        else if (RadioButtonList1.Text == "Spot Purchase" && TextBox19.Text != "")
        {
            Response.Write("<script Language=javascript> alert('***Tendor Number is not required for Spot Purchase***') </script>");
            return;
        }
        else
        {

            string updateSQL;
            updateSQL = "UPDATE CatA SET Description = @Description, Vendor_Name = @Vendor_Name, Date_Of_Purchase = @Date_Of_Purchase, DateOfPurchase = @DateOfPurchase, Bill_No = @Bill_No, Price = @Price, Type_Of_Purchase = @Type_Of_Purchase, Tendor_No = @Tendor_No, Qty = @Qty WHERE (Serial_No = @Serial_No)";
            SqlConnection con = new SqlConnection();

            con.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
            SqlCommand cmd = new SqlCommand(updateSQL, con);

            //  con.Open();

            //cmd.ExecuteNonQuery();

            // Try to open database and execute the update.
            int updated = 0;
            try
            {
                con.Open();

                //lblStatus.Text = updated.ToString() + " record updated.";


                cmd.Parameters.Add("@Equipment_Name", TextBox17.Text);
                cmd.Parameters.Add("@Serial_No", Label5.Text);
                cmd.Parameters.Add("@Description", TextBox18.Text);
                cmd.Parameters.Add("@Vendor_Name", TextBox21.Text);
                cmd.Parameters.Add("@Bill_No", TextBox22.Text);
                cmd.Parameters.Add("@Date_Of_Purchase", txtEventDate.Text);
                cmd.Parameters.Add("@Price", TextBox23.Text);
                cmd.Parameters.Add("@Tendor_No", TextBox19.Text);
                cmd.Parameters.Add("@Type_Of_Purchase", RadioButtonList1.SelectedItem.Value);
                cmd.Parameters.Add("@Qty", TextBox24.Text);
                cmd.Parameters.Add("@DateOfPurchase", txtEventDate.Text);
                cmd.ExecuteNonQuery();
                Response.Write("<script Language=javascript> alert('RECORD UPDATED') </script>");
                lblStatus.Text = "***Record Updated.***";
            }
            catch (Exception err)
            {
                lblStatus.Text = "Error in updating :  ";
                lblStatus.Text += err.Message;
            }
            finally { con.Close(); }
        }
    }
}

[sania khan]

I have attached my database and output screen... can anybody help me to solve this problem...

[pritaeas]

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

This message means that you are trying to fill a column with more characters than it can hold. For example, suppose serial number is defined as varchar(6) and you are trying to insert '12345678'. Best way to avoid this is to check all varchar values, if they exceed your definition, truncate them to the maximum length.

[sania khan]

tnx... Problem solved :)