IF NOT EXISTS problem

Hi,

I am migrating a project from SQL to SQL Compact Edition and the following statement keeps failing in the CE project:

Code Snippet

IF NOT EXISTS (SELECT * FROM Court2 WHERE BookingDate = '2007-05-28') INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52) ELSE UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')

It works fine in SQL.

I've done a fair bit of searching for the solution and it appears my syntax is not perfect but I can't see where.

Any suggestions.

Here's the error that CE produces:

Code Snippet

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

Thanks,

Glen.

Hi Glen,

IF.. ELSE is not available in SQL CE, as only a subset of the full T-SQL grammar is supported by SQL CE,

so you will have to do this in code, like:

Get a DataReader with

Code Snippet

SELECT * FROM Court2 WHERE BookingDate = '2007-05-28'

and perform your insert or update depending on the result of this.

For documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

|||

Thank you.

I'm now working on the next issue:

Unfortunately I'm only a hacker and learning the hard way .. Is there somewhere that I can go that lists the differences and workarounds for the CE that I can read.

Cheers.

|||See SQL CE Compact Edition BOL, - for documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx