ASP Tutorial
How do I update records?
Updating records in a database is most easily done using three ASP pages. There
are other ways to update records, however this is a simple way to utilize ASP to
manipulate existing data without removing it and re-adding it. Also, there are
other "paid for" ASP scripts available that will edit in an easier fashion,
however we think you'll find this way just fine.
Please review the terms below. These terms will be used in this section.
| UPDATE table SET column='value'
column2=value... where column='identifier' |
This SQL statement will update
the current record. |
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 Control Panel. In this example, there are 3 main
scripts. One to select the data you wish to update; another to display the form
and all the values inside the database; and another to update the database.
Create the script with the name select_update_data.asp. This script will be a
form that lists all items inside the database.
Then create the script with the name modify_data1.asp. This script will display
a form with all the values of the database and the form fields which will allow
you to update the data.
Finally, create a modify_data1.asp script. This script will update the data in
the database for good.
------------- Copy the following into select_update_data.asp ------------
<HTML>
<HEAD>
<TITLE>Selecting Data to Modify Script</TITLE>
</HEAD>
<BODY>
<FORM ACTION="modify_data1.asp" METHOD="POST">
<SELECT NAME="ID">
<OPTION SELECTED>-- Select item to modify --</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
' 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 modify_data1.asp -------------
<HTML>
<HEAD>
<TITLE>Modify Data Form</TITLE>
</HEAD>
<BODY>
<FORM ACTION="modify_data2.asp" METHOD=POST>
<%
' This variable comes from the previous form.
ID = request.form("ID")
set rs=Server.CreateObject("adodb.Recordset")
' Set your DSN
connect="DSN=example"
' Set your SQL statement to select the ID from the
' previous form.
sqlstmt = "SELECT * from example WHERE ID =" & ID & " "
' Execute the statement
rs.open sqlstmt, connect
If rs.eof then
response.write "There are no records for this ID."
response.write "<br>Please check back later. </center>"
response.end
Else
' Identify all variables needed for script
Dim ID, Name, Email, Phone
' Pull out all fields to be displayed so they can be changed.
Name = rs("Name")
Email = rs("Email")
Phone = rs("Phone")
End If
%>
<INPUT TYPE=HIDDEN NAME=ID VALUE="<%=ID%>">
<TABLE WIDTH=600 BORDER=1>
<TR><TD>Name:</TD>
<TD><INPUT NAME="Name" TYPE=text VALUE="<%=Name %>"></TD>
</TR>
<TR>
<TD>Email:</TD>
<TD><INPUT NAME="Email" TYPE=text VALUE="<%=Email %>"></TD>
</TR>
<TR>
<TD>Phone:</TD>
<TD><INPUT NAME="Phone" TYPE=text VALUE="<%=Phone %>"></TD>
</TR>
<TR>
<TD COLSPAN=2><INPUT TYPE="Submit"></TD>
</TR>
<%
rs.close
%>
</TABLE>
</FORM>
</BODY>
</HTML>
------------- Copy the following into modify_data2.asp -------------
<HTML>
<HEAD>
<TITLE>Modify Data Script</TITLE>
</HEAD>
<BODY>
<%
' This variable comes from the previous form.
ID = request.form("ID")
Name = request.form("Name")
Email = request.form("Email")
Phone = request.form("Phone")
' Set the connection and the DSN
set conn=server.createobject("adodb.connection")
conn.open "example"
' Push your update SQL statement into the variable
SQLstmt = "UPDATE [example]"
SQLstmt = SQLstmt & " SET Name='" & Name &"',"
SQLstmt = SQLstmt & " Email='" & Email & "',"
SQLstmt = SQLstmt & " Phone='" & Phone & "'"
SQLstmt = SQLstmt & " where ID=" & ID
' Execute the SQL statement
Set RS = conn.execute(SQLstmt)
' Basic error routine
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
If conn.errors.count> 0 then
response.write "Database Errors Occured" & "<P>"
response.write SQLstmt & "<P>"
for counter= 0 to conn.errors.count
response.write "Error #" & conn.errors(counter).number & "<P>"
response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
Next
Else
' no errors, so we'll tell the user that all went well
%>
The entry for <b><%=Name %></b> has been updated.
<%
End If
%>
<%
' now we will close the connection to the database
conn.close
%>
</BODY>
</HTML>
