Simple ASP database techniques

What’s ADO?ADO (ActiveX Data Objects) is a collection of objects created by Microsoft that interact with databases. There’s the Connection Object, which connects us to our databases and the Recordset Object which allows us to retrieve data from our databases.

Creating a Database

Before we can do anything with a database, we must first create one. This sample uses Microsoft Access 2002.

1. Open Microsoft Access

2. On the right side of the screen, click on Blank Database

3. Save your database to your wroot folder of your personal web server.

4. You will be able to select from 3 different options: Create table in design view, create table by using wizard and create table by entering data. Select create table in design view.

5. Now once you have opened our newly created table, you will have the options to add data to it, so lets!

6. In Field Name, enter Name and in Data Type, select Memo. Memo allows us to enter text along with the text option, however the memo allows us to add a large number of characters unlike the text option.

7. Now close the table. Once you do this, it will ask you if you want to save changes to our table? Click Yes. You then will be prompted to enter a name for our new table with the default name being table1. Click OK to save our new table as table1. After this, you will be prompted one more time to see if you would like a primary key for our database. A primary key numbers each row of fields which can be very useful. The numbers will never be the same which strictly identifies each row. In this case, we don’t need one, so click No. We now have our very own table.

8. We can now close our database by exiting Microsoft Access.

The Connection Object

One of the most common task you will need to accomplish when working with databases is retrieving data from your database. In order to do this, we must first make a connection using the Connection Object and the following methods and properties: Mode, Provider, ConnectionString and Open.

<!–#include virtual=”adovbs.inc” –>

<%

Dim DB, RS
Set DB = Server.CreateObject(“adoDB.connection”)
DB.Mode = adModeReadWrite
DB.Provider = “MICROSOFT.JET.OLEDB.4.0”
DB.ConnectionString = Server.MapPath(“db1.mdb”)
DB.Open

%>

When using any of the examples below, remember to add the code above first!

Now, the first line of code uses a include file named adovbs.inc. What’s this? The adovbs.inc is a large file of constants, which are variables except their values can’t be changed. This is used to make our jobs as an ASP programmer easier. For instance, in the adovbs.inc file, the constant adOpenKeyset = 1. So when we use adOpenKeyset in our cursor parameter, we are actually using 1 in our cursor parameter. Why is this? Because that’s the real value of our cursor parameter. Microsoft just made it easier for us, so instead of having to remember numbers, we just need to remember those constants. You will see this in action in our next example.

Don’t have the adovbs.inc file, download it here! Remember to save this file in the root directory of your personal web server.

The 3rd line of code creates a instance of the Connection Object and names it DB. So now we have a variable named DB which is a copy of the Connection Object.

The 4th line of code uses the property mode of the Connection Object and sets it to read/write which not only allows us to read from the database, but the change or add to the database.

The 5th line of code uses the connectionstring property of the Connection Object which locates our database.

The final line of code uses the Open() method of the Connection Object to open the database.

So now we have access to our database but what good is that if we can’t read or write data to it. This is when the RecordSet Object comes into play.

Adding Data Using the Recordset Object

The Recordset Object allows us to read/write/update/delete data from our database. Since we have any empty database, let’s add data to our table named table1 using the following methods and properties: Open, AddNew, Update and Close.

<%
Set RS = Server.CreateObject(“adoDB.recordset”)
RS.Open “table1”, DB, adOpenKeySet,adLockOptimistic,adCmdTable
RS.AddNew
RS(“Name”) = “Andrew”
RS.UpDate
RS.AddNew
RS(“name”) = “Jennifer”
RS.Update
RS.AddNew
RS(“name”) = “Kelly”
RS.Update

%>

The code above takes the names Andrew, Jennifer and Kelly and adds them to our database.

Line 2, like the previous example creates a instance of the Recordset Object and names it RS. So now we have a variable named RS which is a copy of the Recordset Object.

Line 3 opens our database using the following Recordset Object parameters: DB, adOpenStatic, adLockOptimistic, adCmdTable.

DB is the ActiveConnection parameter. We need a connection to our database so we use our Connection Object.

AdOpenStatic constant is the CurosorLocation parameter. It describes what cursor type we would like to use. A cursor is used to navigate through records. Forward, backwards, etc. Also, some cursors are non-updateable so we must use a cursor that supports updating a database. The adOpenKeySet allows this.

AdLockOptimistic constant is the LockType parameter which allows us to add or update data to our database. With the AdLockOptimistic constant in the LockType parameter, the database only locks when the database is being updated. This way no one else can enter data until the data is finished updating. If you want to be on the safe side, you could use the AdLockPessimistic constant in the LockType parameter which locks the database as soon as the Update() method of the RecordSet Object is called.

AdCmdTable constant is the Options parameter which states how our source string (“table1”) should be interpreted. We use the AdCmdTable constant as our Options parameter because we only need to enter data to our table and in our source string, we only add our table name. If we used a source string like (“SELECT * FROM table1 WHERE NAME = ‘Andrew Schools’ “), we would have to use the AdCmdText constant in the Options parameter because we actually have a string of SQL that needs to be interpreted. More on SQL (Structured Query Language) and using the AdCmdText in the Options parameter later.

Retrieving Data Using the Recordset Object

Now lets retrieve the data we just entered into our database.

The code below reads data from our database using the following properties and methods: Open, BOF (Beginning of File), EOF (End of File), MoveFirst and MoveNext.

<%

