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