none
string or binary data would be truncated. the statement has been terminated.

    Question

  • using

     

    System;

    using

     

    System.Collections.Generic;

    using

     

    System.Linq;

    using

     

    System.Web;

    using

     

    System.Web.UI;

    using

     

    System.Web.UI.WebControls;

    using

     

    System.Data.SqlClient;

    public

     

    partial class my_info_myinfo : System.Web.UI.Page

    {

     

    protected void Page_Load(object sender, EventArgs e)

    {

     

    if (!IsPostBack)

    {

     

    SqlConnection myConnection;

     

    SqlCommand myCommand;

     

    SqlDataReader myDataReader;

     

    myConnection =

    new SqlConnection(@"Data Source=CTGPJLPC14\SQL2005_STD;Initial Catalog=fitness4fun;Persist Security Info=True;User ID=sa;Password=nypadmin");

    myConnection.Open();

     

    string usr_id = "melissa";

     

     

     

    //prepare sql statements

     

    string sql = "SELECT * FROM T_USR where usr_id='" + usr_id + "'";

    myCommand =

    new SqlCommand(sql, myConnection);

    myDataReader = myCommand.ExecuteReader();

     

    Console.Write("login");

     

    if (myDataReader.Read())

    {

    username.Text = (

    String)myDataReader["usr_id"];

    displayname.Text = (

    String)myDataReader["usr_disp_name"];

    homeadd.Text = (

    String)myDataReader["usr_address"];

    Password.Text = (

    String)myDataReader["usr_password"];

    dob.Text = (

    String)myDataReader.GetDateTime(myDataReader.GetOrdinal("usr_dob")).ToString("dd MMM yyyy");

    ____.Text = (

    String)myDataReader["usr_sex"];

    postalCode.Text = (

    String)myDataReader["usr_postal_code"];

    country.Text = (

    String)myDataReader["usr_country_id"];

    email.Text = (

    String)myDataReader["usr_email"];

    }

    }

     

    //cleanup objects

     

    // myDataReader.Close();

     

    //myConnection.Close();

     

    }

     

     

     

     

    protected void Addbtn_Click(object sender, EventArgs e)

    {

     

    //Declare a SqlConnection

     

    SqlConnection conn;

    conn =

    new SqlConnection(@"Data Source=CTGPJLPC14\SQL2005_STD;Initial Catalog=fitness4fun;Persist Security Info=True;User ID=sa;Password=nypadmin");

     

    string usr_id = username.Text;

     

    string usr_password = Password.Text;

     

    string usr_disp_name = displayname.Text;

     

    string usr_address = homeadd.Text;

     

    string usr_dob = dob.Text;

     

    string usr_sex = ____.Text;

     

    string usr_postal_code = postalCode.Text;

     

    string usr_country_id = country.Text;

     

    string usr_email = email.Text;

     

    string allergy = Medicine.Text;

     

    string symptom = Symptom.Text;

     

    string usr_height = height0.Text;

     

    string usr_weight = weight0.Text;

     

     

     

    {

     

    try

    {

     

    // Open the connection

    conn.Open();

     

    // prepare command string

     

    string sql = @"

    UPDATE T_USR SET usr_address='"

     

    + homeadd.Text + "',usr_dob='" + dob.Text + "',usr_sex='" + ____.Text + "',usr_postal_code ='" + postalCode.Text + "', usr_country_id ='" + country.Text + "', usr_email='" + email.Text + "' WHERE usr_id='" + usr_id + "'";

     

     

     

     

    // 1. Instantiate a new command with a query and connection

     

    SqlCommand cmd = new SqlCommand(sql, conn);

     

     

    // 2. Call ExecuteNonQuery to send command

    cmd.ExecuteNonQuery();

     

    // Response.Write(insertString);

    }

     

    finally

    {

     

    // Close the connection

     

    if (conn != null)

    {

    conn.Close();

    }

    {

     

    // Open the connection

    conn.Open();

     

    try

    {

     

    // prepare command string

     

    string insertString = @"

    INSERT INTO T_SUR_MEDICAL_HISTORY

    ( usr_id,allergy)

    VALUES('"

     

    + usr_id + "','" + Medicine.Text + "')";

     

     

    SqlCommand cmd = new SqlCommand(insertString, conn);

     

     

     

    // 2. Call ExecuteNonQuery to send command

    cmd.ExecuteNonQuery();

    }

     

    finally

    {

     

    // Close the connection

     

    if (conn != null)

    {

    conn.Close();

    }

    }

    }

     

    }

    }

    }

    }

    i've got this problem string or binary data would be truncated. the statement has been terminated. whenever i want to insert a data into T_SUR_MEDICAL_HISTORY. i've tried changing the varchar(50) to varchar(MAX) in my database but the error message still appears.

    PLEASE HELP

    Friday, October 23, 2009 8:32 AM

Answers

  • Hello,

    oh dear, that source code is not readable...

    But at all, the error messages means you try to insert more characters in a field then is allowed by design.
    If you try to insert e.g. 51 chars into a varchar(50) field you run on this error.

    Please proof the field length and the length of data you want to insert.

    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Friday, October 23, 2009 3:42 PM