ASP Database Update
In this tutorial we'll learn how to update database records with ASP. This isn't too much different from adding database records.
Once again, we'll start by including adovbs.inc and use the Users DSN to open a connection to our database:
<!--#include virtual="/adovbs.inc"-->
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=Users"
%>
Let's assume that an User ID has been passed from a through the Querystring from another page - we'll use this to select the correct record from the database:
<%
iUserID = Request.QueryString("UserID")
sSQL = "select * from Users where ID=" & iUserID
%>
Now we open our Recordset using our SQL query (sSQL) and use the LockType "adLockPessimistic" to lock the table when calling the update method:
<%
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = oConn
rs.LockType = adLockOptimistic
rs.Source = sSQL
rs.Open
%>
Updating a database is almost identical to adding to it - the only difference is we want to use an existing record, so there's no need to use rs.AddNew.
<%
rs("Name")
rs("Age")
rs.Update
%>
All that's left to do now is clean up our objects. We might also want to redirect to another page after updating the database. When redirecting to another page, always make sure this is done AFTER destroying the Recordset and Connection.
<% rs.Close Set rs = Nothing oConn.Close Set oConn = Nothing response.redirect "user.asp?UserID=" & iUserID %>
Now we know how to update and add records, let's finish by learning to delete from the database.
