Thread: Connection String & Record Set Functions

I generally like to create functions for my DB Connection (Either MS SQL or Access) and Recordsets and maintain this info in an include file.

Code:

<%
'SQL Server DB Connection
function oConn()
	dim dbConn, dsnName
	set dbConn = Server.CreateObject("ADODB.Connection")
	'SERVER CONNECTION STRING
	dsnName = "Provider=SQLOLEDB;"		& _
	          "Data Source=ServerName;"	& _
	          "Initial Catalog=DB_Name;"	& _
		  "User Id=UserName;" 		& _
	          "Password=UserPassword"
	dbConn.ConnectionString = dsnName
	dbConn.Open
	set oConn = dbConn 
end function


'Access Database Connection
function oConn()
 	dim dbConn, dsnName
 	
	set dbConn = Server.CreateObject("ADODB.Connection")

	dsnName = "Provider=Microsoft.Jet.OLEDB.4.0;"                               & _
		  "Data Source=" & Server.MapPath("/database/db.mdb") & ";"   & _
		  "Persist Security Info=False"

	dbConn.ConnectionString = dsnName
	dbConn.Open
		
	set oConn = dbConn 
end function

'Create Regular RecordSet
function GetRS(sqlString)
        dim GetRS
	set GetRS = Server.CreateObject("ADODB.Recordset")
	with GetRS
		.CursorLocation = 3
		.CursorType = 3
		.ActiveConnection = oConn
		.Open sqlString
	end with
end function
%>

then to use in any ASP page, you would do the following:

Code:

strSQL = "SELECT * FROM tbl_TableName ORDER BY intID"
set rs = GetRS(strSQL)

with this, there is no need for the Server.CreateObject("ADODB.Recordset") or Server.CreateObject("ADODB.Connection") to be written on the indivudual ASP Pages. Also, should your connection string change, you only need to make a change on one page.