Nsryjdtyk

I have a procedure which updates some records. When I execute it I get the following exception

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

I could found this occur when the parameter length is larger than variable's length. I checked again changing the size. But didn't work. Go the same exception again. How can I solve this? Please help

Here is my code for update

            bool isFinished = dba.update(desingnation, title, initials, surname, fullname, callingName, civilSatatus, natinality, nic, birthday, passport,

                                          hometp, mobiletp, province, district, division, electorate, gramaNiladhari, takafull, p_city,

                                          c_city, p_hno, c_hno, tokens_P, tokens_C, previousEmployeements, bank, branch, type, account, gender, educatinalQ, languageE, languageS, languageT, empNo, appNo);

            if (isFinished)

            {

                WebMsgBox.Show("Successfully Inserted!");

            }

            else

            {

                WebMsgBox.Show("Some Errors Occured");

            }

        }

        else

        {

            WebMsgBox.Show("Some feilds are not valid");

        }

    }

}              

This is the code for passing parameters to stored procedures

            try

            {

                using (SqlCommand cmd = new SqlCommand())

                {

                    cmd.CommandType = CommandType.Text;

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Connection = connection;

                    cmd.CommandTimeout = 0;

                    cmd.Transaction = transactions;



                    /*=======================Update employee details================================*/

                    cmd.CommandText = "update_HS_HR_EMPLOYEE_AADM";



                    cmd.Parameters.Add("@appNo", SqlDbType.Int).Value = appNo;

                    cmd.Parameters.Add("@CALLING_NAME", SqlDbType.VarChar).Value = callingName;

                    cmd.Parameters.Add("@INITIALS", SqlDbType.VarChar).Value = initials;

                    cmd.Parameters.Add("@SURNAME", SqlDbType.VarChar).Value = surname;

                    cmd.Parameters.Add("@TITLE", SqlDbType.VarChar).Value = title;

                    cmd.Parameters.Add("@NAME", SqlDbType.VarChar).Value = fullname;

                    cmd.Parameters.Add("@FULLNAME", SqlDbType.VarChar).Value = fullname + " " + surname;

                    cmd.Parameters.Add("@NIC", SqlDbType.VarChar).Value = nic;

                    cmd.Parameters.Add("@BDY", SqlDbType.VarChar).Value = birthday;

                    cmd.Parameters.Add("@GENDER", SqlDbType.VarChar).Value = gender;

                    cmd.Parameters.Add("@NATIONALITY", SqlDbType.VarChar).Value = natinality;

                    cmd.Parameters.Add("@CIVILSTATUS", SqlDbType.VarChar).Value = civilSatatus;

                    cmd.Parameters.Add("@DESIGNATION", SqlDbType.VarChar).Value = desingnation;

                    cmd.Parameters.Add("@P_ADD1", SqlDbType.VarChar).Value = p_hno;

                    cmd.Parameters.Add("@P_ADD2", SqlDbType.VarChar).Value = tokens_P[0];



                    if (tokens_P.Length > 1)

                        cmd.Parameters.Add("@P_ADD3", SqlDbType.VarChar).Value = tokens_P[1];

                    else

                        cmd.Parameters.Add("@P_ADD3", SqlDbType.VarChar).Value = "";



                    cmd.Parameters.Add("@P_CITY", SqlDbType.VarChar).Value = p_city;

                    cmd.Parameters.Add("@TP_HOME", SqlDbType.VarChar).Value = hometp;

                    cmd.Parameters.Add("@TP_MOBILE", SqlDbType.VarChar).Value = mobiletp;

                    cmd.Parameters.Add("@PROVINCE", SqlDbType.VarChar).Value = province;

                    cmd.Parameters.Add("@DISTRICT", SqlDbType.VarChar).Value = district;

                    cmd.Parameters.Add("@C_ADD1", SqlDbType.VarChar).Value = c_hno;

                    cmd.Parameters.Add("@C_ADD2", SqlDbType.VarChar).Value = tokens_C[0];

                    cmd.Parameters.Add("@PER_GNDIV_CODE", SqlDbType.VarChar).Value = gramaNiladhari;

                    cmd.Parameters.Add("@PER_DSDIV_CODE", SqlDbType.VarChar).Value = division;

                    cmd.Parameters.Add("@TAKAFUL", SqlDbType.VarChar).Value = takafull;

                    cmd.Parameters.Add("@PASSPORT_NO", SqlDbType.VarChar).Value = passport;



                    if (tokens_C.Length > 1)

                        cmd.Parameters.Add("@C_ADD3", SqlDbType.VarChar).Value = tokens_C[1];

                    else

                        cmd.Parameters.Add("@C_ADD3", SqlDbType.VarChar).Value = "";



                    cmd.Parameters.Add("@C_CITY", SqlDbType.VarChar).Value = c_city;

                    cmd.Parameters.Add("@ELECTORATE", SqlDbType.VarChar).Value = electorate;



                    //int appNO = int.Parse((cmd.ExecuteScalar().ToString()));

                    cmd.ExecuteNonQuery();

                    cmd.Parameters.Clear();         





  }     

}         

