Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]  DB: Converting a DB to a Comma-Delimited file
     [next Lesson]  DB: Access Scalability

Database -- Delete Record with SQL statement

SQL statements can be used to delete data as well.

Here is a script that will add a bunch of records with the AU_ID of 200:

   filename=/learn/test/dbaddmany.asp

<Test Script Below>

 

<TITLE>dbaddmany.asp</TITLE>
<body bgcolor="#FFFFFF">
<HTML>
<!--#include file="lib_errors.asp"-->
<%
on error resume next
myDSN = "DSN=Student;uid=student;pwd=magic"
mySQL = "INSERT INTO authors (AU_ID,author,year_born) "
mySQL = mySQL & "VALUES (200,'Charles M. Carroll',1964)"

Set Conn = Server.CreateObject("ADODB.Connection")
conn.open myDSN

for counter=1 to 200
    thistask="Task #" & counter & "<br>"
    response.write thistask
    Conn.Execute(mySQL)
    Call ErrorVBScriptReport(thistask)
    Call ErrorADOReport(mySQL,conn)
next
Conn.Close
set conn=nothing
%>
</BODY>
</HTML>

Now here is a script that will delete all the records the above script added:

   filename=/learn/test/dbdeletemany.asp

<Test Script Below>

 

<TITLE>dbdeletemany.asp</TITLE>
<body bgcolor="#FFFFFF">
<HTML>
<!--#include file="lib_errors.asp"-->
<%
on error resume next
myDSN = "DSN=Student;uid=student;pwd=magic"
mySQL = "DELETE FROM authors WHERE au_id=200"

Set Conn = Server.CreateObject("ADODB.Connection")
conn.open myDSN

Conn.Execute mySQL,howmany
response.write "The statement " & mySQL & "<b> deleted " & howmany & " records</b><br>"
Call ErrorVBScriptReport("Deleting...")
Call ErrorADOReport(mySQL,conn)

Conn.Close
set conn=nothing
%>
</BODY>
</HTML>

The error trapping library looks like this:

   filename=/learn/test/lib_errors.asp

<Test Script Below>


<%
SUB ErrorVBScriptReport(parm_msg)
    If  err.number=0 then
        exit sub
    end if
    pad="&nbsp;&nbsp;&nbsp;&nbsp;"
    response.write "<b>VBScript Errors Occured!<br>"
    response.write parm_msg & "</b><br>"
    response.write pad & "Error Number= #<b>" & err.number & "</b><br>"
    response.write pad & "Error Desc.= <b>" & err.description & "</b><br>"
    response.write pad & "Help Context= <b>" & err.HelpContext & "</b><br>"
    response.write pad & "Help File Path=<b>" & err.helpfile & "</b><br>"
    response.write pad & "Error Source= <b>" & err.source & "</b><br><hr>"
END SUB

SUB ErrorADOReport(parm_msg,parm_conn)
    HowManyErrs=parm_conn.errors.count
    IF  HowManyErrs=0 then
        exit sub
    END IF
    pad="&nbsp;&nbsp;&nbsp;&nbsp;"
    response.write "<b>ADO Reports these Database Error(s) executing:<br>"
        response.write SQLstmt & "</b><br>"
    for counter= 0 to HowManyErrs-1
        errornum=parm_conn.errors(counter).number
        errordesc=parm_conn.errors(counter).description
        response.write pad & "Error#=<b>" & errornum & "</b><br>"
        response.write pad & "Error description=<b>"
        response.write errordesc & "</b><p>"
    next
END SUB
%>

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.