Update Database Record

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 website that allows members to register with a username and password.  While you may not allow the user to change their username, they will certainly need to be able to change their password.

To give this option to your members, open your MyData database and create a table called tblMembers 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 tblMembers"
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="update2.asp">
Username:  <input type="text" name="Username" size="50"><br>
<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 tblMembers WHERE Username = ' " & strUsername & " ' "
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText

IF objRS.EOF THEN
Response.Write "Sorry, that username does not exist. Please click back on your browser and enter a different username."

ELSE
%>

<form name="UpdatePassword" method="post" action="confirm.asp">
Customer:  <input type="text" name="Username" size="50" value='<%=objRS("Username")%>' <br>
Password:  <input type="text" name="Password" size="50" value='<%=objRS("Password")%>' <br>
<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 tblMembers 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 successfully updated in our database.<br><br>"

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

That’s how you update a database record, enjoy.

< Back to ASP Scripts