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

IDENTITY_INSERT On/Off in sql compact

Hi,

Im working in a project that uses sql server compact. While migrating some data from another database I needed to "Turn off" the identity insert :

SET IDENTITY_INSERT <tablename> OFF

and set it back to "ON" after the migration was completed. Unfortunately SQL Server Compact is giving me the following error:

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

Im i doing something wrong or this instruction is not supported by the Compact edition.

Does anybody knows another way to do this?

Thank you

Alexander75

The IDENTITY_INSERT concept is not supported by SQL Compact Edition. Your best workaround for this is to use the ALTER TABLE / ALTER COLUMN IDENTITY to set the appropriate identity value for each row you want to insert. Not nice? You bet! But it's the only documented solution. I have it working on my database tranfer products so you can trust me it works.|||ok. Thanks|||You wouldn't happen to have a code sample for this, would you?

Thanks in advance...|||My samples use native code, but you can use the .NET CF classes for this. The critical part is to determine the IDENTITY properties before altering the table and this can be done by querying the INFORMATION_SCHEMA (see the BOL).