locked
Error - The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON.. RRS feed

  • Question

  • Hi.

    I am getting this error when running my application OFFLINE

    "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."

    The application was built using the Sync Framework. The local database is Microsoft SQL Compact 3.5 SP2 Version 3.5.8080.0

    The stack trace is as follows: 

    A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
    System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>CalBuilder.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.SqlServerCe.SqlCeException, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</ExceptionType><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.</Message><StackTrace>   at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
       at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
       at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at CalBuilder.RunCal.localCal() in C:\Users\MB\Documents\Visual Studio 2010\Projects\CalBuilder\CalBuilder\Calibration\RunCal.vb:line 232
       at CalBuilder.RunCal.OK_Button_Click(Object sender, EventArgs e) in C:\Users\MB\Documents\Visual Studio 2010\Projects\CalBuilder\CalBuilder\Calibration\RunCal.vb:line 26
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
       at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
       at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at CalBuilder.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>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.</ExceptionString></Exception></TraceRecord>

    The code generating the error is:

    Dim strQTol As String = "select [CreateScriptTable].[TestType],[CreateScriptTable].[TestName], [TestOptionsTable].[LowerLimits], [TestOptionsTable].[UpperLimits] from CreateScriptTable inner join TestOptionsTable on CreateScriptTable.TestType=TestOptionsTable.TestName LEFT JOIN TestResultsTable on CreateScriptTable.TestType = TestResultsTable.TestName WHERE CreateScriptTable.InstrumentType= '" & Me.cbInst.SelectedValue.ToString & "' ORDER BY [Index] ASC, TestName"                

    cmd1 = New SqlCeCommand(strQTol, conn)               

    da1 = New SqlCeDataAdapter(cmd1)               

    ds1 = New DataSet               

    da1.Fill(ds1, "CreateScriptTable")


    From reading up around the web, this is an old problem dating back to 2009 which I thought had been sorted. It seems as if the varbinary and nvarchar datatypes are being cast to ntext to image types.

    I have found a fix at http://support.microsoft.com/kb/958478 but this fix seems to pre date the version of SQL CE I have so I presume it will not fix my error. 

    Can anyone help me out please?

    Monday, January 14, 2013 2:58 PM

All replies

  • the error you're getting doesn't seem to be specific to sync framework, you might want to cross post on the SQL Compact forums...
    Tuesday, January 15, 2013 6:20 AM