SqlCeCommand does NOT support subqueries?

yesterday I wanted to create a sql query ( using system.data.sqlce ) which return values from diff tables on mobile devices.

My scenario is as follows: - sql table: Xtable (fields: name : is string, mat : is string, state : is int value); - XTable contains 100 records my sql statement is:

SELECT name, (SELECT count(*) from Xtable where state=0) a开发者_JS百科s Marked, count(*) as Total
FROM XTable
GROUP BY name

result: 0 rows, because i have error with second SELECT.

IF I execute following sql statement:

SELECT name, count(*) as Total FROM XTable GROUP BY name
result : > 0 rows.

Sqlcecommand not support multiselect query? how can I fix it ?

SQL Compact does not support this type of subquery. See this MSDN Forum thread for confirmation.

If you can give error details then it would be easy to help you.

(SELECT count() from Xtable where state=0)

No argument in count()

(SELECT count(*) from Xtable where state=0)

Edit

In your edit you have ***** as argument in count. It was missing originally, is it correct.