Codefixer - Deleting records in a database table using ASP

Below is a table called 'Members_tbl' with 3 rows of data which belongs to a database called 'Members'.

Using the SQL WHERE Clause we are going to delete the rows where the City column is equal to Belfast. This will delete two records in our example.

<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Deleting records in a database table</title>
</head>
<body>
<%
'declare your variables
Dim connection, sSQL, sConnString

'declare SQL statement that will query the database
sSQL="DELETE * FROM Members_tbl WHERE CITY='BELFAST'"

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.connection")

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Members.mdb")

'Open the connection to the database
Connection.Open sConnString

'Execute the SQL statement
Connection.Execute sSQL

'Now close the connection object
connection.Close
Set connection = Nothing
%>
</body>
</html>

We could add more criteria to the SQL statement. If we wanted to only delete the records where City was equal to Belfast and the FirstName was equal to Michael we could use the SQL statement below and assign to our variable sSQL.

sSQL="DELETE * FROM Members_tbl WHERE CITY='BELFAST' AND FIRSTNAME='MICHAEL' "

Just a note that SQL uses single quotes around text values . Numeric values should not be enclosed in quotes. If we wanted to delete the record where Id equals 3 we would write it as below without the enclosing ' and then assign to our variable sSQL.

sSQL="DELETE * FROM Members_tbl WHERE Id=3"

As a final note we haven't used the the recordset object as we are not returning a recordset, we are simply deleting records.