SHARE THIS PAGE

ADO Tutorial

ADO HOME ADO Intro ADO Connect ADO Recordset ADO Display ADO Query ADO Sort ADO Add ADO Update ADO Delete ADO Demo ADO Speed Up

ADO Objects

ADO Command ADO Connection ADO Error ADO Field ADO Parameter ADO Property ADO Record ADO Recordset ADO Stream ADO DataTypes
ADO Summary

ADO Examples

ADO Exam

ADO Certificate

ADO Delete Records

« Previous

Next Chapter »

We may use the SQL DELETE command to delete a record in a table in a database.

Delete a Record in a Table

We want to delete a record in the Customers table in the Northwind database. We first create a table that lists all records in the Customers table:

<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM customers",conn
%>

<h2>List Database</h2>
<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
  response.write("<th>" & ucase(x.name) & "</th>")
next
%>
</tr>
<% do until rs.EOF %>
<tr>
<form method="post" action="demo_delete.asp">
<%
for each x in rs.Fields
  if x.name="customerID" then%>
    <td>
    <input type="submit" name="customerID" value="<%=x.value%>">
    </td>
  <%else%>
    <td><%Response.Write(x.value)%></td>
  <%end if
next
%>
</form>
<%rs.MoveNext%>
</tr>
<%
loop
conn.close
%>
</table>

</body>
</html>

If the user clicks on the button in the "customerID" column he or she will be taken to a new file called "demo_delete.asp". The "demo_delete.asp" file contains the source code on how to create input fields based on the fields from one record in the database table. It also contains a "Delete record" button that will delete the current record:

<html>
<body>

<h2>Delete Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"

cid=Request.Form("customerID")

if Request.form("companyname")="" then
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.open "SELECT * FROM customers WHERE customerID='" & cid & "'",conn
  %>
  <form method="post" action="demo_delete.asp">
  <table>
  <%for each x in rs.Fields%>
  <tr>
  <td><%=x.name%></td>
  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
  <%next%>
  </tr>
  </table>
  <br><br>
  <input type="submit" value="Delete record">
  </form>
<%
else
  sql="DELETE FROM customers"
  sql=sql & " WHERE customerID='" & cid & "'"
  on error resume next
  conn.Execute sql
  if err<>0 then
    response.write("No update permissions!")
  else
    response.write("Record " & cid & " was deleted!")
  end if
end if
conn.close
%>

</body>
</html>

« Previous

Next Chapter »