<% Set oADO = OpenDatabase(database_full_path) Set rs = oADO.Execute("select * from Mini order by id") If Not rs.EOF Then aData = rs.GetRows rs.Close : Set rs = Nothing oADO.Close : Set oADO = Nothing aColumns = UBound(aData,1) + 1 aRows = UBound(aData,2) + 1 response.write(aRows & " rows x " & aColumns & " columns
") Response.Write "" Response.Write "" for i = 0 to (aColumns - 1) Response.Write "" next Response.Write "" for curColumn = 0 to (aRows - 1) Response.Write "" for curRow = 0 to (aColumns - 1) Response.Write "" next Response.Write "" next Response.Write "
" & i & " 
" & aData(curRow, curColumn) & " 
" %> [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <6a68ace3.0408192349.60c8be60@posting.google.com> [ref] => <#nbrEtThEHA.140@TK2MSFTNGP12.phx.gbl> [htmlstate] => on_nl2br [postusername] => Mike [ip] => mike_newsgroups [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 4 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> <% Set oADO = OpenDatabase(database_full_path) 'Set rs = oADO.Execute("select * from Mini order by id") Set rs = oADO.Execute("exec qtrade_3 1") If Not rs.EOF Then aData = rs.GetRows rs.Close : Set rs = Nothing oADO.Close : Set oADO = Nothing aColumns = UBound(aData,1) + 1 aRows = UBound(aData,2) + 1 response.write(aRows & " rows x " & aColumns & " columns
") Response.Write "" Response.Write "" for i = 0 to (aColumns - 1) Response.Write "" next Response.Write "" for curRow = 0 to (aRows - 1) Response.Write "" for curColumn = 0 to (aColumns - 1) Response.Write "" next Response.Write "" next Response.Write "
" & i & " 
" & aData(curRow, curColumn) & " 
" %> [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <6a68ace3.0408200214.271d660b@posting.google.com> [ref] => <#nbrEtThEHA.140@TK2MSFTNGP12.phx.gbl> [htmlstate] => on_nl2br [postusername] => Mike [ip] => mike_newsgroups [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 5 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> When to close db objects in a Web Application - ASP Database

When to close db objects in a Web Application - ASP Database

Do I need to close and destroy the database connection objects ON EACH PAGE when I have my site set up as an application (using eg. conn.Close : Set conn = nothing)? I normally close my db connection objects on asp pages that are NOT set up as an application, but does this also apply to asp pages that ARE set up as applications? Below is an example to illustrate my question: When I connect to a database (that are not set up as an application) using this method: Dim connStr, conn connStr = "PROVIDER=MyProvider; DATA SOURCE=MyServer; USER ID=MyId; PASSWORD=MyPass" ...

  1. #1

    Default When to close db objects in a Web Application

    Do I need to close and destroy the database connection objects ON EACH
    PAGE when I have my site set up as an application (using eg. conn.Close
    : Set conn = nothing)?

    I normally close my db connection objects on asp pages that are NOT set
    up as an application, but does this also apply to asp pages that ARE set
    up as applications?

    Below is an example to illustrate my question:

    When I connect to a database (that are not set up as an application)
    using this method:

    Dim connStr, conn
    connStr = "PROVIDER=MyProvider; DATA SOURCE=MyServer; USER ID=MyId;
    PASSWORD=MyPass"
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Mode = 3
    conn.Open connStr

    I normally close and destroy the conn object using:

    conn.Close
    Set conn = nothing


    However, if I do set the site up as an application using the following
    global.asa file:

    <SCRIPT LANGUAGE=VBScript RUNAT=Server>
    SUB Application_OnStart
    Application("ConnString") = "PROVIDER=MyProvider; DATA SOURCE=MyServer;
    USER ID=MyId; PASSWORD=MyPass"
    Application("Server_Name") = "http://myserver/myapp"
    END SUB

    SUB Session_OnStart
    startPage = "login.asp"
    Session.Timeout = 10
    END SUB
    </SCRIPT>


    And include the following in each of the asp pages that need to connect
    to the database:

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

    SQLString = "SELECT x,y,z FROM TableName WHERE x = '" & something & "'"
    Set rs = conn.Execute(SQLString)
    'Do rest of processing


    Do I need to close and destroy the conn object on EACH of the asp pages
    in this application that connect to the db?


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Luis Guest

  2. #2

    Default Re: When to close db objects in a Web Application

    > Do I need to close and destroy the database connection objects ON EACH 

    Yes, this is good practice. I see no advantage to leaving these out,
    application or not.
     

    See above.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

  3. #3

    Default Re: When to close db objects in a Web Application


    "Luis" <co.za> wrote in message
    news:%phx.gbl... 

    YES!

    You should connect, get your stuff, close, and destroy as fast as possible.

    I personally do stuff like:
    <%
    sSQL = "select something from somewhere"
    Set oADO = CreateObject("ADODB.Connection")
    oADO.Open myConnectionString
    Set rs = oADO.Execute(sSQL)
    If Not rs.EOF Then aData = rs.GetRows
    rs.Close : Set rs = Nothing
    oADO.Close : Set oADO = Nothing
    %>

    <html>
    .....

    <%
    If Not IsEmpty(aData) Then
    For i = 0 To UBound(aData, 2)
    Response.Write aData(0,i) & "<br>"
    Next
    Else
    Response.Write "no records"
    End If
    %>

    Actually, I'll often use a function that returns an array of data from a
    recordset, like, maybe something like:

    Dim oADO
    Function GetData(sql, openConnection)
    If openConnection Then
    Set oADO = CreateObject("ADODB.Connection")
    oADO.Open myConnectionString
    End If

    Set rs = oADO.Execute(sSQL)
    If Not rs.EOF Then GetData = rs.GetRows)
    rs.Close : Set rs = Nothing
    If Open Connection Then
    oADO.Close : Set oADO = Nothing
    End If
    End Function


    aData = GetData("select something from somewhere", true)

    Ray at work







     


    Ray Guest

  4. #4

    Default Re: When to close db objects in a Web Application

    Ray your post was full of juicy goodness, I love those functions,
    thanks for posting it. After an hour of playing around I finally know
    how to use getrows (evil grin). Here's my play code for anyone else
    interested in getrows. Ubound was a little confusing but after doing
    this I now know why they called it that instead of sizeOf or something
    like that.


    <!--#include file="settings.inc"-->
    <!--#include file="utilityfunctions.inc"-->
    <%
    Set oADO = OpenDatabase(database_full_path)

    Set rs = oADO.Execute("select * from Mini order by id")
    If Not rs.EOF Then aData = rs.GetRows
    rs.Close : Set rs = Nothing
    oADO.Close : Set oADO = Nothing

    aColumns = UBound(aData,1) + 1
    aRows = UBound(aData,2) + 1

    response.write(aRows & " rows x " & aColumns & " columns<br>")


    Response.Write "<table border=1>"
    Response.Write "<tr>"
    for i = 0 to (aColumns - 1)
    Response.Write "<td align=""left"" valign=""top""><font
    face=""Verdana, Arial, sans-serif"" size = ""1"">" & i & "&nbsp;</td>"
    next
    Response.Write "</tr>"


    for curColumn = 0 to (aRows - 1)
    Response.Write "<tr>"
    for curRow = 0 to (aColumns - 1)
    Response.Write "<td align=""left"" valign=""top""><font
    face=""Verdana, Arial, sans-serif"" size = ""1"">" & aData(curRow,
    curColumn) & "&nbsp;</td>"
    next
    Response.Write "</tr>"
    next
    Response.Write "</table>"
    %>
    Mike Guest

  5. #5

    Default Re: When to close db objects in a Web Application

    I think in my earlier post I had the rows and columns variable names
    transposed at one point (sorry its late). Here's the correct one...

    <!--#include file="settings.inc"-->
    <!--#include file="utilityfunctions.inc"-->
    <%
    Set oADO = OpenDatabase(database_full_path)

    'Set rs = oADO.Execute("select * from Mini order by id")
    Set rs = oADO.Execute("exec qtrade_3 1")

    If Not rs.EOF Then aData = rs.GetRows
    rs.Close : Set rs = Nothing
    oADO.Close : Set oADO = Nothing

    aColumns = UBound(aData,1) + 1
    aRows = UBound(aData,2) + 1

    response.write(aRows & " rows x " & aColumns & " columns<br>")


    Response.Write "<table border=1>"
    Response.Write "<tr>"
    for i = 0 to (aColumns - 1)
    Response.Write "<td align=""left"" valign=""top""><font
    face=""Verdana, Arial, sans-serif"" size = ""1"">" & i & "&nbsp;</td>"
    next
    Response.Write "</tr>"


    for curRow = 0 to (aRows - 1)
    Response.Write "<tr>"
    for curColumn = 0 to (aColumns - 1)
    Response.Write "<td align=""left"" valign=""top""><font
    face=""Verdana, Arial, sans-serif"" size = ""1"">" & aData(curRow,
    curColumn) & "&nbsp;</td>"
    next
    Response.Write "</tr>"
    next
    Response.Write "</table>"
    %>
    Mike Guest

  6. #6

    Default Re: When to close db objects in a Web Application

    "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in message news:<phx.gbl>... 

    Is there a way of wrapping something like the getrows function above
    so that it acts just like a recordset object, so that it is easy to
    change code over by just changing rs? I mean something like this:

    instead of:
    set rs = cn.execute(sql)

    have this:
    set rs = GetData(cn.execute(sql))

    and it works exactly the same like this:

    While not rs.eof
    response.write(rs(0) & "<br>")
    wend

    The reason I am asking is that it sounds like getrows is the way to go
    from the ASPFAQ article I read and posts like Ray's, but it seems to
    me I have to rewrite all my code to use it as it is shown in the
    examples...

    -Mike
    Mike Guest

  7. #7

    Default Re: When to close db objects in a Web Application

    Mike wrote: 
    >
    > Is there a way of wrapping something like the getrows function above
    > so that it acts just like a recordset object, so that it is easy to
    > change code over by just changing rs? I mean something like this:
    >
    > instead of:
    > set rs = cn.execute(sql)
    >
    > have this:
    > set rs = GetData(cn.execute(sql))
    >
    > and it works exactly the same like this:
    >
    > While not rs.eof
    > response.write(rs(0) & "<br>")
    > wend
    >
    > The reason I am asking is that it sounds like getrows is the way to go
    > from the ASPFAQ article I read and posts like Ray's, but it seems to
    > me I have to rewrite all my code to use it as it is shown in the
    > examples...
    >
    > -Mike[/ref]


    There are several examples in aspfaq. Doing this:

    If IsArray(ar) then
    for i=0 to ubound(ar,2)
    response.write ar(0,i) & "<BR>"
    next
    End if

    accomplishes the same thing (much more efficiently) as your incomplete (and
    deprecated) While loop. Why incomplete? Well, as written, you have an
    infinite loop (you forgot the movenext).

    --
    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 Guest

  8. #8

    Default Re: When to close db objects in a Web Application

    There isn't anything I could think of off the top of my head that would
    allow for easy modifying of your code like you suggested. I suggest either
    changing things one page at a time, or if you decide to do the whole
    ..GetRows thing, just start from this point in time and do it that way in the
    future. I like to think that I'm pretty typical in that if I go back and
    look at pages I've created over the past few years, the consistency among
    them could be better. I probably have many ".MoveFirst" lines in some old
    pages, many do while not rs.eof lines, a bunch of SELECT *s, and all those
    kinds of things that I've learned to stay away from.

    Ray at work

    "Mike" <com> wrote in message
    news:google.com... [/ref]
    >
    > Is there a way of wrapping something like the getrows function above
    > so that it acts just like a recordset object, so that it is easy to
    > change code over by just changing rs? I mean something like this:
    >
    > instead of:
    > set rs = cn.execute(sql)
    >
    > have this:
    > set rs = GetData(cn.execute(sql))
    >
    > and it works exactly the same like this:
    >
    > While not rs.eof
    > response.write(rs(0) & "<br>")
    > wend
    >
    > The reason I am asking is that it sounds like getrows is the way to go
    > from the ASPFAQ article I read and posts like Ray's, but it seems to
    > me I have to rewrite all my code to use it as it is shown in the
    > examples...
    >
    > -Mike[/ref]


    Ray Guest

  9. #9

    Default Re: When to close db objects in a Web Application

    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in message news:<Ooe#phx.gbl>...
     

    whats wrong with do while not rs.eof??

    Bob mentioned while not rs.eof is deprecated... deprecated where? is
    there a list somewhere?

    Thanks,
    Mike
    Mike Guest

  10. #10

    Default Re: When to close db objects in a Web Application

    > Bob mentioned while not rs.eof is deprecated... deprecated where? is

    I think he was talking about while/wend, which I have heard (but haven't
    confirmed) has memory leaks...

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

  11. #11

    Default Re: When to close db objects in a Web Application

    Mike wrote: 
    >
    > whats wrong with do while not rs.eof??[/ref]

    Nothing
     
    Yes, Aaron is correct: I was talking about While...Wend. One of my VB books
    (I think it was "Hardcore Visual Basic") mentioned that this language
    element was on its way out. Unfortunately I can't find anything to that
    effect at msdn. While not explicitly advising users not to use it, they do
    include this little piece of encouragement to use Do ... Loop instead: "Note
    The Do...Loop statement provides a more structured and flexible way to
    perform looping."

    http://msdn.microsoft.com/library/en-us/script56/html/vsstmwhile.asp

    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 Guest

  12. #12

    Default Re: When to close db objects in a Web Application

    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:%phx.gbl... 
    > >
    > > whats wrong with do while not rs.eof??[/ref]
    >
    > Nothing

    > Yes, Aaron is correct: I was talking about While...Wend. One of my VB[/ref]
    books 
    "Note 


    There's a passing reference to it in the Red VBA Coding Conventions,
    which is a sibling of the Red VBA Naming Conventions and tangentially
    associated with VBScript.
    http://www.xoc.net/standards/rvbacc.asp#DeprecatedFeatures

    Also, the WEND Keyword is not supported in Visual Basic .NET and is replaced
    with END WITH.
    http://msdn.microsoft.com/library/en-us/vbcn7/html/vaconflowcontrol.asp


    Chris Guest

  13. #13

    Default Re: When to close db objects in a Web Application

    Chris Hohmann wrote: 
    >
    >
    > There's a passing reference to it in the Red VBA Coding
    > Conventions, which is a sibling of the Red VBA Naming Conventions
    > and tangentially associated with VBScript.
    > http://www.xoc.net/standards/rvbacc.asp#DeprecatedFeatures
    >
    > Also, the WEND Keyword is not supported in Visual Basic .NET and is
    > replaced with END WITH.
    > http://msdn.microsoft.com/library/en-us/vbcn7/html/vaconflowcontrol.asp[/ref]

    Thanks, Chris.

    Bob
    --
    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 Guest

Similar Threads

  1. Keyhole DRM Application has encountered a problem andneeds to close
    By xoxgothicgalxox in forum Macromedia Shockwave
    Replies: 2
    Last Post: September 20th, 04:14 AM
  2. Can flex application will support the session objects
    By Gokulanand in forum Macromedia Flex General Discussion
    Replies: 8
    Last Post: June 21st, 10:41 AM
  3. How to access child application objects?
    By huhgawz in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: August 24th, 07:42 PM
  4. Testing .NET Web application with Flash objects
    By BoeingTester in forum Macromedia Flash Player
    Replies: 0
    Last Post: May 25th, 03:48 PM
  5. NEW: XML Application Objects
    By Terence in forum PHP Development
    Replies: 17
    Last Post: October 5th, 02:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •