• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

string or binary data would be truncated

I have a table User_ExtraInfo.
 TableNow I got an erro: string or binary data would be truncated in the code. All parameters come from textboxs or a dropdown menu.
Please help me, many thanks.
 protected void btnSave_Click(object sender, EventArgs e)
            MembershipUser user = Membership.GetUser(userName);
            user.Email = TextEmail.Text;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MembershipDB"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            string UserID = user.ProviderUserKey.ToString();
            cmd.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;

            cmd.Parameters.Add("FirstName", SqlDbType.NVarChar).Value =TextFirstName.Text;
            cmd.Parameters.Add("LastName", SqlDbType.NVarChar).Value = TextLastName.Text;

            cmd.Parameters.Add("Degree", SqlDbType.NVarChar).Value = TextDegree.Text;
            cmd.Parameters.Add("Organization", SqlDbType.NVarChar).Value = DropDownList1.SelectedValue;
            cmd.Parameters.Add("Phone", SqlDbType.NChar).Value = TextPhone.Text;
            cmd.Parameters.Add("Ext", SqlDbType.NVarChar).Value = TextExt.Text;
            cmd.Parameters.Add("last4SSN", SqlDbType.NChar).Value = TextSSN.Text;
            cmd.CommandText = "update User_ExtraInfo SET FirstName=@FirstName, LastName=@LastName,Degree=@Degree,Organization=@Organization,Phone=@Phone,Ext=@Ext,last4SSN=@last4SSN where UserID =@UserID";

            cmd.ExecuteNonQuery(); // here exception was thrown


Open in new window

4 Solutions
Scott CraigWebmasterCommented:
This generally means that one of your field lengths will exceed what your database is set to.  Have you done any pre-check on the data to ensure that each field is no longer than the field length set in your database?
Luis PérezSoftware Architect in .NetCommented:
It's a good practice to set the MaxLength property of the TextBox controls to the maximum capacity of each field. For example, the TextFirstName TextBox must have a MaxLength of 50; the TextDegree TextBox must have a MaxLength of 10, and so on. With this simple and effortless approach you ensure that none of your text fields exceeds your database capacity, so you will not have the "string or binary data would be truncated" error.

Hope that helps.
käµfm³d 👽Commented:
With this simple and effortless approach you ensure that none of your text fields exceeds your database capacity, so you will not have the "string or binary data would be truncated" error.
Of course you should always validate your data before sending it to your DB since you can't always guarantee that a user will be submitting requests to your site from within a browser  ; )
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Jesus RodriguezIT ManagerCommented:
I recomended to you that as everyone said validate the data of each text bx more or les like this

iif(TextPhone.lenght>0,left( TextPhone.Text,10),"")
where 10 will mean the amount of character that support each field in your database.
zhshqzycAuthor Commented:
I have a dropdown select value, how to set up its length?
käµfm³d 👽Commented:
I think you should spend some time reading up on n-tier architecture. You are heading down the road of brittle design by mixing your UI logic and your data access logic. By creating layers to your application, you make it easier to handle and reuse things like validation and DB access logic.

As to your most recent question, as I evoked earlier, validate the length of the selected item in your code--don't focus on validating it in the UI. Validate the items before you send them to the DB. If the values don't pass validation, let your user know with the appropriate error message.
Jesus RodriguezIT ManagerCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now