OK, time to learn how to delete a record from your database. For this example, let’s say that you had a table of customer names and the state where they live and that you want to delete customers who live in Wyoming because you no longer service that area.
To do this, open your trusty MyData database and create a table called tblCustomers with the following fields:
ID – autonumber
Customer – text field
State – text field
DateEnrolled – date/time field
Next, create a form page called /Delete.asp and copy the below code into your page:
<!--#INCLUDE VIRTUAL="/includes/connection.asp" --> <% DIM mySQL, objRS mySQL = "SELECT Username FROM tblCustomers" Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open mySQL, objConn %> <p>Please enter the state that you would like to delete:</p> <form name="DeleteRecord" method="Post" action="confirm.asp"> <table> <tr><td>State: </td> <td><input type="text" name="State" value="Wyoming"></td></tr> </table> <input type="submit" name="Submit" value="Submit">
Next, create a page called /Confirm.asp and copy the below code into your page:
<% DIM strState strState = Request.Form("State") IF strState<> "" THEN %> <!--#INCLUDE VIRTUAL="/includes/connection.asp" --> <% DIM mySQL, objRS mySQL = "SELECT * FROM tblCustomers WHERE State = ' " & strState& " ' " Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open mySQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText IF objRS.EOF THEN Response.Write "Sorry, you do not have any customers in that state. Please click back on your browser and enter a different state." ELSE DO WHILE NOT objRS.EOF objRS.Delete objRS.MoveNext Loop Response.Write "Your customers in: " & strState & " have been successfully deleted from your database." END IF objRS.Close Set objRS = Nothing objCONN.Close Set objCONN = Nothing %> <% ELSE Response.Write "Please click back on your browser and select a state to delete." END IF %>
When you submit the above form, it will send the value in the State field to the /Confirm.asp page. When the records are found, they will be deleted from the database.
This script is meant for deleting multiple records. If you know that you only have one record in your table or if you only want to delete the first record returned by the script, you could make this slight adjustment in your code:
Use this: objRS.MoveFirst objRS.Delete objRS.Close Set objRS = Nothing Instead of this: DO WHILE NOT objRS.EOF objRS.Delete objRS.MoveNext Loop
That’s it, now you can delete records from your database.