ADO Delete Records

From http://www.w3schools.com (Copyright Refsnes Data)

# ADO Delete Records

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>

From http://www.w3schools.com (Copyright Refsnes Data)