This is the stored procedure

ALTER PROCEDURE [dbo].[update_HS_HR_EMPLOYEE_AADM] 

@appNo Int,

@CALLING_NAME VARCHAR(50),

@INITIALS VARCHAR(50),

@SURNAME VARCHAR(50),

@TITLE VARCHAR(50),

@NAME VARCHAR(50),

@FULLNAME VARCHAR(100),

@NIC VARCHAR(15),

@BDY VARCHAR(50),

@GENDER CHAR(1),

@NATIONALITY VARCHAR(50),

@CIVILSTATUS VARCHAR(50),

@DESIGNATION VARCHAR(50),

@P_ADD1 VARCHAR(50),

@P_ADD2 VARCHAR(50),

@P_ADD3 VARCHAR(50),

@P_CITY VARCHAR(50),

@TP_HOME VARCHAR(50),

@TP_MOBILE VARCHAR(50),

@PROVINCE VARCHAR(50),

@DISTRICT VARCHAR(50),

@C_ADD1 VARCHAR(50),

@C_ADD2 VARCHAR(50),

@C_ADD3 VARCHAR(50),

@C_CITY VARCHAR(50),

@ELECTORATE VARCHAR(50),

@PER_GNDIV_CODE VARCHAR(50),

@PER_DSDIV_CODE VARCHAR(50),

@TAKAFUL VARCHAR(50),

@PASSPORT_NO VARCHAR(50)



AS



BEGIN



update [HS_HR_EMPLOYEE_AADM]

SET

       [EMP_CALLING_NAME]=@CALLING_NAME

       ,[EMP_MIDDLE_INI]=@INITIALS

       ,[EMP_SURNAME]=@SURNAME

       ,[EMP_TITLE]=@TITLE

       ,[EMP_NAMES_BY_INI]=@NAME

       ,[EMP_FULLNAME]=@FULLNAME

       ,[EMP_NIC_NO]=@NIC

       ,[EMP_BIRTHDAY]=@BDY

       ,[EMP_GENDER]=@GENDER

       ,[NAT_CODE]=@NATIONALITY

       ,[EMP_MARITAL_STATUS]=@CIVILSTATUS

       ,[EMP_DATE_JOINED]=GETDATE()

       ,[EMP_CONFIRM_FLG]=0

       ,[CT_CODE]='000008'

       ,[DSG_CODE]=@DESIGNATION

       ,[CAT_CODE]='000001'

       ,[EMP_PER_ADDRESS1]=@P_ADD1

       ,[EMP_PER_ADDRESS2]=@P_ADD2

       ,[EMP_PER_ADDRESS3]=@P_ADD3

       ,[EMP_PER_CITY]=@P_CITY

       ,[EMP_PER_TELEPHONE]=@TP_HOME

       ,[EMP_PER_MOBILE]=@TP_MOBILE

       ,[EMP_PER_PROVINCE_CODE]=@PROVINCE

       ,[EMP_PER_DISTRICT_CODE]=@DISTRICT

       ,[EMP_TEM_ADDRESS1]=@C_ADD1

       ,[EMP_TEM_ADDRESS2]=@C_ADD2

       ,[EMP_PER_ELECTORATE_CODE]=@ELECTORATE

       ,[EMP_TEM_ADDRESS3]=@C_ADD3

       ,[EMP_TEM_CITY]=@C_CITY

       ,[EMP_PER_GNDIV_CODE]=@PER_GNDIV_CODE

       ,[EMP_PER_DSDIV_CODE]=@PER_DSDIV_CODE

       ,[EMP_PASSPORT_NO]=@TAKAFUL

       ,[EMP_TAK]=@PASSPORT_NO

       where App_no = @appNo



