My DataGrid is bound to a DataSet and showing only those records whose Client_Liability_ID IS NULL and I am using RowFilter to do so. Following is the code:
'Set Select Command for WithdrawalDA.
WithdrawalDA.SelectCommand = New SqlCommand
With WithdrawalDA.SelectCommand
.Connection = myConn
.CommandType = CommandType.StoredProcedure
.CommandText = "spSelectWithdrawals"
.Parameters.Clear()
.Parameters.Add("@Plan_ID", Me.PlanID)
End With
'Fill DataSet.
WithdrawalDA.FillSchema(myDataSet, SchemaType.Mapped, "Withdrawals")
WithdrawalDA.Fill(myDataSet, "Withdrawals")
'Set Insert Command for WithdrawalDA.
WithdrawalDA.InsertCommand = New SqlCommand
With WithdrawalDA.InsertCommand
.Connection = myConn
.CommandType = CommandType.StoredProcedure
.CommandText = "spInsertWithdrawals"
.Parameters.Clear()
.Parameters.Add("@Plan_ID", SqlDbType.Int, 4, "Plan_ID")
.Parameters.Add("@Client_Liability_ID", SqlDbType.Int, 4, "Client_Liability_ID")
.Parameters.Add("@WithdrawalDate", SqlDbType.DateTime, 8, "WithdrawalDate")
.Parameters.Add("@Amount", SqlDbType.Money, 8, "Amount")
.Parameters.Add("@Reason", SqlDbType.VarChar, 60, "Reason")
End With
'Set Update Command for WithdrawalDA.
WithdrawalDA.UpdateCommand = New SqlCommand
With WithdrawalDA.UpdateCommand
.Connection = myConn
.CommandType = CommandType.StoredProcedure
.CommandText = "spUpdateWithdrawals"
.Parameters.Clear()
.Parameters.Add("@Withdrawal_ID", SqlDbType.Int, 4, "Withdrawal_ID")
.Parameters.Add("@WithdrawalDate", SqlDbType.DateTime, 8, "WithdrawalDate")
.Parameters.Add("@Amount", SqlDbType.Money, 8, "Amount")
.Parameters.Add("@Reason", SqlDbType.VarChar, 60, "Reason")
End With
'Set Delete Command for WithdrawalDA.
WithdrawalDA.DeleteCommand = New SqlCommand
With WithdrawalDA.DeleteCommand
.Connection = myConn
.CommandType = CommandType.StoredProcedure
.CommandText = "spDeleteWithdrawals"
.Parameters.Clear()
.Parameters.Add("@Withdrawal_ID", SqlDbType.Int, 4, "Withdrawal_ID")
End With
grdWithdrawals.TableStyles.Clear()
grdWithdrawals.DataSource = myDataSet.Tables("Withdrawals").DefaultView
myDataSet.Tables("Withdrawals").DefaultView.RowFil ter = "Client_Liability_ID IS NULL"
Now my problem is how I can delete records which are not shown in the DataGrid. Let’s say if I want to delete records where Client_Liability_ID = 1
I wrote the following procedure, but it doesn’t seem to work as it does not delete records from the Database.
Private Sub RemoveUnconsolidatedDebts(ByVal clientLiabilityID As Integer)
For Each dr As DataRow In myDataSet.Tables("Withdrawals").Select("Client_Lia bility_ID = " & clientLiabilityID)
myDataSet.Tables("Withdrawals").Rows.Remove(dr)
Next
End Sub
What am I doing wrong here?
