Data entry into sql server. string or binary data would be truncated.

I get the following error and I don't know why.  I am using sql server and defined my data as nvarchar(10).  I am only trying to enter one character in the column and I get the error.  I have also tried using char as the data type.  I have some null values in the columns.

Can anyone help?


String or binary data would be truncated.
The statement has been terminated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I am using sql server and defined my data as nvarchar(10)

obviously, either your variable or procedure or query is having some other values causing the above error message to appear..
Your column datatype might be varchar(10), kindly check the variable data type or else post your complete query to help better..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Francis EmpleoSystem DeveloperCommented:
Maybe you're inserting the wrong value. double check your code.
rckrchAuthor Commented:
FRTableConCommand.CommandText = "UPDATE FR_CELLCOLOR SET ContBreakPoint=@ContBreakPoint WHERE CustomerConcernNo=@CustomerConcernNo"
            FRTableConCommand.Parameters("@ContBreakPoint").Value = "R"
            FRTableConCommand.Parameters("@CustomerConcernNo").Value = "1004"

"ContBreakPoint" data type  is nvarchar(10) in sql server.

The above code gives me the error.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

You should be adding the parameters -
FRTableConCommand.CommandText = "UPDATE FR_CELLCOLOR SET ContBreakPoint=@ContBreakPoint WHERE CustomerConcernNo=@CustomerConcernNo"
FRTableConCommand.Parameters.Add("@ContBreakPoint").Value = "R"
FRTableConCommand.Parameters.Add("@CustomerConcernNo").Value = "1004"

Open in new window

rckrchAuthor Commented:
The parameters were added in a previous statement.  There parameters are revised for the same update statement because they are in a loop.  The loop searches for condition in a grid view across several cells.
ste5anSenior DeveloperCommented:
I would look at FR_CELLCOLOR in SSMS. Is it a view or table? Does it has an [INSTEAD OF] UPDATE trigger? When it is a view, do the base tables have such triggers?
rckrchAuthor Commented:
It is not a view it is a table.  it does not use an [INSTEAD OF] UPDATE trigger.

Also, I found the issue.  As I said with my initial post I have null values in some of  the columns in the table.  Due to the loop and the condition the procedure is looking for some of the values during the update could also be null.  This is when I get the error.
rckrchAuthor Commented:
Just increased the nvarchar from 10 to 25.  that solved the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.