Segment 2 - Creating a Connection

Creating a Connection, ahh the choices

There's a multitude of methods to creating a connection, and i've seen a lot of debate between DSN ( Data Source Name ) based connections and DSN-Less Connections ( Direct to source conections )

I won't be contributing to the debate of which method to use, but will advise to research the benefits and drawbacks of each, easiest item to search for in a search engine is : [red]DSN vs DSN Less[/red]

I will cover a small group of commonly used methods in this walkthrough.

Most of the connection methods used in asp tend to have 4 stages :

1.) Creating a Connection Object :
[blue]Set Con = Server.CreateObject("ADODB.Connection")[/blue]
This creates your connection object allowing you to give it parameters such as connect to what database, how to connect to it, and what type of connection you wish to have.
Con is a variable type object this can be called fairly much anything you want, as long as you follow the rules regarding reserved words and variable naming rules.

[green]Please note even in these steps there's a lot of different ways of doing this, example of this first step in a different manner is to skip the Server and go straight to :
Set Con = CreateObject("ADODB.Connection")
[/green]

2.) Establishing Data Source :
This can be set up as a string variable, a session value, or part of the Con's Collection values, or passed directly as an argument of the Connection Object

The values used for any of the aforementioned are dependant on the data source type and method of connection ...
Examples that can be assigned to a variable, to the object's collection, or as an argument to the connection object :

[red]DSNLESS Connections :[/red]

Direct to Access DB connection :
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Drive:\SomePath\myDb.mdb;User Id=admin;Password=password;"

Shared Connection to Access DB (multi-user) :
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Drive:\SomePath\myDB.mdb;Jet OLEDB:System Database=MySystem.mdw", "Username", "Password"

Connection to Excel :
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Drive:\SomePath\myData.xls;Extended Properties=""Excel 8.0;HDR=Yes"""
[green]The HDR is for Header Rows ( column names ) and so thay they can be referenced as such vs all data in spreadshield hence columns would be referred to as Field1, Field2, etc.. [/green]

Connection to Text CSV, Tab Delim, etc :
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Drive:\SomePath\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
[green]Please note there is no file name specified, only the path, reason for this is when querying the file since it is single tabular the name of the file is the table referenced (SELECT * FROM MyTextFile.txt Where FirstName='Joe'). The FMT is in regards to Format ( CSV, Delimited, etc) HDR is same as above

a nice factor about text connections like this is that you can point to the folder, and put innumerous text data files in there and reference them all with the same connection object.
[/green]


There's a Good Reference list for syntax, structure, parameters, and so forth available at:
http://www.able-consulting.com/ADO_Conn.htm

Most of the Common MDAC connections are contained in the OLE Provider for Microsoft Jet for direct to file connections.




[red]DSN Based Connections :[/red]
Personally even if developing something for myself, i prefer to use DSN based connections due to the ease of coding the ASP, it's just a matter fo creating a System DSN with a referencing name say "TestDSN" then pointing to the datasource, say "myDB.mdb"

[blue]"DSN=TestDSN;UID=UserName;Password=Pass;" [/blue]

[green]( pretty much all there is to it, although "UID" might change to "UserName" or "User ID" and same with "Password" might be "pass" or "p-word" depending on the datasource and the mdac driver. )[/green]


[red]Using the Connection String:[/red]
The connection strings just mentioned either DSN-Less or DSN based are then either added to the Connection Collection by means of :

[blue]Con.Connect[/blue] [red]ConnectionStr[/red]

Where ConnectionStr can be the actual text string, the variable which contains the string or a session value containing the string

or the connection string can be passed as an argument of the connection object's method call of open ( see next phase )

3.) Opening a Connection Object :
[blue]Con.Open[/blue] [red]ConnectionArg[/red]
this is a method call of the object Con telling it to open ConnectionStr if supplied.

Once the Connection is open is where you can begin to use the Data Environment for accessing records, inserting, deleting, or updating data.

4.) Closing and Clearing a Connection Object :
This is one of the most often overlooked steps, or misplaced steps in coding, it is necessary to close the connection otherwise the residual connections will eat up available connection slots to the data source and eat up server memory.

[blue]Con.Close
Set Con = nothing[/blue]

This calls Con's Close method, hence closing the active connection, and Set Con=nothing clears the server memory of the Connection Object


Next Section : http://www.tek-tips.com/faqs.cfm?fid=3803