kkamm
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.DeleteCo mmand = MPLCommand("DELETE * FROM " & _
"Answer WHERE PK=" & Row)
AnswerDataAdapter.DeleteCo mmand.Exec uteNonQuer y()
Me.AnswerDataAdapter.Updat e(Me.Answe rDataTable )
Me.MPLDataSet.AcceptChange s()
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.Ad d(drAnswer )
Dim drModifiedAdded As DataRow() = Me.MPLDataSet.Tables(dtAns wer.TableN ame).Selec t(Nothing, Nothing, DataViewRowState.Added)
Me.drAnswer.EndEdit()
Try
Me.AnswerDataAdapter.Updat e(drModifi edAdded)
Me.dtAnswer.AcceptChanges( )
Me.UpdateAutoCompleteLists ()
Catch Ex As DbException
Dim errormsg As String()
errormsg = Ex.Message.Split(".")
Form1.ToolStripStatusLabel 1.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.
AnswerDataAdapter.DeleteCo
"Answer WHERE PK=" & Row)
AnswerDataAdapter.DeleteCo
Me.AnswerDataAdapter.Updat
Me.MPLDataSet.AcceptChange
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.Ad
Dim drModifiedAdded As DataRow() = Me.MPLDataSet.Tables(dtAns
Me.drAnswer.EndEdit()
Try
Me.AnswerDataAdapter.Updat
Me.dtAnswer.AcceptChanges(
Me.UpdateAutoCompleteLists
Catch Ex As DbException
Dim errormsg As String()
errormsg = Ex.Message.Split(".")
Form1.ToolStripStatusLabel
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Re move(drAns wer)
Dim drModified As DataRow() = Me.MPLDataSet.Tables(Me.An swerDataTa ble.TableN ame).Selec t(Nothing, Nothing, DataViewRowState.Deleted)
Me.drAnswer.EndEdit()
Try
Me.AnswerDataAdapter.Updat e(drModifi ed)
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?
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.Re
Dim drModified As DataRow() = Me.MPLDataSet.Tables(Me.An
Me.drAnswer.EndEdit()
Try
Me.AnswerDataAdapter.Updat
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?
>>
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.DeleteCo mmand = MPLCommand("DELETE * FROM " & _
"Answer WHERE PK=" & Row)
AnswerDataAdapter.DeleteCo mmand.Exec uteNonQuer y()
Me.AnswerDataTable.Clear()
Me.AnswerDataAdapter.Fill( Me.AnswerD ataTable)
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.Updat e(Me.Answe rDataTable )
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
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.DeleteCo
"Answer WHERE PK=" & Row)
AnswerDataAdapter.DeleteCo
Me.AnswerDataTable.Clear()
Me.AnswerDataAdapter.Fill(
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(
For Each dr As DataRow in foundrows
dr.Delete
Next
Me.AnswerDataAdapter.Updat
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
ASKER
Roger,
I think your responses will get me on the right path. I appreciate the input. Thank you.
I think your responses will get me on the right path. I appreciate the input. Thank you.
Me.MPLDataSet.AcceptChange