none
compact sql error -2147467259

    Question

  • on executing the following

    SqlCeCommand cmd = new SqlCeCommand(@"IF EXISTS(SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name
    FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name = 'm2' and t.name = 'repositoryDataTable' ORDER BY schema_name, table_name
     select 1
    else select 0", new SqlCeConnection(connectnStr));
    			cmd.Connection.Open();
    			return 0==(int)cmd.ExecuteScalar() <== error below

    System.Data.SqlServerCe.SqlCeException was unhandled
      HResult=-2147467259
      Message=There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]
      Source=SQL Server Compact ADO.NET Data Provider
      ErrorCode=-2147467259
      NativeError=25501

      StackTrace:
           at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
           at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
           at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
           at System.Data.SqlServerCe.SqlCeCommand.ExecuteScalar()
    ...

      StackTrace:
           at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
           at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
           at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
           at System.Data.SqlServerCe.SqlCeCommand.ExecuteScalar()
           at ieNotepadDotNetTabbed.DBStorage.needAlterRepository4M2()
           at ieNotepadDotNetTabbed.MainForm.MainForm_Load(Object sender, EventArgs e)
           at System.EventHandler.Invoke(Object sender, EventArgs e)
           at System.Windows.Forms.Form.OnLoad(EventArgs e)
           at System.Windows.Forms.Form.OnCreateControl()
           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
           at System.Windows.Forms.Control.CreateControl()
           at System.Windows.Forms.Control.WmShowWindow(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.ContainerControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WmShowWindow(Message& m)
           at System.Windows.Forms.Form.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
           at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Control.set_Visible(Boolean value)
           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(Form mainForm)
           at ieNotepadDotNetTabbed.Program.Main(String[] args)
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

    I tried searching the web to see if used any sql not usable on ce, but did not find any the list of not to use

    it's not the system table like schema_name and table__name, I hope.

     is there any other way acceptable to sql ce 4 to find is a column not exists in a table?

    Thursday, January 19, 2017 9:52 AM

Answers

  • use: "SELECT COLUMN_NAME" not "SELET m2" !


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by fs - ab Monday, January 23, 2017 9:10 AM
    Sunday, January 22, 2017 4:41 PM
    Moderator

All replies

  • You can use:

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = N'Orders'

    AND COLUMN_NAME = N'Order ID'

    (Will return NULL if not found)


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, January 19, 2017 10:11 AM
    Moderator
  • thx but still error out but with a different NativeError of 25503:

    SqlCeCommand cmd = new SqlCeCommand(@"select m2
     from INFORMATION_SCHEMA.COLUMNS
     where TABLE_NAME = 'repositoryDataTable' and COLUMN_NAME ='M2'",
     new SqlCeConnection(connectnStr));
      // also tried with N prefix for table_name and column_name values
    cmd.Connection.Open();
    return null==(object)cmd.ExecuteScalar();

    since the message is "The column name is not valid. [ Node name (if any) = ,Column name = m2 ]"

    can I simply capture the exception as field the column does not exist?

    Alberto,  you're right about sql ce does not support "if" - it does not support any other than straight non procedure sql logic.
    BTW: I'm using SQL CE 4 sp1 with .net 4.5


    • Edited by fs - ab Friday, January 20, 2017 6:40 AM
    Thursday, January 19, 2017 11:19 PM
  • use: "SELECT COLUMN_NAME" not "SELET m2" !


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by fs - ab Monday, January 23, 2017 9:10 AM
    Sunday, January 22, 2017 4:41 PM
    Moderator