RSS
| << 14.2.0- Modifying Data | Chapter14 | 14.2.2- Updating Existing Data >> |
Adding Data Using a Recordset
This is where the AddNew and Update methods come in. Let's jump straight in by running an example that adds a record to the Movies table of the Movie2000 database.
Try It Out – Adding New Records
1. We'll use the Movies database and the strConnect connection string that we've used many times before in these ADO examples. Check that your Movie2000.mdb or Movie2000.mdf file is set up as described in Chapter 12 , and that the DataStore.asp SSI file is situated in the \inetpub\wwwroot\BegASPFiles folder.
2. In your editor, create a new file (which we'll call AddNew.asp) and add the following code:
<%
Option Explicit
Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>Adding a New Record</TITLE>
</HEAD>
<BODY>
<%
Dim objRS, intIDForNewRecord
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open "Movies", strConnect, adOpenStatic, adLockOptimistic, adCmdTable
objRS.MoveLast
intIDForNewRecord = objRS("MovieID") + 1
objRS.AddNew ' add a new record
objRS("MovieID") = intIDForNewRecord
objRS("Title") = "Psycho"
objRS.Update
objRS.Close
objRS.Open "SELECT * FROM Movies WHERE MovieID=" & intIDForNewRecord, _
strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If objRS.EOF Then
Response.Write "New record not found - something went wrong"
Else
Response.Write "You've successfully added a new record:<BR> " & _
"Movie title = '" & objRS("Title") & "'<BR>" & _
"MovieID = " & objRS("MovieID")
End If
objRS.Close ' now close and clean up
Set objRS = Nothing
%>
</BODY>
</HTML>
3. Save the AddNew.asp file to the \inetpub\wwwroot\BegASPFiles and open your browser to view the page:
|
|
How It Works
As usual we start with a couple of variable declarations, and then create a Recordset object:
Dim objRS, intIDForNewRecord
Set objRS = Server.CreateObject ("ADODB.Recordset")
Then we open the recordset, so that it contains the data from the Movies table. Notice that this is a static recordset with optimistic locking. All our other recordsets have been read-only, but that won't do if we are intending to make changes to the data store:
objRS.Open "Movies", strConnect, adOpenStatic, adLockOptimistic, adCmdTable
With the recordset open, we're going to move directly to the last record in the recordset, make a note of its MovieID value, and add one to that value. We'll use this in a moment to create the new record:
objRS.MoveLast
intIDForNewRecord = objRS("MovieID") + 1
Now we can add the new data to the recordset. The AddNew method creates a new, empty record within the recordset:
objRS.AddNew ' add a new record
This gives us a new record in the recordset, and so we can add some new data to its fields. And here's a clever bit: when the AddNew method has added the new record to the recordset, it assumes that we're going to do some work on it – so it also points the cursor at the new record. So, we're ready to add the new data straight away:
objRS("MovieID") = intIDForNewRecord
objRS("Title") = "Psycho"
For the MovieID field, we're using the value that we assigned to intIDForNewRecord that we calculated a moment ago. This is an integer value which is one more than the MovieID of the previous last record in the recordset. In this case, that should be enough to keep the MovieID fields of this table unique.
In Access, MSDE, and other database software, it's possible to configure the fields that must contain unique values so that these values are automatically generated whenever a new field is created. For simplicity, we've avoided that here. In Chapter 15 , the Classified.mdb database file uses autogenerated field values.
Now, at this stage, we've only added the data to the recordset – the data store hasn't seen this new record yet. In order to tell the data store of our changes, we must call the Update method:
objRS.Update
Now we'll prove that the new record really has entered the database. To do this, we'll Close the recordset to disconnect it from the database, then Open it again to reconnect (this time using a forward-only read-only cursor, and using a SQL command to select only the new record):
objRS.Close
objRS.Open "SELECT * FROM Movies WHERE MovieID=" & intIDForNewRecord, _
strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If objRS.EOF Then
Response.Write "New record not found - something went wrong"
Else
Response.Write "You've successfully added a new record:<BR> " & _
"Movie title = '" & objRS("Title") & "'<BR>" & _
"MovieID = " & objRS("MovieID")
End If
The newly opened recordset contains fresh data from the database – so if it contains a record with the MovieID value equal to intIDForNewRecord, we can conclude that the earlier Update method successfully added our new record to the database. The browser output will confirm that for us. Finally, we can clean up the Recordset object:
objRS.Close ' now close and clean up
Set objRS = Nothing
| << 14.2.0- Modifying Data | Chapter14 | 14.2.2- Updating Existing Data >> |
