In this series of articles, we have been introduced to using ADO to access a database from with ASP. We have learned how to create an ADO connection, and use that connection to insert data into a database as well as retrieve information... [T]he final two aspects of database interaction that have yet to be addressed are two of the most important. Not many database applications could be complete if they allowed you to merely insert and view records. Without some sort of mechanism to maintain the data within the database, the scope of the application would be quite limited.
Written by Rich Smith Rating: / 84 October 27, 2003
We are now three quarters of the way around the track and are coming to the home stretch. In this series of articles, we have been introduced to using ADO to access a database from with ASP. We have learned how to create an ADO connection, and use that connection to insert data into a database as well as retrieve information.
What is it we are missing? Well, the final two aspects of database interaction that have yet to be addressed are two of the most important. Not many database applications could be complete if they allowed you to merely insert and view records. Without some sort of mechanism to maintain the data within the database, the scope of the application would be quite limited.
If your database application did not have the ability to update or modify existing database entries, then it would be severely limiting in its potential. For example, imagine having a customer database in which you did not have the ability to change the contact information for a customer. You would not be able to update the customer’s mailing address, phone number, or email address. How about an inventory management system that had an inability to update the price or quantity of an item in stock?
For today’s mainstream database applications, database maintenance is a necessity. To that end, there are two main methods of updating information utilizing ADO. These methods very closely reflect the methods we discussed when learning to insert items into the database. They involve using the recordset object, as well as the connection object directly.
As with inserting records into a recordset, updating them has a particular method to facilitate this. The “update” method is used to save modifications to a record within a recordset to the recordset object, as well as to the connection object itself.
Here is an example in the simplest terms:
<% ConnectionString = "DSN=MyDB”
Dim CurrSeq CurrSeq = 0
set conn = server.createobject("adodb.connection") conn.open ConnectionString
set rs = Server.CreateObject("ADODB.recordset") rs.open “Customers”, conn
‘ Go through all records updating record sequence do until rs.EOF CurrSeq = CurrSeq + 1 rs(“CustomerSequence”) = CurrSeq rs.Update rs.MoveNext loop
rs.close set rs = nothing
conn.close set conn = nothing %>
As with the addnew method, there are additional ways to utilize the update method.
Similar to the addnew method, there are two optional parameters that can be used. These are the fieldlist and the valuelist parameters. These parameters work in the same manner as the ones used by the addnew method.
The fieldlist parameter can be a single field name, or an array of field names, or the numeric (ordinal) position of the fields in the new record. For both the single name and array of names, each name must be enclosed within a pair of double quotes.
The valuelist parameter is a single value or an array of values for the fields that you want to populate in the new record. If the fieldlist parameter is an array, then valuelist must also be an array. Further, the valuelist must have the exact same number of members and be in the same order as the fieldlist.
Consider these examples:
<%
set rs = Server.CreateObject("ADODB.recordset") rs.open “Customers”, conn
There are times when, after making changes to the contents of a record, you may want to cancel or undo your changes. If you make changes to a record, and then move to another record in the recordset, the update method is implicitly called automatically.
Luckily, there is yet another method of the recordset object called “cancelupdate”. This method will cancel any changes you have made to the current record in the recordset. This only works, however, if you have not used the update method, or moved to another record in the recordset.
For example:
<% ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
set rs = Server.CreateObject("ADODB.recordset") rs.open “Customers”, conn
rs(“FirstName”) = “Rich”
if UpdateRecords = “Y” then rs.update else rs.cancelupdate end if
As illustrated when we discussed inserting records into the database, the execute method of the connection object can be used to execute a correctly constructed SQL statement. By using this method, you can quickly and easily update database records without the need or overhead of creating a recordset object.
For example:
<%
ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
mySql = “Update Customers set Status = ‘Active’, “ & _ “Profile = ‘Visible’ where CustomerNo = 10”
conn.execute mySql
conn.close set conn = nothing %>
Again, this method can be considered a little more daunting than using the recordset object, because it requires at minimum a fundamental knowledge of SQL. It is, however, one of the most efficient and powerful ways to manipulate your data.
Finally, we are down to the last topic. Deleting or removing data from your database is the last fundamental function we are missing. There are two basic ways to accomplish this using ADO (sound like a pattern is forming here?), just like adding and modifying records.
Using the recordset object, there is a “delete” method to delete the current record. The delete method has an optional parameter, but for the sake of simplicity we are not going to explore it here. When using the delete method keep in mind that the current record is deleted and is still the current record in the recordset. If you attempt to retrieve values from the record, an error will occur. This deleted record will hold its place in the recordset until you move to another record.
An example:
<% ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
set rs = Server.CreateObject("ADODB.recordset") rs.open “Customers”, conn
‘ Go through all records deleting “unused” customers do until rs.EOF if rs(“Status”) = “InActive” then rs.delete end if rs.MoveNext loop
There are many additional properties and methods associated with the connection and recordset objects that can be utilized to gain additional control over your database manipulations. I would suggest additional reading about the ADO objects if you believe you could benefit from these additional features.
I hope in this series of articles I have given you the knowledge you need to begin creating database applications using ADO and ASP. By using the right combinations of database management code, there is virtually no limit to what you can achieve in your future database applications.
blog comments powered by Disqus
/ 84 
ASP Database Fundamentals (Part 3)
