locked
Unable to execute below mentioned queries Against SqlCe 3.1 RRS feed

  • Question

  • Hi,

    When I run the following queries they are working fine with MS Access but not working with SqlCe 3.1 on my desk top.

    I am using Sql Server 2005 management studio to connect to SqlCe (.sdf) file and running the queries with in that.

    Please guide me what is wrong here:

    1)

    select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)

    Error Message:

    Major Error 0x80040E14, Minor Error 25501

    > select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)

    There was an error parsing the query. [ Token line number = 1,Token line offset = 23,Token in error = SELECT ]

    2)

    SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0

    AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)

    AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')

    Error Message:

    Major Error 0x80040E14, Minor Error 25501

    > SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0

    AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)

    AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')

    There was an error parsing the query. [ Token line number = 2,Token line offset = 8,Token in error = SELECT ]

    Tuesday, April 10, 2007 9:54 PM

Answers

All replies

  • Nested selects are not allowed on SQL CE. Use several queries and/or join.

    Wednesday, April 11, 2007 5:14 AM
  • Is it same with all the versions of SqlCe? or just with  3.1

    My current application making use of MS Access and most of the queries are same like what I mentioned above. If nested queries won't work with SqlCe then I need to replace almost all the queries in my application and there are 100s.

    Could you please guide some one if we have any alternative solution for this.

    And also can you refer the SqlCe documentation where its mentioned regarding nested queries support.


    Thanks in advance,
    Sreenaiah

    Wednesday, April 11, 2007 3:11 PM
  • This is the documentation regarding nested queries: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand(vs.80).aspx

     

     It appears that nested queries in SELECT FROM will be made available in the next version (3.5) of SQL Compact Edition, due to be released with the next version of Visual Studio ("Orcas")

    Wednesday, April 11, 2007 6:20 PM
  • Hi Erik,

    I have installed SqlCE 3.5 by downloading the latest copy of Microsoft Synchronization Services for ADO.NET CTP. But I am not clear how to make use of this instead of 3.1.
    Could you provide some info on this.

    I have created a new thread ( How to use SqlCE 3.5 with in Visual Studio 2005? ) in this forum, please look into the same for more details.



    Thursday, April 12, 2007 2:40 PM