ADO Deleting Records in a Database

In the previous tutorials in this series, we have covered how to select, insert, and update records in a database by leveraging ADO in our ASP pages. In this tutorial, we will cover how to delete records in our database. Deleting records is a common process in the management of organization data. We will use the SQL DELETE statement to delete records from our database.

SQL DELETE Statement

Structured Query Language also known as 'SQL' is the standard language used with most modern database applications. SQL provides us with a mechanism to select, insert, update and modify records in a database. In this summary, we are going to focus on the SQL DELETE statement. For more in-depth information about SQL, please visit the SQL section of this site. There are dozens of tutorials to help you better understand the SQL language.

Syntax

DELETE FROM tableName WHERE fieldName='value'

Examples

In this tutorial, we will take a look at two ASP pages. The first example is an ASP page that a user visits to display the records from a database. A user can click on the button for the employee record that they wish to delete. Here is the HTML code for the first page.

<!DOCTYPE html>
<html>
<head>
<title>Employees</title>
<style type="text/css">
table {border:1px solid #ABABAB;border-collapse:collapse;color:#4F4F4F;}
th {border:1px solid #ABABAB;background-color:#5F5F5F;color:#FFFFFF;padding:5px;}
tr {border:1px solid #ABABAB;}
td {border:1px solid #ABABAB;padding:5px;}
.btn {width:25px;border:1px solid gray;}
</style>
</head>
<body>
<%
Dim oConn, oRs, ds
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
Set oConn=Server.CreateObject("ADODB.Connection")
OConn.Open ds
Set oRs=Server.CreateObject("ADODB.Recordset")
oRs.open "SELECT * FROM employees",oConn
%>
<h1>Employees</h1>
<table>
<tr>
<%
for each x in oRs.Fields
        Response.Write("<th>" & UCase(x.name) & "</th>")
next
%>
</tr>
<% do until oRs.EOF %>
<tr>
<form method="post" action="employeeDelete.asp">
<%
for each x in oRs.Fields
  if lcase(x.name)="id" then%>
    <td>
    <input type="submit" name="id" class="btn" value="<%=x.value%>">
    </td>
  <%else%>
    <td><%Response.Write(x.value)%></td>
  <%end if
next
%>
</form>
<%oRs.MoveNext%>
</tr>
<%
loop
oConn.close
%>
</table>
</body>
</html>

When the user clicks on the button in the "ID" field, the user will be taken to a page called "employeeDelete.asp". The following example includes the ASP/ADO code that is needed to present the user with the required input elements so that the information about the specific record can be deleted in the database record.

<!DOCTYPE html>
<html>
<head>
<title>Update</title>
<style type="text/css">
table {border:1px solid #ABABAB;border-collapse:collapse;color:#4F4F4F;}
tr {border:1px solid #ABABAB;}
td {border:1px solid #ABABAB;padding:5px;}
.btn {border:1px solid gray;}
</style>
</head>
<body>
<%
Dim oConn, oRs, empID, sql
ds = "Driver={MySQL ODBC 3.51 Driver};SERVER=db-hostname;DATABASE=db-name;UID=userID;PWD=password"
Set oConn=Server.CreateObject("ADODB.Connection")
OConn.Open ds
empID = Request.Form("id")

If Request.Form("empName")="" then
  Set oRs=Server.CreateObject("ADODB.Recordset")
  oRs.open "SELECT * FROM employees WHERE id ='" & empID & "'",oConn
  %>
  <form method="post" action="employeeDelete.asp">
  <table>
  <%for each x in oRs.Fields%>
  <tr>
  <td><%=x.name%></td>
  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
  <%next%>
  </tr>
  </table>
  <br /><br />
  <input type="submit" class="btn" value="Update record">
  </form>
<%
Else
  sql="DELETE FROM employees WHERE id = '" & empID & "'"
  on error resume next
  oConn.Execute sql
  If err<>0 then
        Response.Write("Error updating Record!")
  Else
        Response.Write("Record " & empID & " was updated!")
  End If
End if
oConn.close
%>
</body>
</html>

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources