string or binary data would be truncated. the statement has been terminated.

Question

0.00/5 (No votes)

See more:

, +

I am getting this error only where a value contains '&'. I don't know why this is happening as '&' operator is ok to insert into SQL server.

Here is the detailed Error log -

VB

System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

System.Data.SqlClient.SqlDataReader.get_MetaData()

System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

System.Data.Common.DbCommand.ExecuteReader()

System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)

System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)

System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)

System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)

System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)

System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)

System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

System.Data.Linq.DataContext.SubmitChanges()

Posted 12-Oct-12 23:48pm

surendersinghprajapati

Add a Solution

Comments

Nitesh Kejriwal 13-Oct-12 6:08am

Are you sure it happens because of "&" symbol?
Are you using plain query or Stored Procedure to submit data to Db?

surendersinghprajapati 16-Oct-12 1:40am

Yes I am sure, it happens because of "&" symbol and I am using Linq to Sql in .Net 4.0. It's strange but true.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Anagha_Globuss · Answer 1 · 2012-10-13T00:11:00

"string or binary data would be truncated" - This Error Means the length of field of table is smaller then the length of data that is to be inserted in Table.

Eg- if length of field is VARCHAR(10);
& the data that is to be inserted is - "Welcome to the world of ASP.NET" i.e it's length is more than 10.
So, we need to increase the length of the field.
This has noting to do with '&'.

n.podbielski · Answer 2 · 2012-10-13T00:00:00

Solution 1

Error like this usually means that some string value is to long.
For example when you define column:

varchar(100)

and you would try to insert string with length>100 you get error like this.

Check your data against this.

Posted 13-Oct-12 0:00am

n.podbielski

Comments

surendersinghprajapati 13-Oct-12 6:09am

Yes I understand that but I have already checked for value sizes as per the database tables and all seems good aspect that one value contains '&' which is causing this error.

n.podbielski 13-Oct-12 6:21am

Maybe this sign is unicode and its size is really 2 bytes and your column is char(xx).
Then even if length of string is correct this will be to much for DB. Check this in sql script because I am just guessing now.

surendersinghprajapati 13-Oct-12 6:31am

Yes I checked same values inserting into database by sql script instead of LINQ to SQL and values inserted corrected without issue.

So I am suspecting error with LINQ to SQL. Is something with OR mapping details which store in DBML file?

n.podbielski 13-Oct-12 6:50am

Then maybe LinqToSQL do something funny with data. I would check with SQL Profiler what statement it's generating exactly. When you gonna see that maybe cause and solution will be obvious.

surendersinghprajapati 16-Oct-12 1:42am

I just added temp solution for now and will look into this once get some time to do R&D with LinqToSql. Anyway Thanks for suggestion.

Member 11428695 14-Feb-15 1:10am

I've specified more length for the column than the value i'm passing in textbox.. still i'm getting the same error???

Member 11978149 9-Mar-16 2:57am

Please check your primary key in your table.