ASP Database Add
In the previous tutorial we learnt how to connect to an Access database. Now we'll go one step further and add a record to our database using ASP.
For this example we include adovbs.inc so we can use the adLockOptimistic constant.
We start by opening a connection to the database called oConn, using the DSN "Users":
<!--#include virtual="/adovbs.inc"-->
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=Users"
%>
Now we need an SQL query to open our recordset with. Using an SQL Select query "where 0=1", we deliberately create an empty recordset, since we aren't interested in any existing records:
<% sSQL = "select * from Users where 0=1" %>
The next step is to create our Recordset (rs). We tell it to use the connection we opened (oConn) and our SQL query (sSQL). The LockType property "adLockOptimistic" locks the table when executing the update statement. If you don't use adovbs.inc, adLockOptimistic can be replaced with the number 3.
<%
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = oConn
rs.LockType = adLockOptimistic ' alternatively 3
rs.Source = sSQL
rs.Open
%>
Now our recordset is open, we use the AddNew method to indicate that we want to add to the database. We then enter the information to pass into each field in the table. Finally, we use the Update method to add the record.
<%
rs.AddNew
rs("Name") = Request.Form("Name")
rs("Age") = Request.Form("Age")
rs.Update
%>
Now we're finished, free up the server resources by closing and destroying our objects:
<% rs.Close Set rs = Nothing oConn.Close Set oConn = Nothing %>
And that's how to add database records. In the next tutorial, we learn how to update database records.
