SqlCEDataAdapter update method deletes all rows

[David Brunning]

I have a table in SqlCe and am using a datatable object, dataadapter
and commandbuilder object to persist changes back to the SqlCe
database.

If I insert or update rows in the datatable the update method persists
my changes to the database without a problem. If I delete a row the
update method removes ALL rows from the database.

By stepping through the code I can see that after calling the update
method of the dataadapter and the accept changes method on the
datatable the datatable has the correct number of rows in it. if I
then run SqlCe ISQL and query that table directly in the database I
find there are no rows at all...

Has anyone seen this happening? I am pretty well stumped...

Thanks

 

[David Brunning]

I have mocked up a new application which behaves in exactly the same
way. The issue appears to be in the use of the CommandBuilder. If I
explicitly provide insert, update, select and delete commands this all
appears to work correctly.

Is this a bug in the command builder?

 

[Yunwen Bai [MS]]

Hi David, could you provide me a a repro code snippet ?

Regards!

Yunwen Bai
Sql Server CE

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Yunwen Bai [MS]]

And also, can you check the ado.DeleteCommand.CommandText to see what it is
?

Thanks,


Yunwen Bai
Sql Server CE

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Brunning]

Here is the update code:
--------------------------
SqlCeDataAdapter TempAdapter=new SqlCeDataAdapter();
SqlCeCommandBuilder CommandBuilder=new
SqlCeCommandBuilder(TempAdapter);
TempAdapter.SelectCommand=this.BuildSelectCommand();

try
{
TempAdapter.Update(this.mtblStgAssetAnalysisLink);
}
catch (System.Exception exc)
{
string Temp=exc.Message;
}

this.mtblStgAssetAnalysisLink.AcceptChanges();
--------------------------

Where the select command is built by:

--------------------------
SqlCeCommand SelectCommand=new SqlCeCommand();
SelectCommand.CommandType=CommandType.Text;
SelectCommand.CommandText="select intAssetID, intAnalysisClassID,
intAnalysisTypeID, rowguid, dteDateUpdated from
tblStgAssetAnalysisLink ";
SelectCommand.Connection=this.mConn;
return SelectCommand;

 

[Yunwen Bai [MS]]

is mtblStgAssetAnalysisLink filled by the same SqlCeDataAdapter ?

and also, Have you checked the DeleteCommand's commandtext ?

Thanks,

Yunwen Bai
Sql Server CE

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

I may have the same problem as you have described and have tracked down the issue to how the command builder builds the delete command .
My file structure is typical header/detail with primary key of header say 'order id' and for the detail file "order id' column PLUS 'line nbr' column .. this 2 column primary key may be the problem .

When creating detail file I used SQL to alter table to have a Primary Key constraint identifing both 'orderId' and 'lineNbr' columns. futhermore after the data adapter has filled the dataset and the dataset assigned to the datatable I added a 2 column primary key to the data table using 'datatable.primarykey = ' method .

when I then ask command builder to create insert/delete/update commands the UPDATE command correctly included the proper selection: ie ' update ..... where orderId=? and lineNbr=? ' ... HOWEVER the delete command says only 'delete ... where order_id=?' and thus when I delete a single row (ie single line nbr) all lines are deleted .

could this be similar to your setup


----- Yunwen Bai [MS] wrote: ----

is mtblStgAssetAnalysisLink filled by the same SqlCeDataAdapter

and also, Have you checked the DeleteCommand's commandtext

Thanks

Yunwen Ba
Sql Server C

This posting is provided "AS IS" with no warranties, and confers no rights

 

[Brian Sabino [MS]]

This is a Bug in Sql CE (CommandBuilder does not generate a proper Delete
Command against a table with a compound primary key). Can you workaround
this issue by using your own DeleteCommand?

--
This posting is provided "AS IS" with no warranties, and confers no rights.

I may have the same problem as you have described and have tracked down

the issue to how the command builder builds the delete command ..

My file structure is typical header/detail with primary key of header say

'order id' and for the detail file "order id' column PLUS 'line nbr' column
... this 2 column primary key may be the problem ..

When creating detail file I used SQL to alter table to have a Primary Key

constraint identifing both 'orderId' and 'lineNbr' columns. futhermore
after the data adapter has filled the dataset and the dataset assigned to
the datatable I added a 2 column primary key to the data table using
'datatable.primarykey = ' method ..

when I then ask command builder to create insert/delete/update commands

the UPDATE command correctly included the proper selection: ie ' update
...... where orderId=? and lineNbr=? ' ... HOWEVER the delete command says
only 'delete ... where order_id=?' and thus when I delete a single row (ie
single line nbr) all lines are deleted ..

could this be similar to your setup?



----- Yunwen Bai [MS] wrote: -----

is mtblStgAssetAnalysisLink filled by the same SqlCeDataAdapter ?

and also, Have you checked the DeleteCommand's commandtext ?

Thanks,

Yunwen Bai
Sql Server CE

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

mmmm .. not sure .

