Have you ever set up your database connection string in all your ASP pages, only to realize that you did not enter the correct drive letter or directory?  And because of this error you now have to change the code in all your pages that contain a database connection? 

A great way to help avoid this problem from occurring is to use an include file to store your connection string information, and then reference it in each page that needs to connect to your database.   This way if your database is moved, all you need to do is change the string in the include file and presto! all your applicable pages will be changed too!  (Please see our article on Repeating Headers and Footers for another great way to use include files).

What is an include file? 

An include file is a separate page that is included in your current web page by using what is called in "include tag".  I.E.:

#INCLUDE FILE="header.inc"

The tag can be inserted anywhere on a page.  However, I recommend placing this include file at the top of the page so that it is easy to find (Note: if you initiate your buffers you will need to put your "Response.Buffer = True" statement at the very top of the page, above any include files).  

What are the rules?

• Give your page an ASP name:
  
  First of all, if you use an include file in your page and you do not already have it named as an ASP page (i.e. mypage.asp) then you will need to save it with the .asp extension.
  
  
• Put your connection string in a separate page:
  
  Create a new page (preferrably in Note Pad as you won't need your standard HTML tags) and enter the following code:
  
  Dim strConnect           'variable to store your connection string.  it will be
                                    referenced in your ASP pages that connect to the 
                                    database
  
  strConnect = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\inetpub\wwwroot\mydomainname\database\databasename.mdb; UID=;PWD=;"
  
  Use the above string to connect to a Microsoft Access Database.  Use the one below to connect to a SQL server database
  
  strConnect = "Driver={SQL Server}; Server=ServerName; Database=DatabaseName; UID=;PWD=;"
  
  (Note: for the Microsoft Access connection string you will need to change the DBQ path to the absolute path to your database.  If your database is on a hosting server, ask the hosting company's Tech Support for the absolute path to the database.  You will also need to change the database name to yours, and for the SQL server connection, you will need to supply the Server Name and the Database Name.  I also suggest setting a database password in your database and then referencing it in the appropriate connection string).
  
  Save the file with a name that includes the ".inc" extension and place it in the root directory of your site. I.E.:
  
  dsnlessconnect.inc
  
  
  
• Place a reference tag for the include file at the top of your page (right below the "Response.Buffer" statement if necessary):
  
  #INCLUDE FILE="dsnlessconnect.inc"
  
  
• Reference the "strConnect" variable wherever you open a recordset:
  
  If you are going to use a query or SQL statement for your recordset, then you will need to include the following lines of code in your ASP page that opens a recordset:
  
  Dim objRec                   ' recordset variable
  Dim strSQL                   'SQL string
  
  strSQL = "SELECT * FROM Customers WHERE CustID=1"
  
  Set objRec = Server.CreateObject("ADODB.Recordset")
  objRec.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
  
  Otherwise you would change the last line of code to the following:
  
  objRec.Open "Customers", strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
  
  
• But what does this mean?
  
  In the code above we are referencing the 5 ADO Constants of the recordset object. The format is:
  
  recordset.open Source, ActiveConnection, CursorType, LockType, Options
  
  
  1. Source: will either be a variable that contains your SQL statement (strSQL) or will be the name of your table in quotes (i.e. "Customers")
     
     
  2. ActiveConnections: this is your connection string variable from your include file.
     
     
  3. CursorType: specifies whether or not your recordset us updateable, scrollable, non-scrollable, etc.  In our example we use the Constant for an updateable, non-scrollable recordset.
     
     
  4. LockType: specifies whether or not your recordset is readonly and whether or not it is updateable, etc.  In our example we use the Constant for a readonly recordset, where no updating is allowed.
     
     
  5. Options: specifies that the Source contains a command text (SQL statement) or the name of a table.