RSS
| << 12.3.1- Getting the Data | Chapter12 | 12.5.0- What is a Connection? >> |
Testing our Data Store
We're not going to waste any time explaining details, and some of the code you'll see won't be explained until the next chapter , but this short section of code shows how simple it is to get data from a data store.
Try It Out – Displaying Data from a Data Store
1. Using your favorite ASP editor, type the following lines of code, taking care to type in the correct file location for your own database setup in the middle:
<%Option Explicit%>
<HTML>
<HEAD>
<TITLE>Testing our connection</TITLE>
</HEAD>
<BODY>
<%
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Dim strDatabaseType
'Choose one of the following two lines, and comment out the other
strDatabaseType = "Access"
'strDatabaseType = "MSDE"
'Now we use this selection to open the connection in the appropriate way
If strDatabaseType = "Access" Then
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\datastores\Movie2000.mdb;" & _
"Persist Security Info=False"
Else
objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _
"User ID=sa;Initial Catalog=Movie;" & _
"Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"
End If
objRS.Open "Movies", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable
While Not objRS.EOF
Response.Write objRS("Title") & "<BR>"
objRS.MoveNext
Wend
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>
</BODY>
</HTML>
2. Save the code into a file with the name Connect.asp, in the \inetpub\wwwroot\BegASPFiles folder.
3. Now type the URL into your browser, to view the page:
|
|
It's pretty easy, huh? Who told you ASP database programming was hard?!
How It Works
As we said at the top of this example, we won't study every detail of this example at this stage in the book. We'll see more about some of the features seen here later in this chapter, and in Chapters 13 and 14 . But for now, there are a few points to get your teeth into.
Here's the first piece of code that's completely new to us:
Set objConn = Server.CreateObject("ADODB.Connection")
This line creates an ADO Connection object; we're going to refer to this object in our code by giving it the name objConn.
To build the connection to the data store via an OLE-DB provider, we must supply the relevant connection information – such as the name of the OLE-DB provider and the location of the data store– to the Open method of the Connection object. The Open method will use this information to open a connection.
In this example, we've used a variable called strDatabaseType in the code, which you can use to specify the database type that you're using on your local system. Select one of the lines:
strDatabaseType = "Access"
strDatabaseType = "MSDE"
Comment out the other. The code will use your selected value in the If…Then statement to select the appropriate connection details.
For the Access example database, we achieve this using the following code:
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\datastores\Movie2000.mdb;" & _
"Persist Security Info=False"
Alternatively, if you're using an MSDE database application to supply the data then you can achieve the same using the following code:
objConn.Open "Provider= SQLOLEDB;Persist Security Info=False;" & _
"User ID=sa;Initial Catalog=Movie;" & _
"Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"
This example will only work if you haven't already set a password for the sa account. In any corporate environment, or with data you wouldn't want to compromise, you should set a password, and provide it in the connection string.In order to simplify the examples in these three chapters, we have not done this.
Notice that MSDE requires more information than the Access database.
The output on the screen comes from using our Connection object to open a Recordset object (which will contain data retrieved from the Movies table of the data store), and then displaying that data as part of the web page:
objRS.Open "Movies", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable
While Not objRS.EOF
Response.Write objRS("Title") & "<BR>"
objRS.MoveNext
Wend
Don't worry about the recordset manipulation for now – we'll cover it in detail in Chapter 13.
When we've finished with the connection, we can Close it and release it from memory (we do the same for the recordset):
objConn.Close
Set objConn = Nothing
At its very simplest, that's all there is to displaying data from a data store. As you can see, this program has just three steps:
- Making a connection to the data store
- Displaying the data
- Closing the connection
But, we don't need to create a 'connection' when we open up a document or spreadsheet using Word or Excel. So why do we even need to establish a 'connection' from the ASP page to the data store? Let's focus on the physical act of connecting your data store.
| << 12.3.1- Getting the Data | Chapter12 | 12.5.0- What is a Connection? >> |
