armbrustconsulting.com

Resources | ASP | Database Connection

Database Connection:

With Active Server Pages, its a fairly easy process to connect to a database that resides on your server. The database doesn't even have to reside on the same server as your web pages, if you are using an N-Tier Model. The following example will show you the basics of connecting to a database with ASP.

The first thing you want to do is set up the information that will be used by your CONNECTION Object. I use CONSTANTs in this example for two reasons: it makes updating your code easier (if you keep the constant declarations at the top of the page), and it makes things easier on the server's processor if you are referencing this information more than once:

'the DATA SOURCE can be a hard coded path to a 'directory on your server, or it can point to 'a DSN that you have set up ahead of time '(check out your control panel --> ODBC Data Sources) private const datas = "DATA SOURCE=pathtothefile" 'for the PROVIDER, we'll use an MS Access database for this example 'the PROVIDER value depends on the Driver you are using for your database private const provider = "PROVIDER=Microsoft.Jet.OLEDB.3.51;" 'finally, the dbname is what you have named the Database 'that you are going to be accessing -- you can leave this out 'if you are going to be using the CONNECTION object to connect 'to more than one database private const dbname = "MasterDB"

Next, you will need to create your ADO CONNECTION Object:

dim objConn set objConn = Server.CreateObject("ADODB.Connection") objConn.Open provider & datas

You also have the option of using the CONNECTIONSTRING property of the Connection object, instead of passing these values into the Connection Object's OPEN method. The ConnectionString can have the following attributes set:

Provider=
File Name=
Remote Provider
Remote Server
URL=

Now that you have a Connection object open, you need to use a SQL Statement to get information out of the database. You can either use the Connection object directly to execute the statement, use a Command Object (a good idea if you're going to be executing a specific SQL Statement more than once), or use a Recordset Object. This example will use a RECORDSET Object:

dim RS, strSQL strSQL = "Insert Your SQL Statement Here" set RS = server.CreateObject("adodb.recordset") with RS .CursorType = 3 .CursorLocation = 3 .Open strSQL, objConn end with

A few notes on the preceding code are needed. Each property of the RecordSet has a corresponding enumeration, which is explained next. The default value is indicated, though these default values can be changed through the database and server software:

CursorType -- Use the CursorType property to specify the type of cursor that should be used when opening the Recordset object.

-1 -- AdOpenUnspecified
0 -- AdOpenForwardOnly (default)
1 -- AdOpenKeyset
2 -- AdOpenDynamic
3 -- AdOpenStatic ( requires AdUseClient for CursorLocation)

CursorLocation -- This property allows you to choose between various cursor libraries accessible to the provider. This property setting affects connections established only after the property has been set.

1 -- AdUseNone (obsolete)
2 -- AdUseClient
3 -- AdUseServer (default)

LockType -- This property specifies the type of locking when editing a record in a Recordset.

-1 -- AdLockUnspecified
1 -- AdLockReadOnly (default)
2 -- AdLockPessimistic
3 -- AdLockOptimistic
4 -- AdLockBatchOptimistic

Finally, after you are done with your RecordSet and Connection, make sure to free up your Server's resources (and don't forget to close the connection before you destroy it):

objConn.close set objConn = nothing set RS = nothing

This example was kept fairly simple. In an ideal situation, you should not pass a SQL Statement directly to your database. Check out this page for an explanation of how to secure your connections.

Also, see this page for a description of the CONNECTION Object's timeout property.

Want to discuss this article, or other development issues? Visit our message boards!

Or contact us directly with a comment or question on this article: click here !

armbrustconsulting.com