When binding the DataSet to the Datagrid control
and using the DataAdapter object to modify the database,
such as:
dim adp as new OleDbDataAdapter(stradp,conn)
dim ocb as new OleDbCommandBuilder(adp)
adp.DeleteCommand = ocb.GetDeleteCommand()
adp.Update(ds,"Orders")
-----------------------------------------
When performing the deletion operation, if we add a method like this:
sub mydatagrid_delete(sender as object, e as datagridcommandeventargs)
dim dt as new DataTable()
dt = ds.Tables("Orders")
dim dr as DataRow
dr = dt.Rows(E.Item.ItemIndex)
dr.delete
'dr.AcceptChanges 'I once tried to use complete deletion and found that when adp automatically updated back to the database, the corresponding sql statement could not be automatically generated
' to solve the page index exception that occurred when deleting the last item of the current page
'************************************************ ****************
dim lastEditPage as integer = mydatagrid.currentPageIndex
If (mydatagrid.pageCount - mydatagrid.currentPageIndex) = 1 and mydatagrid.Items.Count = 1 Then
If mydatagrid.pageCount > 1 Then
lastEditPage = LastEditPage - 1
Else
lastEditPage = 0
End If
End If
mydatagrid.currentPageIndex = lastEditPage
'************************************************ ***************
session("orderList") = ds
mydatagrid.edititemindex = -1
mydatagrid.datasource = ds.tables("Orders")
mydatagrid.databind()
end sub
An exception occurred when deleting the first one one by one. It was found that when deleting the second one, it could not be deleted, that is, the original dr(2) did not automatically become dr(1). If we use dr.delete dr.acceptChanges, the changes can be made automatically, but as explained above, automatic updates cannot be used to return to the database. We must obtain the actual index when deleting, so we used this method to create another Table in the current ds to maintain synchronous deletion, but save the actual index value in the id column of the Table. The specific code is as follows:
A way to solve the dr index:
dim orderTable as new DataTable() 'Create a temporary table to save the index and maintain synchronized deletion
dim theNewRow as DataRow
dim dc as DataColumn
orderTable.TableName = "orderId"
ds.Tables.add(orderTable)
dc = new DataColumn()
dc.ColumnName = "id"
orderTable.columns.add(dc)
dim dcKey() as DataColumn = {orderTable.Columns("id")}
orderTable.primaryKey = dcKey
dim i as integer
For i = 0 to (ds.Tables("Users").Rows.Count - 1)
theNewRow = orderTable.NewRow()
theNewRow("id") = i.toString()
orderTable.Rows.add(theNewRow)
Next
code added in the above delete function, replace dr = dt.Rows(e.Item.ItemIndex):
dim drOrder as DataRow
drOrder = ds.Tables("orderId").Rows(E.Item.ItemIndex)
dim currentOrder as integer = CInt(drOrder("id")) + mydatagrid.currentPageIndex * mydatagrid.PageSize
dr = dt.Rows(currentOrder)
drOrder.delete
If there is an update function, replace dr = dt.Rows(e.Item.ItemIndex):
dim drOrder as DataRow
drOrder = ds.Tables("orderId").Rows(E.Item.ItemIndex)
dim currentOrder as integer = CInt(drOrder("id")) + mydatagrid.currentPageIndex * mydatagrid.PageSize
dr = dt.Rows(currentOrder)