So far in this series, we've been learning to use ADO from within ASP. We have gone over how to make connections, and learned how to extract data using recordsets. In this installment, we will go over the various methods of adding information to a database using ADO.
Written by Rich Smith Rating: / 40 September 30, 2003
There are two common methods of adding data to a database using ADO. The first method is by using additional properties of the recordset object. This method is easy to understand, but does have it’s drawbacks. The other more confusing method is to utilize the connection object directly. We will explore each method, and discuss the when it is best to use each of them.
We’ve learned so far how to use recordsets to retrieve data, but they work just as well when adding data to a database. In order to use this method though, your database provider needs to support updateable recordsets. The key to inserting a record is the “addnew” method of the recordset object. The simplest way to use this method is without any arguments. This allows you to create an empty record, modify its contents, and then update it to the database. Here’s an example:
<% ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
set rs = Server.CreateObject("ADODB.recordset") rs.open “Customers”, conn
When using the addnew method to create new records in a recordset object, there are two optional parameters that can be used. These are the fieldlist and valuelist parameters, respectively. 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
conn.close set conn = nothing %> The above examples all use the addnew method to create records without the need to implement a bunch of code just to add records to a database.
At the beginning of the article, I did mention the ability to use the connection object to add records. In order to use this method, you will need to know a little SQL. In essence, instead of adding new records to a recordset object we will formulate the appropriate SQL statement to do the work and hand it directly to the connection object for processing. This is done using the “execute” method of the connection object. For example:
<%
ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
mySql = “Insert into Customers (FirstName, LastName) values (“ mySql = mySql & “’Rich’, ‘Smith’)”
conn.execute mySql
conn.close set conn = nothing %> As you can see, this method is a little more complicated because it requires some knowledge of SQL.
The aforementioned methods of inserting data into a database will all achieve the same results, but which one do you use? Well, that really depends on your knowledge of databases and your application requirements. For example, using the recordset properties to add records to the database is good to use if the database resides locally on the same machine, or if you are not very proficient in SQL. While using the connection object properties is more confusing, it is extremely lightweight and very efficient.
We’ve come quite a way thus far, in using ADO to manipulate the data in a database. We have learned to create connections, as well as how to add and retrieve data. When creating ASP applications, this should cover 85% or more of your database needs. In the next and final installment in this series, we will learn how to maintain the data in our database. This includes how to update or change the information that exists in the database, as well as completely remove records that have previously been added. About the author: Rich Smith owns and operates Jamsoft Development, a programming firm who specializes in custom systems for small businesses.
blog comments powered by Disqus
ASP Database Fundamentals (Part 4)
