Updating SS CE DB After merge Rep

[Guest]

(Portions previously posted in SQL Server CE newsgroup, but it appears no one
is minding the shop over there!)
Ok, so I managed to get merge replication working and I have a single table
in a database out on my PDA. I now want to change a row in the table and
synch back to the main database. I'm trying to do the easy part – use
System.Data.SqlServerCe to update the table in SQL CE but I can't get the
blasted table to update! (I know this because I examine the results in the
Query Analyzer after running my app.)

In addition, on the PDA…
- I know I’m connecting to the database from my application code because I
can select and read labels from the table with no problems.
- I know the update statement is correct because I can run it from the Query
Analyzer (both against the backend database and the CE database) and achieve
the desired result.

Earl, from the SQL Server CE newsgroup writes that he “too had some SQLCE
update problems that were not code related at all, rather the issue was with
the backend database structureâ€. Can anyone expand on this? The table that
I’m experimenting with does have a trigger on the backend database but it
doesn’t affect the column that I’m attempting to update, and besides, I don’t
think triggers are replicated hence it seems unlikely that they would foul
things up until you attempt to synch back up with the backend.

Here is my code, if it is run on the PDA against the replicated CE database,
no exceptions are thrown, and no rows are updated. Have I goofed up somehow?

Bill

try
{
SqlCeConnection cn = new SqlCeConnection( @"Data Source=\My
Documents\oxsite.sdf" );
string str = "UPDATE cioxsitelist SET oxsitelabel = 'Kilroy was here'
WHERE oxsitecode = 66";
SqlCeCommand cmd = new SqlCeCommand( str, cn );
cmd.CommandType = CommandType.Text;
cn.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch( SqlCeException sex )
{
foreach( SqlCeError err in sex.Errors )
{
MessageBox.Show( err.Message, "SQL CE Error" );
}
}
catch( Exception ex )
{
MessageBox.Show( ex.Message, "Error" );
}

 

[Darren Shaffer]

Bill,

What error are you getting when you attempt to update the
cioxsitelist table in your code?
--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com

 

[Guest]

No errors are produced. The update simply does nothing.

Bill

 

[Darren Shaffer]

what's the schema of the cioxsitelist table on device?
just tell me column name, type, allows null, constraints, etc.

i've seen this happen when there is an identity range specified
on the table before merge.
--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com

 

[Guest]

I tried dropping the CE DB and recreating it, and for reasons unknown, my
update of the CE DB started working. Go figure!

Bill

 

[Darren Shaffer]

that's good news Bill. would you mind still posting your schema?

I handle most of the questions in the sqlserver.ce newsgroup and
every once in a while I see this issue and can never seem to get
enough info to recreate it. here's how it normally happens: some table
on the server has a number of constraints that are not supported by
SQL CE. the table is published and all but the PK is dropped in
the process of converting the table for use in SQL CE. you try
some DML against the table on device. the database becomes
corrupt somehow (which you solved by dropping and re-creating it)
and the table seems unable to handle UPDATE commands. I've seen
a repair operation in the DB fix this, but it's not consistent.

if you could tell me the schema of that table (as it stands on the server),
I might be able to recreate this and get it escalated to MS.

thanks,
--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com