Auto-Increment Primary key Sqlce Problem

[jonnycage]

Hello,

I am using Remote data access, passing a copy of one database on SQL Server 2055 to another database SqlCe Mobile server.

I've got 4 entries on one table on SQL Server 2005, then i use RDA and i have now that 4 entries on my pda database.

The problem is that when i want to insert another entry on that table the Id autoincrement starts from the beginning (from 1).

Example:

Table "Colmos" on First State after the copy using RDA:


ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1

Then i try to make an insert with de pda database to the "Colmo" table and i do it successful at first.
I get:

ColmoID Zona

2 Zona 1
3 Zona 1
4 Zona 1
5 Zona 1
6 Zona 1
1 Zona 1 (note that the increment counter start again from 1)

When i want to do another insert i get this error:

A duplicate value cannot be inserted into a unique index. [ Table name = Colmo,Constraint name = PK__Colmo__00000000000000F3 ]




The code that i am using is:

Dim sql As String = "INSERT INTO Colmo(Zona) VALUES('Zona 1')"

Dim c As SqlCeCommand = New SqlCeCommand(sql, connection)

connection.Open()

c.ExecuteNonQuery()
connection.Close()

If anyone could help me..

Thanks!

[Strider]

its probably not best to use autoincrement fields when using RDA (cause problems if inserting from multiple devices) the best option is to use the uniqueidentifier field