Sql query not working

[tims]

Hi, im doing the tutorial on the Northwind database. Im using VS2005 with
SQL CE 3.5SP1 and
have created a new database, added a customer table with 2 columns
Id and Name and added 2 records.

The following query doesn't work, from Query Designer->Execute SQL command

SELECT TOP 1 Id, Name
FROM Customer

I get the error
SQL ExecutionError
Executed SQL Staement SELECT TOP 1 Id,Name FROM Customer
Error Source: SQL Server Mobiel Edition ADO.NET Data Provider
Error Message: There was an error parsing the query. [ Token Line number =1,
TOken line offset =8,Token in error = TOP]

I can see its complaining about the TOP but WHY????

Please help!

 

[Chris Tacke, eMVP]

Because it's not supported. Get the data ordered using a resultset, then
read the number of lines you want.


--

Chris Tacke, Embedded MVP
OpenNETCF Consulting
Giving back to the embedded community
http://community.OpenNETCF.com

 

[Chris Tacke, eMVP]

Because it's not supported. Get the data ordered using a resultset, then
read the number of lines you want.


--

Chris Tacke, Embedded MVP
OpenNETCF Consulting
Giving back to the embedded community
http://community.OpenNETCF.com

 

[Christopher Fairbairn [MVP]]

Hi Tims,

The following query doesn't work, from Query Designer->Execute SQL command

SELECT TOP 1 Id, Name
FROM Customer

Support for the TOP clause is a new feature of SQL Server Compact 3.5
(http://msdn.microsoft.com/en-us/library/bb686896.aspx).

However the graphical database tools within Visual Studio 2005 only support
older v3.0 and v3.1 format databases.

If you want to use graphical tools to interact with SQL Server Compact v3.5
databases you will need to use Visual Studio 2008 or SQL Server Management
Studio 2008. Support for a version of SQL Server Compact edition is
typically tied to a specific Visual Studio or SSMS release.

Could you please confirm that you are successfully opening the database file
and performing queries against it with the graphical tools of VS2005? If you
are this suggests your databases is not in the v3.5 format, and this helps
explain the syntax errors you get when SQL Server CE attempts to parse the
TOP clause which wasn't supported in v3.1.

If you want to continue using VS2005 you would have to forgo the use of the
graphical tools and progamatically access the database contents via the 3.5
version of System.Data.SqlServerCE.dll assembly etc.

Hope this helps,
Christopher Fairbairn

 

[Christopher Fairbairn [MVP]]

Hi Tims,

The following query doesn't work, from Query Designer->Execute SQL command

SELECT TOP 1 Id, Name
FROM Customer

Support for the TOP clause is a new feature of SQL Server Compact 3.5
(http://msdn.microsoft.com/en-us/library/bb686896.aspx).

However the graphical database tools within Visual Studio 2005 only support
older v3.0 and v3.1 format databases.

If you want to use graphical tools to interact with SQL Server Compact v3.5
databases you will need to use Visual Studio 2008 or SQL Server Management
Studio 2008. Support for a version of SQL Server Compact edition is
typically tied to a specific Visual Studio or SSMS release.

Could you please confirm that you are successfully opening the database file
and performing queries against it with the graphical tools of VS2005? If you
are this suggests your databases is not in the v3.5 format, and this helps
explain the syntax errors you get when SQL Server CE attempts to parse the
TOP clause which wasn't supported in v3.1.

If you want to continue using VS2005 you would have to forgo the use of the
graphical tools and progamatically access the database contents via the 3.5
version of System.Data.SqlServerCE.dll assembly etc.

Hope this helps,
Christopher Fairbairn

 

[Ginny Caughey MVP]

In addition to the other suggestions, try TOP(1) instead of TOP 1.

 

[Ginny Caughey MVP]

In addition to the other suggestions, try TOP(1) instead of TOP 1.