DELETE using OLEDB Command object reflected in MDB but NOT in dataset

I am using an OLEDB command to delete a given row from an MDB :

            AnswerDataAdapter.DeleteCommand = MPLCommand("DELETE * FROM " & _
            "Answer WHERE PK=" & Row)

            AnswerDataAdapter.DeleteCommand.ExecuteNonQuery()

            Me.AnswerDataAdapter.Update(Me.AnswerDataTable)

            Me.MPLDataSet.AcceptChanges()


Seems to work fine with the MDB but I run into all sorts of concurrency issues with regards to the respective dataset.

I am also doing INSERTIONS but I haven't had any problems with concurrency there:

        drAnswer = Me.AnswerDataTable.NewRow()

        drAnswer.BeginEdit()

        drAnswer("UniqueID") = UniqueID
        drAnswer("Answer") = Answer
        drAnswer("InputMask") = InputMask

        Me.AnswerDataTable.Rows.Add(drAnswer)

        Dim drModifiedAdded As DataRow() = Me.MPLDataSet.Tables(dtAnswer.TableName).Select(Nothing, Nothing, DataViewRowState.Added)

        Me.drAnswer.EndEdit()

        Try

            Me.AnswerDataAdapter.Update(drModifiedAdded)

            Me.dtAnswer.AcceptChanges()

            Me.UpdateAutoCompleteLists()

        Catch Ex As DbException

            Dim errormsg As String()
            errormsg = Ex.Message.Split(".")
            Form1.ToolStripStatusLabel1.Text = errormsg(0) & "..."

            Me.dtAnswer.RejectChanges()

        End Try



My question is this: Should I use the same construct to do DELETIONS as I use for INSERTIONS? In other words, should I mark a row for deletion in the dataset and then do an Update and NOT use the OLEDB COMMAND object to do the deletion? If so, will this take care of both the MDB update AND the dataset update such that they are synchronized?

Sorry if this seems remedial but the many-paths-to-one-solution aspect of .Net databasing sometimes leads me to conceptual bottlenecks.




LVL 1
kkammAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SanclerConnect With a Mentor Commented:
A dataadapter's .Update command works by checking the .RowState of rows in the relevant datatable.  If it finds any .Deleted it runs the Delete command on those.  If it finds any .Added it runs the Insert commmand on those.  If it finds any .Modified it runs the .Update command on those.  As you are deleting a record with a direct command to the database, using ExecuteNonQuery, without - so far as I can see - doing anything to the datatable, the lines

            Me.AnswerDataAdapter.Update(Me.AnswerDataTable)

            Me.MPLDataSet.AcceptChanges()

will do nothing.  Or at least nothing relating to the row you want to delete.

So, yes, the answer to the question in your penultimate paragraph is "yes".  You should use the same approach as you do for insertions.  That is, delete the row in the datatable and then call the dataadapter's .Update command.  That will then do the same deletion from the database and they will remain in synch.

Roger
0
 
YZlatCommented:
try getting rid of the line

 Me.MPLDataSet.AcceptChanges()
0
 
kkammAuthor Commented:
That is what I suspected the case was.

 I changed my DELETE code to match the INSERT code:

  Dim dr As DataRow

        For Each dr In Me.AnswerDataTable.Rows 'Needed to grab specific row from datatable
            If dr.Item(3) = Row Then
                drAnswer = dr

            End If

        Next

        drAnswer.BeginEdit()
        Me.AnswerDataTable.Rows.Remove(drAnswer)
        Dim drModified As DataRow() = Me.MPLDataSet.Tables(Me.AnswerDataTable.TableName).Select(Nothing, Nothing, DataViewRowState.Deleted)
        Me.drAnswer.EndEdit()

        Try
                Me.AnswerDataAdapter.Update(drModified)
         
         Catch Ex As Exception

            MsgBox(Ex.message)

        End Try

It seems like it should work but I am now getting indexing errors,etc. Part of this process is that I am having the users select from a DataGridView which row they want to delete and then feeding that to the routine above.

This leads me to another question: Will a DataGridView which is databound to my datasource allow me to bypass all this plumbing and take care of the deletions on the fly or are the deletions just a data display feature which leave the backend database untouched?
0
 
SanclerCommented:
>>
Will a DataGridView which is databound to my datasource allow me to bypass all this plumbing and take care of the deletions on the fly or are the deletions just a data display feature which leave the backend database untouched?
<<

The DataGridView is the display/editing mechanism.  The data on which it works is that in the bound datasource in the application.  The link between the datasource in the application and the database is provided by the dataadapter.  So you can "bypass all this plumbing and take care of the deletions on the fly" but - as you discovered - it will leave the datasource in the application and the database out of synch.  If you want to tackle things that way, you can.  But to re-synch the database and the datasource in the application you would need to use the dataadapter's .Fill method on the relevant datasource again.  So, going back to your original code, it would go something like this

            AnswerDataAdapter.DeleteCommand = MPLCommand("DELETE * FROM " & _
            "Answer WHERE PK=" & Row)

            AnswerDataAdapter.DeleteCommand.ExecuteNonQuery()

            Me.AnswerDataTable.Clear()

            Me.AnswerDataAdapter.Fill(Me.AnswerDataTable)

If you want to do the deletion from the datasource in the application and then use the dataadapter to pass that deletion on to the database your code should go something like this

  Dim foundrows() As DataRow = Me.AnswerDataTable.Select("PK = " & Row)
  For Each dr As DataRow in foundrows
     dr.Delete
  Next
  Me.AnswerDataAdapter.Update(Me.AnswerDataTable)

The problem with your current code for this approach - besides it being more convoluted than is really necessary ;-) - is that it uses the .Remove method.  As the help file says "When a row is removed, all data in that row is lost. You can also call the Delete method of the DataRow class to just mark a row for removal. Calling Remove is the same as calling Delete and then calling AcceptChanges."  Going back to what I said in my earlier post, this means that when the dataadapter looks for a .Deleted flag it doesn't find one - the row has disappeared altogether, rather than having its .Deleted flag set - so it does nothing.

Roger
0
 
kkammAuthor Commented:
Roger,
I think your responses will get me on the right path. I appreciate the input. Thank you.
0
All Courses

From novice to tech pro — start learning today.