updating MS Access table from datagrid

[yzlin04]

I'm using VB.net 2003. I can delete a row in datagrid (dgTempTransact ion), but my MS Access table can't directly being updated. The row i deleted is still exist in my MS Access, but in my datagrid, it already being deleted.
The table name in my MS Access: tableTempTransa ction.

Here is my code for that part...i don't know where is the error. Please help me..... Thanks anyway.

Dim currManager As CurrencyManager

If IsNothing(DsTem pTransaction1) Then Exit Sub

currManager = CType(BindingCo ntext(DsTempTra nsaction1, "tableTempTrans action"), CurrencyManager )

'get the current row
Dim dRow As DataRow = CType(currManag er.Current, DataRowView).Ro w

'prompt to save changes
If MsgBox("Are you sure want to delete this item?", MsgBoxStyle.OKC ancel, "Hands On Programming") = MsgBoxResult.OK Then
'delete it from the dataset
dRow.Delete()

'get just the changed data
Dim dsChanged As DataSet = DsTempTransacti on1.GetChanges
'>>>Problem in this part...table inside MS Access cannot be updated.
Else
'reset the dataset
DsTempTransacti on1.RejectChang es()
End If

[kenobewan]

It looks to me as though you deleting the row but not using sql to delete in access. HTH.

[yzlin04]

It looks to me as though you deleting the row but not using sql to delete in access. HTH.

but how to update it from the dataset by using SQL?

[radcaesar]

Until u use the DataAdapter.Upd ate() method, it will not update physically in the DB.

if (okayFlag)
{
// apply updates to the database
dataAdapter.Upd ate(dsChanges," PhoneNumbers");
// tell the user
MessageBox.Show ("Updated " + selectedRow["Phonenum"]);
Application.DoE vents();
// apply changes and refresh the listbox
dataSet.AcceptC hanges();
Fill_lb();
}
else // if any errors then throw out the changes
dataSet.RejectC hanges();

[yzlin04]

Until u use the DataAdapter.Upd ate() method, it will not update physically in the DB.

if (okayFlag)
{
// apply updates to the database
dataAdapter.Upd ate(dsChanges," PhoneNumbers");
// tell the user
MessageBox.Show ("Updated " + selectedRow["Phonenum"]);
Application.DoE vents();
// apply changes and refresh the listbox
dataSet.AcceptC hanges();
Fill_lb();
}
else // if any errors then throw out the changes
dataSet.RejectC hanges();

When i try to add the sample code u provided, the following error occured:

An unhandled exception of type 'System.Invalid OperationExcept ion' occurred in system.data.dll

Additional information: Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.



Code:

If IsNothing(DsTem pTransaction1) Then Exit Sub

currManager = CType(BindingCo ntext(DsTempTra nsaction1, "tableTempTrans action"), CurrencyManager )

'get the current row
Dim dRow As DataRow = CType(currManag er.Current, DataRowView).Ro w

'prompt to save changes
If MsgBox("Are you sure want to delete this item?", MsgBoxStyle.OKC ancel, "Hands On Programming") = MsgBoxResult.OK Then
'delete it from the dataset
dRow.Delete()

'get just the changed data
Dim dsChanged As DataSet = DsTempTransacti on1.GetChanges
'>>THIS LINE>> OleDbDataAdapte r1.Update(dsCha nged, "tableTempTrans action")
MessageBox.Show ("Delete Successful...", "Delete", MessageBoxButto ns.OK, MessageBoxIcon. Information)
Application.DoE vents()
DsTempTransacti on1.AcceptChang es()
Else
'reset the dataset
DsTempTransacti on1.RejectChang es()
MessageBox.Show ("Delete failed...", "Delete", MessageBoxButto ns.OK, MessageBoxIcon. Information)
End If

[VBStarterKid]

When i try to add the sample code u provided, the following error occured:

An unhandled exception of type 'System.Invalid OperationExcept ion' occurred in system.data.dll

Additional information: Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.



Code:

If IsNothing(DsTem pTransaction1) Then Exit Sub

currManager = CType(BindingCo ntext(DsTempTra nsaction1, "tableTempTrans action"), CurrencyManager )

'get the current row
Dim dRow As DataRow = CType(currManag er.Current, DataRowView).Ro w

'prompt to save changes
If MsgBox("Are you sure want to delete this item?", MsgBoxStyle.OKC ancel, "Hands On Programming") = MsgBoxResult.OK Then
'delete it from the dataset
dRow.Delete()

'get just the changed data
Dim dsChanged As DataSet = DsTempTransacti on1.GetChanges
'>>THIS LINE>> OleDbDataAdapte r1.Update(dsCha nged, "tableTempTrans action")
MessageBox.Show ("Delete Successful...", "Delete", MessageBoxButto ns.OK, MessageBoxIcon. Information)
Application.DoE vents()
DsTempTransacti on1.AcceptChang es()
Else
'reset the dataset
DsTempTransacti on1.RejectChang es()
MessageBox.Show ("Delete failed...", "Delete", MessageBoxButto ns.OK, MessageBoxIcon. Information)
End If

REPLY: I believe your problem could very well be that you have not adapted the OleCommandBuild er into your data adapter of your DataSet.
If you want to make your dataset to be able to make direct changes to the MS Access table, you need to enable the SQL UPDATE for your dataset and very important is that should be made before you apply the DataAdapter.Upd ate() method. To do this, OleCommandBuild er must be enabled to make yor DataSet open up the INSERT, UPDATE and DELETE options.

The tutorial on this, may help you:

http://vb-helper.com/howto_net_datagrid.html