locked
String or binary data would be truncated. The statement has been terminated.How to handle this error? RRS feed

  • Question

  • I am getting this error when ever i click the register button. I am also not putting more values than assigned in the table.The table is below:

    FirstName              nvarchar(50)       Allow Nulls

    LastName              nvarchar(50)       Allow Nulls

    Email                     nvarchar(50)        Allow Nulls

    Password              nvarchar(50)        Allow Nulls

    Address                 nvarchar(50)       Allow Nulls

    The code is below:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Data;
    using System.Windows.Documents;
    using System.Windows.Input;
    using System.Windows.Media;
    using System.Windows.Media.Imaging;
    using System.Windows.Shapes;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Net;
    using System.Collections.Specialized;
    using System.Data;
    using System.Text.RegularExpressions;
    namespace Sup
    {
       /// <summary>
       /// Interaction logic for Signup.xaml
       /// </summary>
       public partial class Signup : Window
       {
          public Signup()
          {
              InitializeComponent();
          }
          private void Reset()
          {
             textBoxAddress.Text = "";
             textBoxEmail.Text = "";
             textBoxFirstName.Text = "";
             textBoxLastName.Text = "";
             passwordBox1.Password = "";
             passwordBoxConfirm.Password = "";
          }
          private void registerButton_Click(object sender, RoutedEventArgs e)
          {
             if (textBoxEmail.Text.Length == 0)
             {
                MessageBox.Show("Enter an email.");
                textBoxEmail.Focus();
             }
     
             else if (!Regex.IsMatch(textBoxEmail.Text, @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"))
             {
                MessageBox.Show("Enter a valid email.");
                textBoxEmail.Select(0, textBoxEmail.Text.Length);
                textBoxEmail.Focus();
             }
             else
             {
                string firstname = textBoxFirstName.Text;
                string lastname = textBoxLastName.Text;
                string email = textBoxEmail.Text;
                string password = passwordBox1.Password;
     
                if (passwordBox1.Password.Length == 0)
                {
                   MessageBox.Show("Enter password.");
                   passwordBox1.Focus();
                }
                else if (passwordBoxConfirm.Password.Length == 0)
                {
                   MessageBox.Show("Enter Confirm password.");
                   passwordBoxConfirm.Focus();
                }
                else if (passwordBox1.Password != passwordBoxConfirm.Password)
                {
                   MessageBox.Show("Confirm password must be same as password.");
                   passwordBoxConfirm.Focus();
                }
                else
                {
                   string address = textBoxAddress.Text;
                   SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=logins;Integrated Security=SSPI");
     
                   con.Open();
     
                   SqlCommand cmd = new SqlCommand("Insert into Registration (FirstName,LastName,Email,Password,Address) values('" + firstname + "','" + lastname + "','" + email + "','" + password + "','" + address + "')", con);
     
                   cmd.CommandType = CommandType.Text;
                   cmd.ExecuteNonQuery();
                   con.Close();
                   MessageBox.Show("You have Registered successfully.");
                   Reset();
                }
             }
          }
       }
    }

    • Edited by heyrbiar Sunday, February 23, 2014 9:22 AM
    Sunday, February 23, 2014 7:30 AM

All replies

  • String or binary data truncation issue, is almost all cases, rises when the data you are trying to insert is more than what the column can consume or store. 

    Check your values for any space character before or after:

    Eg:

    create table Test_table(Col1 nvarchar(50))
    
    Insert into Test_Table Select '1234567890123456789012345'--works
    Insert into Test_Table Select '            1234567890123456789012345678901234567890'--will not work
    
    Select * From Test_Table
    
    Drop table Test_Table

    Sunday, February 23, 2014 7:41 AM
  • Tried that but same error, any other way to solve this?
    Sunday, February 23, 2014 7:44 AM
  • Tried that but same error, any other way to solve this?
    What did you try? Can you show me the values that you are trying? May be you can show us the Insert statement captured in SQL Profiler.
    Sunday, February 23, 2014 7:52 AM
  • You should try to find what is the root cause for this error  and which are the inputs that are coming where lenght is more than expected(more than 50 character string)

    If you don't care about the data lost due to truncation (which *should not* do), you can do this.

    insert into <<table>>(column1)
    Values(<substring(<<inputvalue>>,1,50);


    Satheesh
    My Blog | How to ask questions in technical forum


    Sunday, February 23, 2014 8:00 AM
  • FirstName: John

    LastName: Adam

    Email: john@gmail.com

    Password: *******

    Address: California, Usa

    Sunday, February 23, 2014 8:01 AM
  • Please show us the insert statement captured from the SQL Profiler to help you better.

    May be you will have trailing spaces....

    Sunday, February 23, 2014 8:09 AM
  • You need to check below things

    1. If the insert statement has values passed as string literals then check if values are within field length limits. Especially keep a note of cases where you're doing conctenation of values as there's a chance of final result excedding max length limit

    2. In cases where you insert values from another table make sure datatypes of your table has length atleast same as length of source field. In case of concatenating mutiple columns your table column length should be atleast sum of lengths of all the involved source columns.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, February 23, 2014 8:37 AM
  • I have added code to my thread, now please figure out what is the problem and it's solution
    Sunday, February 23, 2014 9:24 AM
  • >>SqlCommand cmd = new SqlCommand("Insert into Registration (FirstName,LastName,Email,Password,Address) values('" + firstname + "','" + lastname + "','" + email + "','" + password + "','" + address + "')", con);

    What you could do is add trim in your code. I am not sure if this could solve the issue

    
    
    SqlCommand cmd = new SqlCommand("Insert into Registration 
    (FirstName,LastName,Email,Password,Address)
     values(ltrim(rtrim('" + firstname + "')),ltrim(rtrim('" + lastname + "')),ltrim(rtrim('" + email + "')),ltrim(rtrim('" + password + "')),ltrim(rtrim('" + address + "')))", con);
    

    May be Address column is having issue, and you are sending characters more than the columns defined length.

    Also I would recommend you to read about SQL Injection as your appending the input values to a string and executing as query

    http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev


    Satheesh
    My Blog | How to ask questions in technical forum




    Sunday, February 23, 2014 10:00 AM
  • I have added code to my thread, now please figure out what is the problem and it's solution

    You may trim when assign to variable from the controls as below:

    string firstname = textBoxFirstName.Text.Trim();
    string lastname = textBoxLastName.Text.Trim();
    string email = textBoxEmail.Text.Trim();
    string password = passwordBox1.Password.Trim();

    ...
    string address = textBoxAddress.Text.Trim();

    One more thing, you need to check any special character appended with the string variable too.

    Sunday, February 23, 2014 12:18 PM
  • I get these two errors when adding this into code.

    1. The name 'ltrim' does not exist in the current context.
    2. The name 'rtrim' does not exist in the current context.
    Sunday, February 23, 2014 2:39 PM
  • Getting the same exception, any other way to solve this, please help
    Sunday, February 23, 2014 2:43 PM
  • As I earlier requested, Could you please share us the statements captured in SQL PRofiler?

    That would give you good info, believe me.

    Sunday, February 23, 2014 2:46 PM
  • Sorry, but i didn't understand 'statements captured in SQL PRofiler'
    Sunday, February 23, 2014 3:04 PM
  • Okay, Yes there was some issues with the script.

    Can you try this?

    SqlCommand cmd = new SqlCommand("Insert into Registration 
    (FirstName,LastName,Email,Password,Address)
     values(ltrim(rtrim('" + firstname + "')),ltrim(rtrim('" + lastname + "')),ltrim(rtrim('" + email + "')),ltrim(rtrim('" + password + "')),ltrim(rtrim('" + address + "')))", con);
    
    May be Address column is having issue, and you are sending characters more than the columns defined length.



    Satheesh
    My Blog | How to ask questions in technical forum


    Sunday, February 23, 2014 3:08 PM
  • Sorry, but i didn't understand 'statements captured in SQL PRofiler'

    Please refer the below link to know how to capture the statement...

    http://scn.sap.com/docs/DOC-43832

    Sunday, February 23, 2014 3:12 PM
  • Okay, what you can do is add a message-box to show the query on screen and then you can copy paste the statement (Press Ctrl+C  while messagebox show up) here
    SqlCommand cmd = new SqlCommand("Insert into Registration (FirstName,LastName,Email,Password,Address) values('" + firstname + "','" + lastname + "','" + email + "','" + password + "','" + address + "')", con);
    
    MessageBox.Show("Insert into Registration (FirstName,LastName,Email,Password,Address) values('" + firstname + "','" + lastname + "','" + email + "','" + password + "','" + address + "')");
    
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    con.Close();
    MessageBox.Show("You have Registered successfully.");
    Reset();


    Satheesh
    My Blog | How to ask questions in technical forum


    Sunday, February 23, 2014 3:14 PM
  • Here is it:
    Insert into Registration (FirstName,LastName,Email,Password,Address) values('John','Adam','heyrbiarkhan@gmail.com','test','California')

    Sunday, February 23, 2014 3:20 PM
  • And did you get the exception when you pass these values?

    and are you sure the column names are defined as nvarchar(50)? 

    Can you open the SQL Server Management studio and connect to your database and run the same query?

    Insert into Registration (FirstName,LastName,Email,Password,Address)
    values('John','Adam','heyrbiarkhan@gmail.com','test','California')


    Satheesh
    My Blog | How to ask questions in technical forum


    Sunday, February 23, 2014 3:29 PM
  • yeah i get the exception and yeah column names are defined as nvarchar(50).
    Sunday, February 23, 2014 3:33 PM
  • And what about running the query directly from Management studio?

    Can you show us the table creation script?

    Is this the only query running or is there any other queries too from your app(during the same flow)?


    Satheesh
    My Blog | How to ask questions in technical forum



    Sunday, February 23, 2014 3:48 PM
  • Well, i do not have management studio installed
    Sunday, February 23, 2014 3:56 PM
  • Well, i do not have management studio installed

    Then, how did you get the table info? I doubt on your table structure then it has got the column size specified.

    Please script out your table structure and share with us. You may ask someone who has access to the server and share us


    • Edited by SQLZealots Sunday, February 23, 2014 4:10 PM
    Sunday, February 23, 2014 4:09 PM
  • I have already mentioned table structure on my thread. If you are talking about something else then please let me know

    Heyrbiar, From what you have provided, there should not be any issue. Please find the script.

    Can you hardcode your values in .NET and see if that works? How do you know the table structure if you do not have access to see your tables? 

    create Table Registration(FirstName nvarchar(50),        
    
    LastName nvarchar(50)    ,    
    
    Email nvarchar(50)  ,       
    
    Password nvarchar(50) ,        
    
    Address nvarchar(50) )  
         
    Insert into Registration (FirstName,LastName,Email,Password,Address) 
    values('John','Adam','heyrbiarkhan@gmail.com','test','California')
    
    Drop table Registration

    Sunday, February 23, 2014 5:06 PM
  • I have access to see my table.
    Monday, February 24, 2014 10:05 AM
  • With out management studio, how you are seeing the table? What ever you have posted is not showing any issue.

    There can be two reason for this error, Its either your column is not defined properly or you are sending more data than expected. 


    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, February 24, 2014 10:09 AM
  • How to define the coloumn properly then?

    Monday, February 24, 2014 1:52 PM