Updating a Record in Your Database
Updating
a record in your database is not very difficult. So let's just dive
right in. First thing we need to do is figure out which record we want
to update. We will be using the same database that we've been using.
First we create a page that will list our users in our users table.
Save this page as users.asp.
<%
@Language=VBScript %>
<% Option Explicit %>
<!--#include file="adovbs.inc"-->
<!--#include file="connection.asp"-->
<%
Dim RS, objConn
Set RS = objConn.execute("SELECT userID, username FROM users")
%>
<html>
<head><title>Users</title></head>
<body>
Click the user you want to edit<br>
<%
while not rs.eof
response.write "<a href='edit.asp?ID=" & RS("userID")
& " '>" & RS("username") & "</a><br>"
RS.movenext
wend
RS.Close
Set RS = nothing
objConn.Close
Set objConn = nothing
%>
</body></html>
Now
we create our edit.asp page. This page will be a form that pulls the
current info about the person from the database.
<%
@Language=VBScript %>
<% Option Explicit %>
<!--#include file="adovbs.inc"-->
<!--#include file="connection.asp"-->
<%
Dim RS, objConn, ID
'Get the ID
from the querystring
ID = trim(request.querystring("ID"))
Set RS = objConn.execute("SELECT * FROM users WHERE userID = " & ID)
%>
<html>
<head><title>Edit a User</title></head>
<body>
<form action="updateuser.asp" method="post">
In the input
boxes, we fill them with the values of our recordset by using value="<%=RS("fieldname")%>"
Username: <input type="text" name="username"
value="<%=RS("username")%>"><br>
Password: <input type="text" name="password"
value="<%=RS("password")%>"><br>
We include
a hidden field that contains our ID to pass to our updateuser.asp page.
<input type="hidden" name="ID" value="<%=ID%>">
<%
rs.close
set rs = nothing
objConn.close
set objConn = nothing
%>
<input type="submit" name="submit" value="submit">
</form></body></html>
Finally,
we create our updateuser.asp page. First we need to request the ID from
the previous page, so we know which record needs updating.
<%
@Language=VBScript%>
<% Option Explicit %>
< !--#include file="adovbs.inc"-->
< !--#include file="connection.asp"-->
<%
Dim ID, objConn, RS, username, password
username = trim(request.form("username"))
password = trim(request.form("password"))
ID = trim(request.form("ID"))
Set RS = objConn.execute("UPDATE users SET username = ' " & username
& " ' , password = ' " & password & " ' WHERE userID = " & ID & " ")
<html>
<head><title>Edit User</title></head>
<body>
You have successfully updated a user. <a href="users.asp">Click
here</a> to continue.
</body</html>
Notice
that our UPDATE statement is a bit different than our INSERT INTO statement.
First we specify what table we are updating and then tell it to SET
the fields equal to the values of our variables. Each field=variable
is separated by a comma. Then it ends with the WHERE portion of the
string telling it which record needs updating. And there you have it.
Now you can edit and update your own records too. If you want to download
this lesson and the files for it, click here.
See ya next time!
~Geoff Swartz | 