I'm updating SQL CE data after it's been modified (add/change/delete) within a datagrid ... I remove the row from the datagrid using: myCurrencymaster.removeAt(currencymaster.position
and add rows using the datatable's rows.add method ..

the data adaptor's .udpate handles all the the grid add, changes and deletes ..so I can test the dataset's 'haschanges.deleted' property and know that some rows have been deleted but I assume to execute my own delete I will have to trap and save the row's key values at the 'removeAt' stage ? or is there a way of reading thru the datatable rows and tested for deleted rows

also I still want to execute the command builders' .update command to let it handle the adds/modifies .. but will it also try to execute the deletes because the haschanges.deleted is set?? I think the haschanged. properties are 'read only' so I wont' be able to 'manually' turn off the .deleted will I ?

p.s. thanks for the quick response - wished I'd check the newsgroups sooner

brad elliot

 

[Brian Sabino [MS]]

I'm proposing that you just set your own DeleteCommand in the Data Adapter
as in the sample code below. The CommandBuilder doesn't do anything magical
(in fact we almost didn't ship it). It's merely a helper class so that in
some cases you can avoid creating commands by hand. In fact you'll get
better perf if you hand craft your Insert, Update, and Delete commands.

//Create and connect to DB

string dbFile = @"\temp.sdf";

string cnString = "Data Source = " + dbFile;

if(File.Exists(dbFile))

File.Delete(dbFile);

SqlCeEngine en = new SqlCeEngine(cnString);

en.CreateDatabase();

en.Dispose();

SqlCeConnection cn = new SqlCeConnection(cnString);

cn.Open();

//Create Test Schema

SqlCeCommand cmd = cn.CreateCommand();

cmd.CommandText = "Create Table t1 (col1 int, col2 nvarchar(20), col3
uniqueidentifier DEFAULT newid(), primary key (col1,col2));";

cmd.ExecuteNonQuery();

cmd.CommandText = "Insert into t1 (col1,col2) values (5,'Hello');";

cmd.ExecuteNonQuery();

cmd.CommandText = "Insert into t1 (col1,col2) values (5,'Hello2');";

cmd.ExecuteNonQuery();

cmd.Dispose();

//Setup DataAdapter

SqlCeDataAdapter da = new SqlCeDataAdapter("Select * from t1",cn);

SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);

da.InsertCommand = cb.GetInsertCommand();

da.UpdateCommand = cb.GetUpdateCommand();


//Create custom delete command

da.DeleteCommand = new SqlCeCommand("DELETE FROM t1 WHERE col1 = ? and col2
= ?",cn);

SqlCeParameter p1 = new SqlCeParameter("@param1",SqlDbType.Int);

p1.SourceVersion = DataRowVersion.Original;

p1.SourceColumn = "col1";

SqlCeParameter p2 = new SqlCeParameter("@param2",SqlDbType.NVarChar);

p2.SourceVersion = DataRowVersion.Original;

p2.SourceColumn = "col2";

da.DeleteCommand.Parameters.Add(p1);

da.DeleteCommand.Parameters.Add(p2);

//Now that the DataAdapter commands are populated the DA can be used
normally

DataSet ds = new DataSet();

da.Fill(ds);

ds.Tables[0].Rows[0].Delete();

da.Update(ds);

--
This posting is provided "AS IS" with no warranties, and confers no rights.

mmmm .. not sure .

I'm updating SQL CE data after it's been modified (add/change/delete)

within a datagrid ... I remove the row from the datagrid using:
myCurrencymaster.removeAt(currencymaster.position)

and add rows using the datatable's rows.add method ..

the data adaptor's .udpate handles all the the grid add, changes and

deletes ..so I can test the dataset's 'haschanges.deleted' property and
know that some rows have been deleted but I assume to execute my own delete
I will have to trap and save the row's key values at the 'removeAt' stage ?
or is there a way of reading thru the datatable rows and tested for deleted
rows?

also I still want to execute the command builders' .update command to let

it handle the adds/modifies .. but will it also try to execute the deletes
because the haschanges.deleted is set?? I think the haschanged. properties
are 'read only' so I wont' be able to 'manually' turn off the .deleted will
I ??

 

[Guest]

I am trying to do what you have suggested, but receive a System.InvalidOperationException when I call the Updat
of the SqlCeDataAdapter
We have our own DAO layer, so the things I am trying to do are more generic
- Before calling the DataRow.Delete(), I build the DeleteCommand based on the row (taking the primary key info from DataRow.Table.Primarykey, building the where clause based on that and adding all the parameters with the right SqlDataType, SourceColumn and SourceVersion (Original)
- Then I call DataRow.Delete(
- I create the DbAdapte
- I create the CommandBuilder (as the insert and update commands seem to be fine, so I don't want to generate them
- I set the DbDataAdapter's DeleteCommand with the command I have built on the first step
- I call DbDataAdapter.Update and the the exception (the docu says: "The source table is invalid."

Have you got an idea what's wrong with my approach

Thanks