Make your own free website on Tripod.com

DELETING RECORDS


With ADO, the RS.Delete is still functional. Therefore, if you wish, you may delete records with something similar to the following:

PLEASE NOTE:It is necessary to include adAffectCurrent when you issue the Delete to eliminate the possibility of deleting records other than the one which you are on and intending to delete.


Private Sub cmdDelete_Click()

rs.Delete adAffectCurrent

rs.MoveNext

If rs.EOF Then

rs.MoveLast

Else

FillFields

End If

End Sub


However, for the purposes of this application, rather than using the RS.Delete, moving off of the record and subsequently refilling the fields(Above), we will use Oracle Stored Procedures (See Deleting Records under SQL PLUS: Creating Oracle Stored Procedures)
to centralize processing at the Server level. This will keep network traffic to a minimum.


RUNNING THE ORACLE STORED PROCEDURE IN THE VB APPLICATION

In the Click Event of the Delete button, the code below first Declares my Variable to identify the record to be deleted and sets it to the appropriate Text Box. Once the Active Connection has been confirmed, it then sets the Command Text equal to the Stored Procedure. Subsequently, it then Executes the Store Procedure, indicating that only the Current Record will be affected and provides the Variable containing the appropriate data from the Text Box. Lastly, a Requery and a FillFields is performed, placing the user at the First Record, enabling them to see the updated Recordset immediately.


Private Sub cmdDelete_Click()

Dim MyCustomerID As Integer

MyCustomerID = CInt(txtCustomerID.Text)

mdkcmd.ActiveConnection = conn

mdkcmd.CommandText = "DeleteCustomer"

mdkcmd.Execute adAffectCurrent, MyCustomerID

rs.Requery

If rs.RecordCount = 0 Then

MsgBox "There Are No Records In This Table"

Else

rs.MoveFirst

FillFields

End If

End Sub


RETURN TO HOME PAGE