END

The specified error, "String or binary data would be truncated.rnThe statement has been terminated." is showing when you are trying to insert a value that is higher than the specified size of the column, When we look into the given procedure we can't identify the sizes of each column, So it would better if you cross check the sizes of columns with the values that you are giving.

I can say @GENDER may cause a similar issue, since it is defined as @GENDER CHAR(1), in the procedure but you are taking a string to the method and passing as SqlDbType.VarChar instead for that you have to give the value as char. for this particular field

Specify varchar size in SqlDBType.Varchar in C# code matching the size as specified in stored procedure eg.

cmd.Parameters.Add("@CALLING_NAME", SqlDbType.VarChar, 50).Value = callingName;

corresponding to parameter @CALLING_NAME VARCHAR(50) in stored procdeure.
This ensures that size is not exceeded when being passed to stored procedure.

If length is not specified for string parameter , ADO.NET picks up arbitary length value which may exceed the size specified specified in stored procedures VARCHAR parameters.

Also at front end ensure that the number of characters being entered in textboxes doesnot exceed corresponding parameters size.
This can be done using MaxLength attribute or prompting user with message using JQuery/Javascript if size exceeds.

Do it for other parameters and check.

The String or binary data would be truncated error is telling you that you are losing data. One of the annoying things about this error is that it doesn't tell you which column(s) the problem relates to, and in a scenario like this (with lots of columns), it makes it hard to diagnose.

Notice that for each column, there is a value in a C# variable, a declared parameter type (in both the C# code and the stored proc) and the size of the column in the table (the definition of which is missing from the question - which may explain why there isn't an accepted answer yet). All of these maximum lengths and types need to tie up, for all of the columns. You really need to check all of them; but we all like shortcuts, so...

My tip for finding which column(s) are having the problem is to find a scenario where it occurs so that you can easily repeat it - this is particularly easy to do it you have a unit test of this method. Now modify the stored proc to comment out half of the columns, and try again.

• If it works, then you know the uncommented columns are fine (for this particular set of data), and the problem was in one of the columns that was commented out, so uncomment half of the lines, and try again.


• If it didn't work, then the problem is with the uncommented columns, so comment out half of the remaining columns and try again.


• Repeat until you've worked out which columns have problems. I say 'columns', because although it may only be one column having this problem, there could be more than that.
  


• Now put everything back as it was when you started.

Now that you've worked out which column(s) have problems check each column's definition in the table against the stored proc parameter definition, the C# parameter definition, and the value in the C# variable. You may need to track this all the way back to where the value was entered in the user interface, and ensure that suitable restrictions are in place to prevent the value being too big.

As a bonus tip, I like having unit tests that my parameter sizes correspond with the type and size of the column that they relate to. I also have constants representing the max length of each string field and the maximum value of numeric fields. These constants are unit tested against the column in the database, and are used when restricting the values given by the user in the user interface. They can also be used by the unit test of that method, to prove that inserting the largest possible value for each column actually works.