ASP Tutorial
How do I remove data from my database?
The syntax to remove records is rather simple, but since it's simple, it can be
done wrong. As in all scripting languages, removing or deleting data is not
recommended. Moving to other locations is best. AIT recommends that you not use
the delete function if at all possible. A suggestion is to create a 'status'
column in your database that will display things such as 'active' or 'inactive'.
This way, all your SQL queries will use statements that are looking for all
'active' items. This will ensure that you do not lose valuable data.
If you do wish to proceed with deleting your data, the syntax is below. Please
review the terms below. These terms will be used in this section.
| delete * from table where
column='something' |
This statement will remove all
records where the value for the column defined is 'something. This may
remove more than one column, so please be careful. |
| |
|
| delete * from table |
This is not a good statement to
use at all. If you leave off the 'where' clause, all records will be
deleted! Thus, please ensure you complete your SQL statement with a
conditional clause. |
To execute the following example script below, you
will need to create either a MS Access, or MS Excel database, and create a DSN.
Here is a short description of what the database should look like. In this
example, we will use MS Access.
Table name: example
| Field |
Data Type |
| ID |
AutoNumber |
| Name |
Text
|
| Email |
Text
|
| Phone |
Text
|
Input a few lines of data into the table, if you
haven't already. We'll say that 5 or 6 entries are sufficient. If you've gone
through each chapter, then you've probably already created this DSN, and
database. Again, if you haven't already done so, create a DSN named 'example'
using your Windows 2000 Control Panel [PDF]. Create the script with the name
select_delete_data.asp. This script will be a form that lists all items inside
the databaseThe code to create the script is below. Then create the script with
the name delete_data.asp
------------- Copy the following into select_data.asp -------------
<HTML>
<HEAD>
<TITLE>Selecting Data to Delete Script</TITLE>
</HEAD>
<BODY>
<FORM ACTION="delete_data.asp" METHOD="POST">
<SELECT NAME="ID">
<OPTION SELECTED>-- Select item to delete --</OPTION>
<%
set rs=Server.CreateObject("adodb.Recordset")
' Set the DSN
connect="DSN=example"
' Designate your SQL statement
sqlstmt = "SELECT * from example"
' Connect the record set with the DSN and the SQL statement
rs.open sqlstmt, connect
' Connect the record set with the DSN and the SQL statement
rs.open sqlstmt, connect
' This area is a logical error routine. If there
' is no data in the database (the rs.eof or record set end of file),
' then display that there are no records in the database.
' Else, you're going to display the script as it's written
If rs.eof then
response.write "There are no records in the database<br><br>"
response.end
Else
' This is an example of a loop. The "Do" statement
' matches up with the "Loop" at the end of this script. It is associated
' with the do as it is a conditional loop. While the file is not
' at the end, continue to do the following.
Do while not rs.eof
ID = rs("ID")
Name = rs("Name")
Email = rs("Email")
Phone = rs("Phone")
' Now that we've assigned the variables, you'll need to print each item of the
' database into a drop down menu. This way, you'll select everything in
' the database and can only delete records that are in the database.
%>
<OPTION VALUE=" <%= ID %> "> <%=Name %> </OPTION>
<%
rs.MoveNext
loop
End If
%>
</SELECT>
<INPUT TYPE="SUBMIT">
</FORM>
<%
' This is the same error routine used in the last chapter.
If err.number>0 then
response.write "VBScript Errors Occured:" & "<P>"
response.write "Error Number=" & err.number & "<P>"
response.write "Error Descr.=" & err.description & "<P>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
response.write "SQLState=" & err.sqlstate & "<P>"
End If
%>
</BODY>
</HTML>
------------- Copy the following into delete_data.asp -------------
<HTML>
<HEAD>
<TITLE>Delete Data Script</TITLE>
</HEAD>
<BODY>
<%
' This variable comes from the previous form.
ID = request.form("ID")
' Set the connection and the DSN
set connect=server.CreateObject("adodb.connection")
connect.Open("DSN=example")
' Creating delete query on the basis of recieved values
sqlstmt="delete from example where ID=" & ID
' Execute delete query
connect.Execute sqlstmt
%>
<h2>The record requested has been removed.</h2>
<%
connect.close
%>
</BODY>
</HTML>
