Re: Proper Database Connection Cleanup

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

The information about this happening only when the page is reloaded before the first request completes is your clue. If the page is taking a long time to process the recordset and you get another access during that time, think about what is happening in that case.

You might want to investigate GetRows as both a performance enhancement and possible solution to your problem. GetRows will read the entire recordset into a 2 dimensional array, allowing you to close the recordset right away instead of keeping it open to move through the records. It's so much faster also, because you aren't having so many requests to the database. I always use it unless I just need one or 2 records. Once you get the hang of it it's not that difficult.

Also make sure you are using the correct cursor type.

--

Bill James


"Jonathan" <Jonathan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:9495FFE3-D56E-460F-86B4-EC07FBCAA9B1@xxxxxxxxxxxxxxxx
Here are the modified functions I use to open/close a database connection.

Public Sub ConnectToDB2(byref XCmd, byref YRS, byRef Conn)


'this function connects to the database
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Mode = 3
Conn.Open ("DRIVER={MySQL ODBC 3.51
Driver};SERVER=localhost;DATABASE=DB;USER=root;PASSWORD=blahblahblah;OPTION=3;")

Set XCmd= Server.CreateObject("ADODB.Command")
XCmd.ActiveConnection = Conn
Set YRS = Server.CreateObject ("ADODB.Recordset")
end Sub

Public Sub CloseDBCon(Byref XObjCmd, byref YObjRS, byref ZObjCon)

Set XObjCmd.ActiveConnection = Nothing
If YObjRS.State <> 0 then YObjRS.Close
ZObjCon.Close
Set XObjCmd = Nothing
Set YObjRS = Nothing
Set ZObjCon = Nothing

End Sub


All of my pages are having issues with a resource in use, but they only
happen when you double click the link before the page loaded from the initial
click. The exception to this is my login page with has a submit button that
refers back to the same file with a redirect after the user enters a
password. A second redirect is used if the user entered the right password.
At this point a internal sever error (500) or a resource in use error occurs
depedning if I am looking at the page on my network or over the internet
respectively.

I do call the CloseDBCon function before the redirect. This should clean up
all open DB connections.

I was using the same objRS on the page for multiple queries but I think its
fixed now. Here's what I did.

ObjCmd.CommandText = sqlstring
Set ObjRS = ObjCmd.Execute

..... use data from DB

ObjRS.Close
ObjCmd.CommandText = sqlstring
Set ObjRS = ObjCmd.Execute

I didn't think I needed to close ObjCmd. I do use ObjRS and ObjCmd in some
of my include pages. A connection is made with these variables on my page at
the first few lines and then the include files start a query with them. I
would assume that this is ok because they are opened in the main page.

I have server-side ASP script debugging enabled, but this issue doesn't
generate a Just-in-Time debugging opportunity. I'm wondering if the error is
from DB connections in my code or from something else? The ADO DB objects are
the only objects I use. Really the only debug info I'm getting from IIS is
internal server error or resource in use.

Last, I am using MySQL. Do they have a limit for the maximum DB connections
somewhere? I am new to using their database and I do connect two or three
times in some cases.

Thanks for the help.

"Richard Mueller [MVP]" wrote:

When I am done with a recordset I use the Close method. I can reuse the
object by assigning another query and then use Open again. When the program
is done, I use the Close method of the Recordset and Connection objects.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

"Bill James" <wgjames@xxxxxxxx> wrote in message
news:eqrDJsaeHHA.4564@xxxxxxxxxxxxxxxxxxxxxxx
Two suggestions to add to your cleanup.

Set ObjCmd.ActiveConnection = Nothing
ObjRS.Close

Also, your statement, "This code will be used over and over", makes me
wonder if you are using the same recordset variable multiple times in the
same page.

--

Bill James


"Jonathan" <Jonathan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:590224D5-3D64-4C70-AD3A-51335E1357DB@xxxxxxxxxxxxxxxx
I am running IIS with classic ASP and I am having issues loading some of my
pages. I get an error that says:

"The requested resource is in use. "

What I want to do is have a function to open the database in an include
file. This code will be used over and over so it needs to be in one spot.
Here is the code that I am using in an include file named DB.inc:

Public Sub ConnectToDB2(byref XCmd, byref YRS, byRef Conn)


'this function connects to the database
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Mode = 3
Conn.Open ("DRIVER={MySQL ODBC 3.51
Driver};SERVER=localhost;DATABASE=DB;USER=admin;PASSWORD=blahblahblah;OPTION=3;")

Set XCmd= Server.CreateObject("ADODB.Command")
XCmd.ActiveConnection = Conn
Set YRS = Server.CreateObject ("ADODB.Recordset")
end Sub

In my pages, I open the database by calling ConnectToDB2 ObjCmd, ObjRS,
ObjCon At the end of the page, I clean everything up like this:

Set ObjRS = Nothing
Set ObjCmd = Nothing
ObjCon.Close
Set ObjCon = Nothing

Any ideas why I keep getting the resource in use errors? I have several
include files but the database in opened in my page. The page includes the
inc files that manipulate the data from the DB. To have only one DB
connection object, I open the DB only once in my page and clean it up at
the
end.

Thanks for any ideas.



.

Relevant Pages

  • Re: Proper Database Connection Cleanup
    ... Here are the modified functions I use to open/close a database connection. ... Public Sub ConnectToDB2(byref XCmd, byref YRS, byRef Conn) ...
    (microsoft.public.scripting.vbscript)
  • Re: ODBC Connection to SQL Server Compact
    ... Sylvain Lafontaine, ing. ... "The database file has been created by an earlier ... The conn object opened "nicely". ... Dim Conn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: Proper Database Connection Cleanup
    ... but I think two of them have a database query in them. ... Public Sub ConnectToDB2(byref XCmd, byref YRS, byRef Conn) ... Set ObjRS = ObjCmd.Execute ... from DB connections in my code or from something else? ...
    (microsoft.public.scripting.vbscript)
  • Re: ODBC Connection to SQL Server Compact
    ... "The database file has been created by an earlier ... The conn object opened "nicely". ... Dim Conn As ADODB.Connection ... 'Delete All Records from Mobile Database ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to get the data from a huge table efficiently?
    ... only display/ship the results on the client-side. ... // SQL Server 2000 Database ... Connection conn = null; ... CallableStatement qstmt = conn.prepareCall(sql, ...
    (comp.lang.java.databases)