|
|
ASP Scripts
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:
<!--#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 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.
< Back to
ASP Scripts
|
