SqlCE Deletion of record from DataSet

  • Thread starter Thread starter Chris Voon
  • Start date Start date
C

Chris Voon

I have some records in SQL CE 2.0 and would like to delete
all the rows. I obtain the data using SQLCE data adapter
and DataSet and managed to populate the data. Following i
tried to issue a delete + acceptchanges on to the rows and
do an update. The records are not deleted! I also try with
the removeat method which seems to have worked fine in
removing the rows. But ultimately when an update method is
invoked, nothing has changed. Here's the code I use.

Try
Dim strSQL = "SELECT * FROM JOB_HEADER WHERE
job_id='PPC00020031212121212'"

Dim myda As New SqlCeDataAdapter
myda.SelectCommand = New SqlCeCommand("SELECT
* FROM JOB_DELAY WHERE job_id='PPC00020031212121212'",
m_jobConn)
Dim cb2 As SqlCeCommandBuilder = New
SqlCeCommandBuilder(myda)

Dim ds As DataSet = New DataSet
myda.Fill(ds, "JOB_DELAY")

' Trying to Issue a delete command for old
records
Dim rowIndex As Integer
While ds.Tables("JOB_DELAY").Rows.Count > 0
ds.Tables("JOB_DELAY").Rows.RemoveAt(0)
End While

myda.Update(ds, "JOB_DELAY")
Catch ex As SqlCeException
ToolSet.ErrorHelper.ShowSqlCeErrors(ex)
End Try
 
Rather than removing the rows from the collection you should call the
Delete() method on each row. This way they are not removed from the DataSet
but marked as deleted and when you call DataAdapter.Update() this will
translate into a Delete statement being sent to the database. e.g.

Dim rowIndex As Integer
While ds.Tables("JOB_DELAY").Rows.Count > 0
ds.Tables("JOB_DELAY").Rows(0).Delete
End While

myda.Update(ds, "JOB_DELAY")

Peter
 
About me the better solution is:
dim sqlCmd as sqlCeCommand
sqlCmd=new New SqlCeCommand("DELETE FROM JOB_DELAY WHERE
job_id='PPC00020031212121212'", m_jobConn)
sqlCmd.executenonquery

But if you want to use your solution, you need to get
the DeleteCommand from CommandBuilder before myda.Fill
in this way:
myda.deletecommand=cb2.GetDeleteCommand
 
Dear Peter,

The method you showed was one that I've tried earlier and
it actually causes an endless loop within the while
statement.
 
Back
Top