I have a procedure which updates some records. When I execute it I get the following exception
"String or binary data would be truncated.\r\nThe 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
5条答案
按热度按时间monwx1rj1#
simply change size of your column because you are passing larger size data than your column size for example:
your column size is : nvarchar(200) if you will try to pass 400 character then it will show the same error
6yjfywim2#
Specify varchar size in SqlDBType.Varchar in C# code matching the size as specified in stored procedure eg.
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.
xghobddn3#
The specified error,
"String or binary data would be truncated.\r\nThe 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 asSqlDbType.VarChar
instead for that you have to give the value as char. for this particular fielde5nqia274#
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.If you have a suitable version of SQL Server (see the following hyperlinked page), you can turn on Trace Flag 460 (this may require a restart) to tell you exactly which table and column the problem relates to.
If not, here's my more manual approach... after which there is some information about how your parameters can be silently truncated without this error (which is not good).
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.
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.
However, note that it is worth making your
varchar
,nvarchar
andvarbinary
parameters larger than your column sizes due to the silent truncation that occurs with parameter coercion:SQL server will silently coerce your values to be whatever type the parameter is. For example...
will output...
This may come as a surprise, given that SQL will complain about something like this:
by saying
String or binary data would be truncated.
And yet the following code does not complain, silently coerces the value and inserts the record:So if you want to be aware of truncation issues occurring, you need to be sure that your parameter has a larger capacity than the column it is going into. For example, if we just change one character...
...will give the truncation error. However, notice that this isn't a complete solution because if I try it with a different value...
Then it will silently coerce, truncating after the space, then (because it's a varchar) the value has trailing spaces removed, and it inserts without complaining.
So the only way to be sure is to make your parameter is several characters larger than it needs to be, because there probably won't be multiple spaces in a row. You could use VARCHAR(MAX) for everything, but there is some concern that this could have performance impacts.
One place where this is particularly important is encrypted values. If encrypted values are truncated, then you can't decrypt them. So you need to be sure that your VARBINARY parameters are sized larger than the relevant column so that you would get errors instead of inserting truncated values. In this case, I believe a single character larger is sufficient, since there is no trimming of VARBINARYs. Well, apparently VarBinarys will trim trailing "nul" (ASCII=0) characters from the end; but only if ANSI_PADDING is set to OFF, but as Microsoft say, it should always be set to "ON". This section also covers what trimming occurs with different field types with different settings.
It's also worth saying that SQL isn't even consistent with how it does this. If we re-try the original example with a DECIMAL...
It doesn't complain about the value having too many decimal places, it just silently coerces it. And the same is true if I do it through a parameter which has more decimal places than the column...
And yet if I give it a value that is too large...
Then it will complain with
Arithmetic overflow error converting numeric to data type numeric.
(as it would if I put that value into a parameter first).One other thing to mention relates to parameter coercion and encryption. Imagine a scenario where you have an SQL column typed DECIMAL(9,2) and a parameter of the same type, and you are giving it a dot net "decimal" from your C# code. If the "decimal" in your code has lots of decimal places, this silent coercion will effectively be asking SQL to do the rounding for you. Which is fine... Until you decide to encrypt that column, because now the value you are encrypting would be a much longer value than the SQL DECIMAL column would have been able to hold, so is probably larger than you have allowed (in terms of your VARBINARY length). In this scenario you would need to ensure the value was rounded to the correct number of decimal places before encryption.
On the trimming trailing space of the parameter. It is only trimming as much as it needs... this shows that it has trimmed one space from the parameter, but left the remaining 4 after the N.
Yet another learning point about this... the same concept applies to user defined table types which correlate to table definitions.
Here is an example script to demonstrate the issue. Notice that the creation and dropping of the type has to be done outside the transaction.
vqlkdk9b5#
I have few suggestions to identify this: (Choose any 1, and then move to other, if issue still exists. Order doesn't matter, which ever Suggestion, you fell easy).
Steps:
Start debugging, and stop just before you are going to call the db.
Open Profiler, connect to DB.
Click on Clear Trace Window.
Start the debugging.
Stop the profiler, as soon as you got few requests.
Identify the Query, and run on SQL Server.
Suggestion 2: Try to Insert data using SQL Server management studio.
Steps:
Right click on
table
-> Click onScript Table as
-> Insert To.Now, compare with your Input, if you are passing correctly.
Suggestion 3: Use this code to get the difference between data length and data passed or directly truncate when passing to DB.
Steps:
Create this Class
DbContextExtension
.