Update Record ASP Script

Posted by admin

May 7, 2008

Once you have a record in your database, chances are you may need to update it at some point. When would you want to do this? Let’s say you have a membership based website that is secured with a username and password combination. While you probably would not allow the user to change their username, they certainly can change their password.

To give this option to your members, open your MyData database and create a table called tblUsers with these fields:
ID - autonumber
Usernames - text field
Passwords - text field
DateLastUpdated - date/time field

Next, create a form page called update.asp and copy the below code into your page:

<!–#INCLUDE VIRTUAL=”/includes/connection.asp” –>

<%
DIM mySQL, objRS
mySQL = “SELECT Username FROM tblUsers”
Set objRS = Server.CreateObject(”ADODB.Recordset”)
objRS.Open mySQL, objConn
%>

<p>To change your password, enter your Username below:</p>
<form name=”UpdatePassword” method=”Post” action=”confirm.asp”>
<table>
<tr><td>Username: </td>
<td><input type=”text” name=”Username” size=”50″></</td></tr>
</table>

<input type=”submit” name=”Submit” value=”Submit”>

As always, there are a lot of ways to collect the username from the user. The main purpose here is to show how to update the record in the database though so on to the good stuff.

Next create a page called update2.asp and copy the below code:

<%
DIM strUsername
strUsername = Request.Form(”Username”)
IF strUsername <> “” THEN
%>

<!–#INCLUDE VIRTUAL=”/includes/connection.asp” –>

<%
DIM mySQL, objRS
mySQL = “SELECT * FROM tblUsers WHERE Username = ‘ ” & strUsername & ” ‘ ”
Set objRS = Server.CreateObject(”ADODB.Recordset”)
objRS.Open mySQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText

IF objRS.EOF THEN
Response.Write “<div align=’center’>Sorry, that username does not exist. Please click back on your browser and enter a different username.</div>”

ELSE
%>

<form name=”UpdatePassword” method=”post” action=”confirm.asp”>
<table>
<tr><td>Customer:</td><td>
<input type=”text” name=”Username” size=”50″ value=’<%=objRS(”Username”)%>’>
</td></tr>
<tr><td>Password:</td><td>
<input type=”text” name=”Password” size=”50″ value=’<%=objRS(”Password”)%>’>
</td></tr>
</table>
<input type=”submit” name=”Submit” value=”Submit”>
</form>

<%
END IF

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

<%
ELSE
Response.Write “Please click back on your browser and enter your username.”
END IF
%>

Last, create a page called confirm.asp and copy the below code into your page:

<!–#INCLUDE VIRTUAL=”/includes/connection.asp” –>

<%
DIM mySQL, objRS
mySQL = “SELECT * FROM tblUsers WHERE fUsername = ‘” & strUsername & “‘”
Set objRS = Server.CreateObject(”ADODB.Recordset”)
objRS.Open mySQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText

objRS.MoveFirst
objRS(”Password”) = Request.Form(”Password”)
objRS(”DateLastUpdated”) = Date()
objRS.Update

objRS.Close
Set objRS = Nothing

Response.Write “<div align=’center’>” & strUsername & “,<br>”
Response.Write ” Your password has been succesffully updated in our database.<br><br>”

objRS.Close
Set objRS = Nothing
objCONN.Close
Set objCONN = Nothing
%>