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.