3

I am getting an error:

The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates while trying to save the changes to SQLCE tables.

Please note this is an update operation.

I have version 3.5.1.0 of SQLCE installed.

Below is the error message and stack trace with the code:

System.Data.SqlServerCe.SqlCeException was unhandled Message=The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates. Source=SQL Server Compact ADO.NET Data Provider
HResult=-2147217900
NativeError=25923
StackTrace:
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicUpdate(TrackedObject item)
at System.Data.Linq.ChangeDirector.StandardChangeDirector.Update(TrackedObject item)
at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

Code causing the error:

var storedBug = DataContext.Bug_Tables.Where(b => b.Id.Equals(bugToSave.Id)).FirstOrDefaul(); 

storedBug.Name = bugToSave.Name;
storedBug.DeveloperId = bugToSave.Developer == null ? null : bugToSave.Developer.Id; 
storedBug.TesterId = bugToSave.Tester == null ? null : bugToSave.Tester.Id; 
storedBug.AssignedPersonId = bugToSave.AssignedPerson == null ? null : bugToSave.AssignedPerson.Id; 
storedBug.BugAreaId = bugToSave.BugArea.Id;
storedBug.BugStatusId = bugToSave.Status.Id;
storedBug.PriorityId = bugToSave.Priority == null ? null : bugToSave.Priority.Id; 
storedBug.Description = bugToSave.Description;
storedBug.Notes = bugToSave.Notes;
storedBug.Replication = bugToSave.Replication;
storedBug.FixedInBuild = bugToSave.FixInBuild;

DataContext.SubmitChanges();
Samuel Liew
  • 76,741
  • 107
  • 159
  • 260
Amitesh
  • 77
  • 1
  • 7
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! If you post error messages, **please** use the blockquotes ( ` " ` ) to properly format the error message. – marc_s Mar 27 '11 at 20:23
  • I had forgotton to mention that the Description and note fields store RTF strings. And also I am able to insert data fine but its only the updates that fail – Amitesh Mar 27 '11 at 20:25

1 Answers1

2

Please refer to http://social.msdn.microsoft.com/Forums/en/sqlce/thread/eca752c8-f921-44cf-a35b-5ecb14cc3134 which has all the answers

Amitesh
  • 77
  • 1
  • 7
  • Summation of the link: `NVARCHAR(MAX)` is not supported by SQL CE. I changed my code to use `NVARCHAR(4000)` instead. – Samuel Slade Oct 17 '16 at 18:34
  • I've already came to SO to get the answer and yet you redirect me to another website. Seriously. –  Apr 03 '18 at 00:20