DataSet/DataTable AcceptChanges() and SqlCeAdapter.Update() confusion

  • Thread starter Thread starter PeterB
  • Start date Start date
P

PeterB

Hi!

I have tried to find information on this subject, and several posts has
taken it up but none has given me the answer I need. I believe it's my lack
of AcceptChanges()-calls that is the reason for my problem but I can't seem
to realize when to make them.

I use custom sql-queries for my SqlCeAdapter commands. Everything works just
fine, I can add, update and select, but I can't delete! Or, the deletions
made in the datatable is not reflected in the database for some reason.

I use a DataSet in which I have only one table. I fill the table with the
Fill() method of the SqlCeDataAdapter class setting the only parameter
needed for the select query. The data is displayed in a datagrid. I add a
row by simply setting the required parameters of the Insert sql-query and
execute the Update() method of the adapter. This works fine. I do not call
AcceptChanges(), and the data is inserted into the database.

If I then want to remove a row in the datatable I use the RemoveAt(indx)
method and this works fine on the datatable, i.e. data is removed from the
datatable. I then call adapter.Update() method to make sure the database is
updated as well, but this is where I have no luck.

The code below explains how I have intialized the datatable and adapter, and
how I call the different methods in order to remove and add data. What am I
doing wrong here? Where should the acceptchanges() be inserted?

public void InitializeDataSet()
{
dsStolp = new DataSet("STOLP");
dtFel = new DataTable("FEL");
dsStolp.Tables.Add(dtFel);

// STOLPNR is used to identify a set of fel
// Select command text = "SELECT ... all columns ... FROM FEL WHERE STOLPNR
= ?"

// Delete command
// LOPNR is the primary key of the fel-table
// sqlConn is an open SqlCeConnection
string delTxt = "DELETE FROM FEL WHERE LOPNR = ?";
SqlCeCommand sqlFelDelCmd = new SqlCeCommand( delTxt, sqlConn );
sqlFelDelCmd.Parameters.Add(new SqlCeParameter("lopnr", SqlDbType.Int, 0,
"lopnr") );

sqlAdaptFel = new SqlCeDataAdapter("", sqlConn);

// The commands are created elsewhere...
sqlAdaptFel.SelectCommand = sqlFelSelCmd;
sqlAdaptFel.UpdateCommand = sqlFelUpdCmd;
sqlAdaptFel.InsertCommand = sqlFelInsCmd;
sqlAdaptFel.DeleteCommand = sqlFelDelCmd;
}

// Save all changes to db
public void SaveFelDataToDb()
{
sqlAdaptFel.Update(dtFel);
}

// Delete fel
public void DelFelFromTable( )
{
int rowIndx = this.dGridFel.CurrentRowIndex;
dtFel.Rows.RemoveAt( rowIndx );
SaveFelDataToDb(); // Update changes
}

// Get fel-subset from db
public void GetFelDataFromDb( int stolpnr )
{
// lopnr is primary key of the fel-table
sqlFelSelCmd.Parameters["stolpnr"].Value = stolpnr;
sqlAdaptFel.Fill( dtFel );
}

// Add fel
public static void AddRowToFelTable( )
{
DataRow row = DataBase.dtFel.NewRow();

... Add values to all required fields ...

dtFel.Rows.Add( row );

DataBase.SaveFelDataToDb(); // Update changes
}
 
Ah... thanks a lot! I knew it was easy :-)

/ P

Peter Foot said:
There are two ways of removing a row. One is to Delete the row (where it
actually remains in the table but marked as deleted. The other is to remove
the row from the table - it is then detached and the change will not be
reflected in the database following an Update().

Instead of removing the Row from the collection, mark the row as deleted
using:-
ds.Tables[0].Rows[0].Delete();

Then when the Update method looks at your changed rows it will noticed the
Deleted state of the row and execute the required Delete statement for the
row. Once the change is committed to the database the provider should call
AcceptChanges on your dataset automatically at which point your row is
permanently deleted from the datatable.



Peter


--
Peter Foot
Windows Embedded MVP

In The Hand
http://www.inthehand.com

PeterB said:
Hi!

I have tried to find information on this subject, and several posts has
taken it up but none has given me the answer I need. I believe it's my lack
of AcceptChanges()-calls that is the reason for my problem but I can't seem
to realize when to make them.

I use custom sql-queries for my SqlCeAdapter commands. Everything works just
fine, I can add, update and select, but I can't delete! Or, the deletions
made in the datatable is not reflected in the database for some reason.

I use a DataSet in which I have only one table. I fill the table with the
Fill() method of the SqlCeDataAdapter class setting the only parameter
needed for the select query. The data is displayed in a datagrid. I add a
row by simply setting the required parameters of the Insert sql-query and
execute the Update() method of the adapter. This works fine. I do not call
AcceptChanges(), and the data is inserted into the database.

If I then want to remove a row in the datatable I use the RemoveAt(indx)
method and this works fine on the datatable, i.e. data is removed from the
datatable. I then call adapter.Update() method to make sure the database is
updated as well, but this is where I have no luck.

The code below explains how I have intialized the datatable and adapter, and
how I call the different methods in order to remove and add data. What
Click to expand...
am
I
doing wrong here? Where should the acceptchanges() be inserted?

public void InitializeDataSet()
{
dsStolp = new DataSet("STOLP");
dtFel = new DataTable("FEL");
dsStolp.Tables.Add(dtFel);

// STOLPNR is used to identify a set of fel
// Select command text = "SELECT ... all columns ... FROM FEL WHERE STOLPNR
= ?"

// Delete command
// LOPNR is the primary key of the fel-table
// sqlConn is an open SqlCeConnection
string delTxt = "DELETE FROM FEL WHERE LOPNR = ?";
SqlCeCommand sqlFelDelCmd = new SqlCeCommand( delTxt, sqlConn );
sqlFelDelCmd.Parameters.Add(new SqlCeParameter("lopnr", SqlDbType.Int, 0,
"lopnr") );

sqlAdaptFel = new SqlCeDataAdapter("", sqlConn);

// The commands are created elsewhere...
sqlAdaptFel.SelectCommand = sqlFelSelCmd;
sqlAdaptFel.UpdateCommand = sqlFelUpdCmd;
sqlAdaptFel.InsertCommand = sqlFelInsCmd;
sqlAdaptFel.DeleteCommand = sqlFelDelCmd;
}

// Save all changes to db
public void SaveFelDataToDb()
{
sqlAdaptFel.Update(dtFel);
}

// Delete fel
public void DelFelFromTable( )
{
int rowIndx = this.dGridFel.CurrentRowIndex;
dtFel.Rows.RemoveAt( rowIndx );
SaveFelDataToDb(); // Update changes
}

// Get fel-subset from db
public void GetFelDataFromDb( int stolpnr )
{
// lopnr is primary key of the fel-table
sqlFelSelCmd.Parameters["stolpnr"].Value = stolpnr;
sqlAdaptFel.Fill( dtFel );
}

// Add fel
public static void AddRowToFelTable( )
{
DataRow row = DataBase.dtFel.NewRow();

... Add values to all required fields ...

dtFel.Rows.Add( row );

DataBase.SaveFelDataToDb(); // Update changes
}
Click to expand...
Click to expand...
 
Back
Top