connection to db best practices

Discussion in 'ASP General' started by Mike D, Oct 14, 2004.

  1. Mike D

    Mike D Guest

    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
    Mike D, Oct 14, 2004
    #1
    1. Advertising

  2. 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


    "Mike D" <> wrote in message
    news:...
    > 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], Oct 14, 2004
    #2
    1. Advertising

  3. Mike D

    Luis Guest

    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)?


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Luis, Oct 21, 2004
    #3
  4. Luis wrote:
    > 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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Oct 21, 2004
    #4
  5. Mike D

    Luis Guest

    Bob wrote:
    > 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


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Luis, Oct 21, 2004
    #5
  6. 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

    "Luis" <> wrote in message
    news:...
    > Bob wrote:
    >> 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()
    >
    Ray Costanzo [MVP], Oct 21, 2004
    #6
  7. Luis wrote:
    > Bob wrote:
    >> 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?


    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
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Oct 21, 2004
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Izvra
    Replies:
    0
    Views:
    528
    Izvra
    Dec 23, 2003
  2. karim
    Replies:
    0
    Views:
    439
    karim
    Jul 13, 2003
  3. John Dalberg
    Replies:
    3
    Views:
    558
    samuelhon
    Nov 16, 2006
  4. Ryan N.
    Replies:
    2
    Views:
    152
    Ryan N.
    Feb 11, 2004
  5. Chicken McNuggets

    Best book on C gotchas and best practices?

    Chicken McNuggets, Jul 31, 2013, in forum: C Programming
    Replies:
    9
    Views:
    254
    Fred J. Tydeman
    Aug 5, 2013
Loading...

Share This Page