connection to db best practices

[Mike D]

What are the best practices when using a db and include files?

I typically store my connection string in an include file. I then open my
db do what I need to and close the connection. I haven't put these in
functions. How does everyone else handle the connections are the in
functions? in includes?

Mike

 

[Ray Costanzo [MVP]]

I also use an include file, like so:

<%
Dim oADO, bDataOpen
Sub OpenData()
Set oADO = CreateObject("ADODB.Connection")
oADO.Open "Provider=sqloledb;Data Source=servername;Initial
Catalog=dbName;User Id=username;Password=password;"
bDataOpen = True
End Sub

Sub CloseData()
oADO.Close
Set oADO = Nothing
bDataOpen = False
End Sub

Function TextIn(TheText,MaxLength)
Dim sResult
sResult = TheText
If MaxLength > 0 Then
If Len(sResult) > MaxLength Then sResult = Left(sResult, MaxLength)
End If
sResult = Replace(sResult, "'", "''")
sResult = "'" & sResult & "'"
TextIn = sResult
End Function
%>

I'll also sometimes create functions like GetData (returns array from
..GetRows), ExecNonQuery (sub), GetSingleValue, or some thing along those
lines.

Ray at work

 

[Luis]

Is it ok/safe to put the OpenData() and CloseData() subs that you
illustrated in this example in the Global.asa file (instead of an
includes file)?

 

[Bob Barrows [MVP]]

Is it ok/safe to put the OpenData() and CloseData() subs that you
illustrated in this example in the Global.asa file (instead of an
includes file)?

If you mean: is it OK to store the connection object in session or
application, then the answer is No, not if you want your website to handle
more than one user at a time.

http://www.aspfaq.com/2053

Bob Barrows

 

[Luis]

If you mean: is it OK to store the connection object in
session or application, then the answer is No, <snip>

Luis replied:

So, would it be bad to include the following in my Global.asa?

SUB CloseRs
rs.Close
Set rs = nothing
END SUB

SUB CloseConn
conn.Close
Set conn = nothing
END SUB

Then I just add:

CloseRs()
CloseConn()

to each of my asp pages when I want to close a db connection that I've
opened on those pages.


Example:

'*** START MyStuff.asp page ***

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("connstring")

Dim strGetSql, rs
'SomeStoredProcedure does a SELECT on the db...
strGetSql = "EXEC SomeStoredProcedure @Blah = '" & SomeBlah & "'"
Set rs = conn.Execute(GetSql)

strTra = rs("Field1")
strLala = rs("Field2")

CloseRs()
CloseConn()

Response.Write("Tra is: " & strTra & "<br>" & "Lala is: " & strLala)

'*** END MyStuff.asp page. ***


The only ther thing I would have in my Global.asa is:

SUB Application_OnStart
Application("ConnString") = "PROVIDER=SQLOLEDB; Server=MyServer;
DATABASE=MyDB; USER ID=Foo; PASSWORD=Bar;"
Application("Server_Name") = "http://localhost/blah"
END SUB

 

[Ray Costanzo [MVP]]

You'd have to use an include file for this, not global.asa. But putting it
all in an include would be fine.

One thing about your CloseRS sub though - you may want to pass the RS as an
argument to the subroutine, i.e.

'''your code...
Set rs = conn.Execute(something...)
Set rs2 = conn.Execute(somethingElse...)
'''some code
CloseRS rs
CloseRS rs2



Sub CloseRS(ByRef rs)
rs.Close
Set rs = Nothing
End Sub

Ray at work

 

[Bob Barrows [MVP]]

Luis replied:

So, would it be bad to include the following in my Global.asa?

Yes, given that this code is not going to do anything. You can't call a sub
in Global.asa from an asp page ...

SUB CloseRs
rs.Close
Set rs = nothing
END SUB

SUB CloseConn
conn.Close
Set conn = nothing
END SUB

Then I just add:

CloseRs()
CloseConn()

to each of my asp pages when I want to close a db connection that I've
opened on those pages.

??? These subs are not in scope. These lines should raise an error.

Example:

'*** START MyStuff.asp page ***

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("connstring")

This is good. Nothing wrong with this.

Dim strGetSql, rs
'SomeStoredProcedure does a SELECT on the db...
strGetSql = "EXEC SomeStoredProcedure @Blah = '" & SomeBlah & "'"
Set rs = conn.Execute(GetSql)

strTra = rs("Field1")
strLala = rs("Field2")

CloseRs()
CloseConn()

These two lines of code aren't going to do anything. Don't you get an error
message when they're attempted to be executed? Is there an "on error resume
next" line that you aren't showing us?

The only ther thing I would have in my Global.asa is:

SUB Application_OnStart
Application("ConnString") = "PROVIDER=SQLOLEDB; Server=MyServer;
DATABASE=MyDB; USER ID=Foo; PASSWORD=Bar;"
Application("Server_Name") = "http://localhost/blah"
END SUB

This is good: no objects, just strings.

Bob Barrows