Lesson 21: Delete data from database
In the two previous lessons, you have learned to retrieve and insert data into a database. In this lesson, we'll look at how to delete records in the database, which is considerably easier than inserting data.
Delete data using SQL
The syntax for an SQL statement that deletes records is:
Example 1: Delete a record
When deleting a record, you can use the unique AutoNumber field in the database. In our database, it is the column named Id. Using this unique identifier ensures that you only delete one record. In the next example, we delete the record where Id has the value 24:
<html> <head> <title>Delete data in the database</title> </head> <body> <% ' Database connection - remember to specify the path to your database Set Conn = Server.CreateObject("ADODB.Connection") DSN = "DRIVER={Microsoft Access Driver (*. mdb)}; " DSN = DSN & "DBQ=" & Server.MapPath("/cgi-bin/database.mdb") Conn.Open DSN ' The SQL statement that deletes the record strSQL = "DELETE FROM people WHERE Id = 24" Conn.Execute(strSQL) ' Close the database connection Conn.Close Set Conn = Nothing %> <h1>Record is deleted!</h1> </body> </html>
Remember that there is no "Recycle Bin" when working with databases and ASP. Once you have deleted a record, it is gone and cannot be restored.