Link to home
Start Free TrialLog in
Avatar of kkamm
kkammFlag for United States of America

asked on

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.




Avatar of YZlat
YZlat
Flag of United States of America image

try getting rid of the line

 Me.MPLDataSet.AcceptChanges()
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kkamm

ASKER

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?
Avatar of Sancler
Sancler

>>
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
Avatar of kkamm

ASKER

Roger,
I think your responses will get me on the right path. I appreciate the input. Thank you.