DELETE RECORD

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 that 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 these 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:



<%
DIM mySQL, objRS
mySQL = "SELECT Username FROM tblCustomers"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn
%>

Please enter the state that you would like to delete:

State:

Next, create a page called confirm.asp and copy the below code into your page:

<%
DIM strState
strState = Request.Form("State")
IF strState<> "" THEN
%>



<%
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 succesffully 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 record is found, it will be deleted. The above 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.

http://www.aspwebpro.com/aspscripts/records/delete.asp