SQLCE Exception

[Jason L James]

I am using a sqlceCommandBuilder to update my
dataset back to my sqlce DB.

The dataset is updating correctly, but when I
issue the

da.update(ds)

command I get an exception that says:

A duplicate value cannnot be inserted into a unique index[,,,,,]

My select statement is

"SELECT dID, dName, rowguid FROM tblDepartment ORDER BY dName ASC"

These are all of the fields.

My command builder and update code is:

Dim myDepartmentCB As New SqlCeCommandBuilder(myDADept)
myDADept.Update(myDS.Tables("Department"))

My datatable is constructed as follows:

CREATE TABLE [tblDepartment] (
[dID] [int] IDENTITY (1, 1) NOT NULL ,
[dName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF__tblDepart__rowgu__5812160E] DEFAULT (newid()),
CONSTRAINT [PK_tblDepartment] PRIMARY KEY CLUSTERED
(
[dID]
) ON [PRIMARY]
) ON [PRIMARY]

Does anyone have any ideas. Should I use my own generated insert,
update and delete commands? I appear to get nullreference exceptions
when I do that!

Many thanks,

Jason.

 

[Tom Krueger [MSFT]]

Hi,

You are getting an exception "A duplicate value cannot be inserted into a
unique index[,,,,,]" when calling da.update(ds).

I haven't worked much with CommandBuilder so it may be doing something funky
that is messing you up. You didn't mention that you have added rows to the
dataset, however, I assume that is what you are doing and that you added a
value that already exists to a field requiring to be unique.

Does this exception only occur when rows have been added? If yes, how are
you setting the identity on the new rows?

Here are two references that may help.
http://www.codeproject.com/cs/database/relationaladonet.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;313028

Let us all know how this turns out.

Tom


--
Tom Krueger
Microsoft Corporation
Program Manager
http://weblogs.asp.net/tom_krueger

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Alex Feinman [MVP]]

Set a breakpoint on myDADept.Update(myDS.Tables("Department"))
and in the Command window print out the
myDepartmentCB.UpdateCommand.CommandText, then post it here.

 

[Jason L James]

Alex, Thanks for the offer of help. I am only inserting new
records at this time, although updates and deletes may be
required before the application is completed.

The insert command for the DA is set to nothing?

? myDADept.InsertCommand.CommandText
Referenced object 'InsertCommand' has a value of 'Nothing'.

The insert command of the command builder is set as below:

? myDepartmentCB.GetInsertCommand.CommandText
"INSERT INTO tblDepartment (dName) VALUES (?)"

However, for reasons I don't understand, the department
update is now working and writting back to the sdf file.

Of course, this has not filtered down into the area, location or
item inserts.

Select Command:
? myDAArea.SelectCommand.CommandText
"SELECT aID, adID, aName, rowguid FROM tblArea ORDER BY aName ASC"

Insert Command from DA:
? myDAArea.InsertCommand.CommandText
Referenced object 'InsertCommand' has a value of 'Nothing'.

Insert Command from CB:
? myAreaCB.getInsertCommand.commandtext
"INSERT INTO tblArea (adID,aName) VALUES (?,?)"

The rowguid has default values entered by SQLCE as newID().

I have the commands for the Locations and Items also, but I figure if
we sort one then they will all be the same.

Many thanks,

Jason.

 

[Jason L James]

I re-created my DB without the relationships in the database, just
the dataset, replicated it to my PPC and things appears to be
working much better now. I read somewhere that the action of
creating a merge replacition publication does things to the indexs
of the database. Could this be the problem. The publication wizard
does some funny things to the data tables; i.e. adds guid rows
and sets of PK as NOT FOR REPLICATION. Could this be
messing things up, especially if there are relationships in the
underlying DB.

Any thoughts,

Jason.

Alex, Thanks for the offer of help. I am only inserting new
records at this time, although updates and deletes may be
required before the application is completed.

The insert command for the DA is set to nothing?

? myDADept.InsertCommand.CommandText
Referenced object 'InsertCommand' has a value of 'Nothing'.

The insert command of the command builder is set as below:

? myDepartmentCB.GetInsertCommand.CommandText
"INSERT INTO tblDepartment (dName) VALUES (?)"

However, for reasons I don't understand, the department
update is now working and writting back to the sdf file.

Of course, this has not filtered down into the area, location or
item inserts.

Select Command:
? myDAArea.SelectCommand.CommandText
"SELECT aID, adID, aName, rowguid FROM tblArea ORDER BY aName ASC"

Insert Command from DA:
? myDAArea.InsertCommand.CommandText
Referenced object 'InsertCommand' has a value of 'Nothing'.

Insert Command from CB:
? myAreaCB.getInsertCommand.commandtext
"INSERT INTO tblArea (adID,aName) VALUES (?,?)"

The rowguid has default values entered by SQLCE as newID().

I have the commands for the Locations and Items also, but I figure if
we sort one then they will all be the same.

Many thanks,

Jason.