insertion problems on sql ce

[Paul Marker]

I have a relation called Correspondence of the following
format:

corrId int identity(1, 1) primary key
type varchar(50)
cwith varchar(50)
isInternal smallint(2)
rowguid uniqueidentifier

the rowguid attribute was added by sql server when setting
up merge replication.

On the CE application I am trying to insert a row into the
table and then pull the corrId back out using @@IDENTITY.
Strangely when I try and insert I get the following error:

HR: 800040E2F
Native: 25016
Message: A duplicate value cannot be inserted into a
unique index [,,,,,]
Source: Microsoft SQL Server 2000 Windows CE Edition
Parameter 1: 0
Parameter 2: 0
Parameter 3: 0
Parameter 4:
Parameter 5:
Parameter 6:

I could understand the error the corrId was being
incremented by 1 each time (& it didn't have the
uniqueidentifier attribute). The question is what I am
missing that causing the problem?

Thanks.

 

[Mark Ihimoyan [MSFT]]

could you please include some sample code so i can see what exactly you are
doing.

Thanks.

 

[Paul Marker]

I don't have the code to hand now, but I was basically
using the command.ExecuteScalar() to execute and insert
sql statement on the Timesheet table and then called a
select statement to retirve the last ID on the identity
field created using the @@IDENTITY, to use with the
insertion of a correspondence entry.

The code works fine, if the first synchronization occurs
with emtpy Timesheet and Correspondence tables. However
if there is also rows in the tables I get the error
mentioned. I think the identity range management would
have solve itm but it was disabled any i found no way to
enable it (even during the publication setup in EM). So i
changed the format of the tables, replacing the identity
with a uniqueidentifier attribute.

A bit of topic but why does sql ce not allow "SELECT NEWID
()" like Sql Server? Seems a simple thing to allow?

Thanks.