Database connection function

[charon]

hi,
due to I have multiple databases my web-site, so it will be good if I can create a connection function which can be use for all the database.

Below is my coding, it seems like can't work. If declare all the connection variables (objDC, objRS..etc) inside the subroutine then it can't be used outside the subroutine. So, I declare it outside the subroutine. Does it correct?????? Please advice!

<!-- #Include file="ADOVBS.INC" -->
<%
dim objDC
dim objRS
dim objRS2
dim objRS3
Session.Timeout=720

Sub DbConnectionOpen(DataPath)
'Create and establish data connection
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30
'Use this line to use Access
objDC.Open "DBQ=" & DataPath & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;uid=;pwd=1234"
' Create recordset and retrieve values using the open connection
End Sub

Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
Set objRS3 = Server.CreateObject("ADODB.Recordset")

with objRS
.ActiveConnection = objDC
.CursorLocation = adUseClient
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
end with

with objRS2
.ActiveConnection = objDC
.CursorLocation = adUseClient
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
end with

with objRS3
.ActiveConnection = objDC
.CursorLocation = adUseClient
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
end with


call from other asp page:
DataPath = Server.MapPath("../../db/data1.mdb")
DbConnectionOpen DataPath
sSQL="SELECT * FROM TableName"
%>

How about using function??? How?? I failed to return the connection value! Please advice!

[glenngv]

Function DbConnectionOpen(ByVal DataPath)
'Create and establish data connection
Dim objDC
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30
'Use this line to use Access
objDC.Open "DBQ=" & DataPath & ";Driver={Microsoft Access Driver (*. mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;ui d=;pwd=1234"
Set DbConnectionOpen = objDC
End Function

Function RsOpen(ByVal strSQL, ByVal objDC)
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
with objRS
.ActiveConnection = objDC
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
end with
objRS.open(strSQL,objDC)
set RsOpen = objRS
End Function

'from db1
DataPath1 = Server.MapPath("../../db/data1.mdb")
set objConn1 = DbConnectionOpen(DataPath1)
sSQL1="SELECT * FROM TableName"
set objRs1 = RsOpen(sSQL1,objConn1)

'from db2
DataPath2 = Server.MapPath("../../db/data2.mdb")
set objConn2 = DbConnectionOpen(DataPath2)
sSQL2="SELECT * FROM TableName"
set objRs2 = RsOpen(sSQL2,objConn2)

[charon]

Thanks so much!!!

[charon]

hi, would it be a problem if we din't specify the byVal, if just
Function DbConnectionOpen(DataPath) ???, can you tell me the diiferent and what will happen???

[glenngv]

it is always good practice to use ByVal (pass by value)
the default in classic ASP is ByRef (pass by reference)
but in ASP.NET the default is ByVal

to see the importance and the difference between the two, read this tip from asp101: http://www.asp101.com/tips/index.asp?id=68

[whammy]

I can't imagine why byRef was chosen as the default in classic .asp... what were they thinking? :)