Set RS = Server.CreateObject(“adoDB.recordset”)
RS.Open “table1”, DB, adOpenKeySet, adLockReadOnly, adCmdTable
If RS.BOF and RS.EOF Then
Response.Write “<p align=’center’>Sorry, no files!</p>”
Else
RS.MoveFirst
While Not RS.EOF
Response.Write RS(“name”)& “<BR>”
RS.MoveNext
wEnd
End If

%>

The first two lines of code is something we have already seen and talked about. The only thing different is we use a AdLockReadOnly constant instead of a AdLockOptimistic constant since we are not updating the database. Now you don’t have to do this, however it save resources since the database doesn’t have to keep track of when you are changing files in the database.

The 3rd line of code checks to see if the database is empty by using the BOF and EOF properties of the Recordset Object. If the following statement is true (no data in our database), then we tell the user by writing to the screen, Sorry, no files! If the statement is false (there is data in our database), then we proceed down to line 6. Note that it’s good pratice to check to see if there isn’t any files. If you don’t and there is no files, you will get an ugly error message which states there isn’t any files.

Line 6 tells the cursor to move to the Beginning of the file just in case it isn’t there. We don’t want to skip any files…

Line 7 starts a While…Loop. It will loop until there is no more data in our database.

Line 8 writes our data to the screen.

Line 9 tells the cursor to move to the next file in our database. Without this line, we would have a infinitive loop because we would always display the first file in our database and our While…Loop would always be true and never end, thus looping for ever or until the script timed out.

Line 10 ends our loop.

Line 11 ends our If…EndIf statement.

Now since we are opening all of these objects, it’s good practice to close them once we are finished with them. This also saves precious server memory.

<%

RS.Close

Set RS = Nothing

DB.Close

Set DB = Nothing

%>

Deleting Data Using the Recordset Object

There will come a time when you will need to delete data from your database and it’s real easy too. The code below will delete the name Kelly from the database.

<%

Set RS = Server.CreateObject(“adoDB.recordset”)
RS.Open “SELECT * FROM table1 WHERE name=’Kelly’ “, DB, adOpenStatic, adLockOptimistic, adCmdText

If RS.BOF and RS.EOF Then

Response.Write “<p align=’center’>Sorry, no files!</p>”

Else

RS.Delete

End If

RS.Close

Set RS = Nothing

DB.Close

Set DB = Nothing

%>

Line 2 opens the Recordset Object as shown in previous examples, however, the source string is different. It uses a SELECT statement of SQL which states: find all files with the name = Kelly. Note that the Options parameter is set to AdCmdText.

What’s with the * ? It means All fields. In this statement, you could replace the * with name, since we only have one field we can select from anyway, which is name.

Once we have found the file(s) with the name(s) = Kelly, line 6 deletes the first file using the Delete() method of the RecordSet Object. Why only the first file? If we wanted to delete all file(s) with the name(s) = Kelly, we would have to use a While…Loop.

<%

Dim RS

Set RS = Server.CreateObject(“adoDB.recordset”)
RS.Open “SELECT * FROM table1 WHERE name=’Kelly’ “, DB, adOpenStatic, adLockOptimistic, adCmdText

If RS.BOF and RS.EOF Then

Response.Write “<p align=’center’>Sorry, no files!</p>”

Else

RS.MoveFirst

While Not RS.EOF

RS.Delete

RS.MoveNext

wEnd

End If

RS.Close

Set RS = Nothing

DB.Close

Set DB = Nothing

%>

Updating Data Using the Recordset Object

Updating data is another very important technique when working with databases and it’s almost like adding data to a database. Look at the code below:

<%
Set RS = Server.CreateObject(“adoDB.recordset”)
RS.Open “SELECT * FROM table1 WHERE name=’Andrew’ “, DB, adOpenKeySet,adLockOptimistic,adCmdText

If RS.BOF and RS.EOF Then

Response.Write “<p align=’center’>Sorry, no files!</p>”

Else
RS(“Name”) = “Andrew loves ASP!”
RS.UpDate

End If

RS.Close

Set RS = Nothing

DB.Close

Set DB = Nothing
%>

The only thing that’s different from adding data then updating data is the lack of the AddNew() method of the Recordset Object and of course we use a SELECT statement of SQL to obtain the file we wish to update. Note that if you had more than one file with the name = Andrew, you would have to use a While…Loop like the previous example to update every file.

If you are copy and pasting these examples, remember to use the Connection Object first. For instance, if you wanted to use the code above, you would have to do the following:

<!–#include virtual=”adovbs.inc” –>
<%
Dim DB, RS
Set DB = Server.CreateObject(“adodb.connection”)
DB.Mode = adModeReadWrite
DB.Provider = “MICROSOFT.JET.OLEDB.4.0”
DB.ConnectionString = Server.MapPath(“db1.mdb”)
DB.Open
Set RS = Server.CreateObject(“adoDB.recordset”)
RS.Open “SELECT * FROM table1 WHERE name=’Andrew’ “, DB, adOpenKeySet,adLockOptimistic,adCmdText

If RS.BOF and RS.EOF Then

Response.Write “<p align=’center’>Sorry, no files!</p>”

Else
RS(“Name”) = “Andrew loves ASP!”

RS.UpDate

End If

RS.Close

Set RS = Nothing

DB.Close

Set DB = Nothing
%>

There are many different ways to read/add/update/delete data to a database including complex SQL statements then what this tutorial discusses. For more information on SQL, visit W3Schools.com

Author: Andrew Schools