RSS
| << 12.6.1- Creating a Connection to a Database | Chapter12 | 12.6.3- The Errors Collection >> |
The Properties Collection
You've seen one of the properties of the Connection object – the ConnectionString property – in the code fragments above. However, there's a whole lot of information behind the Connection object that we haven't met – and some of which we'll never need! But it's useful to know that the names and values of all the connection properties can be accessed from a single location – the Connection object's Properties collection. The Properties collection contains a Property object for each property that the connection supports.
A collection is much like an array (we covered arrays in Chapter 4 , and we met the collections of the ASP Request object in Chapter 7 ). You can look through the elements of the Properties collection in much the same way as we can look through the elements of the ASP Request.Querystring collection (or any of the other collections of the Request object). Let's try this out, and then we'll examine how it works.
Try It Out – The Properties Collection
We're going to establish a connection to the database; we won't pass any data across the connection, but instead we'll just examine the properties of the established connection.
1. Create a new file, and type in the following HTML and script. Don't forget that this file, like all the code in the book, can be obtained from the Wrox web site at http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764543636,descCd-download_code.html, so if your typing is like mine then you might prefer to download it instead of typing it in:
<%
Option Explicit
Dim strConnect
%>
<!-- #INCLUDE FILE="DataStore.asp" -->
<HTML>
<HEAD>
<TITLE>ADO Connection Properties</TITLE>
</HEAD>
<BODY>
<TABLE BORDER=1>
<TR>
<TD><B>Property</B></TD><TD><B>Value</B></TD>
</TR>
<%
Dim objConn ' Connection object
Dim objProp ' Property object
' create the connection object
Set objConn = Server.CreateObject ("ADODB.Connection")
' and open it
objConn.Open strConnect
' loop through the properties
For Each objProp In objConn.Properties
Response.Write "<TR>" & _
"<TD>" & objProp.Name & "</TD>" & _
"<TD>" & objProp.Value & " </TD>" & _
"</TR>"
Next
' now close and clean up
objConn.Close
Set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>
2. Save this file into the \inetpub\wwwroot\BegASPFiles folder, calling it ConnProps.asp.
3. Ensure that the SSI file DataStore.asp, which we gave earlier in this chapter, is also in the same folder.
4. In your web browser, browse to ConnProps.asp:
|
|
As you can see by scrolling down the page, there are a lot of properties in this collection! But there's no cause for alarm: you don't need to know about them all in order to continue and, in fact, you'll probably find that you'll rarely need to use most of them in your entire programming career. But it's useful to know that they're there.
How It Works
First of all we specify Option Explicit – as it is good practice for catching typographical errors in the code – and declare the strConnect variant ready for our connection string. Then we include the data store SSI – just as we did in Connect2.asp – to define the value of strConnect:
<%
Option Explicit
Dim strConnect
%>
<!-- #INCLUDE FILE="DataStore.asp" -->
Now we begin a table that will show the properties and their values:
<TABLE BORDER=1>
<TR>
<TD><B>Property</B></TD><TD><B>Value</B></TD>
</TR>
Once the table header is created, we need to create the table body. For this, we loop through all of the properties using a For Each … Next statement – each iteration through the loop will look at each property in turn and write a single row of the table. In preparation, we declare a couple of variants – one for the ADO Connection object, and one for the ADO Property object.
<%
Dim objConn ' Connection object
Dim objProp ' Property object
Now we create the Connection object, and open it.
' create the connection object
Set objConn = Server.CreateObject ("ADODB.Connection")
' and open it
objConn.Open strConnect
Once the connection is open we can start looping through the Properties collection. The For Each … Next statement is perfect for iterating through collections, because the control variable (in this case objProp), which references each member of the collection in turn, can also be used inside the loop. Inside this loop, we write the property's Name and Value into cells in the table:
' loop through the properties
For Each objProp In objConn.Properties
Response.Write "<TR>" & _
"<TD>" & objProp.Name & "</TD>" & _
"<TD>" & objProp.Value & " </TD>" & _
"</TR>"
Next
We've included a non-break space here – the bit. It's there to ensure that each cell in the table contains at least one character, even if the property value itself is empty. It's just a formatting trick to make the table look tidier.
And finally we can close the connection and clean up (and complete the table with a closing </TABLE> tag):
' now close and clean up
objConn.Close
Set objConn = Nothing
%>
</TABLE>
That's all there is to it. You'll find this technique of looping through a collection quite useful, and you will see it again later.
You can use the Properties collection to find out what functionality is supported on a connection. For example MSDE allows you to have a maximum row size of 8060 characters, while Access only allows 4049. Admittedly, this is unlikely to be a problem, but you could check the Maximum Row Size property to find this out. To do this you don't need to loop through the whole collection – you can just access the element you need directly, like this:
Response.Write "Cols = " & objConn.Properties("Maximum Row Size")
This uses the same collection, but you are retrieving a Connection's property value using the property name.
| << 12.6.1- Creating a Connection to a Database | Chapter12 | 12.6.3- The Errors Collection >> |
