Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Database connection not closing

Status
Not open for further replies.
JanS

JanS

Technical User
Joined
Feb 21, 2001
Messages
77
Location
AU
Hi,

I have a page with some fairly intensive calls to and from a SQL Server database. Even though I am adding recordset.close and connection.close commands, the database connections are not ended. The database itself ends the connections after 5 mintues of inactivity.

Any ideas why this would be? This is proving to be a huge problem as after a period of time, noone can connect to the database. Increasing the number of connections is not an option, Id rather try and determine why connections are not being ended when the command is issued.

Thanks in advance
jan
 
Sort by date Sort by votes
Howdy,

After you close the RecordSet issue the statement 'Set rs = Nothing'. That may or may not work, but it will get you closer. An app we are writing seems to function correctly (at least as far as this issue goes...)

Later,
Roger
 
Upvote 0 Downvote


jans,

Can you post some of your code? Seems like you are going through a large loop or you actually do have huge amounts of data.


fengshui1998
 
Upvote 0 Downvote
There are quite a few calls to the database but none of the statements return a huge data set.

Have a look and see what you think.....some is greatfully borrowed from another Tek-Tips forums user :)

<% @LANGUAGE = VBScript %>
<% Option Explicit
Response.Expires = 0

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3

Dim objConn, objRS, strQuery
Dim strConnection, strBuild, upd_address

dim SQLStmt, rsGroups, GroupsArray

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
strConnection = &quot;DSN=CEOWeb;Database=CEOWeb;UID=WebApp;Password=WApword;&quot;
objConn.Open strConnection

Set objRS = Server.CreateObject(&quot;ADODB.recordset&quot;)

objRS.PageSize = 1

objRS.CacheSize = 1
objRS.CursorLocation = adUseClient

objRS.Open &quot;login_names&quot;, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect

'get group names
SQLStmt = &quot;select group_id, group_name from groups order by group_name&quot;
set rsGroups = objConn.Execute(SQLStmt)


If Request.ServerVariables(&quot;CONTENT_LENGTH&quot;) = 0 Then
objRS.AbsolutePage = 1
Else
Select Case Request.Form(&quot;Submit&quot;)
Case &quot;Add&quot;

if request.form(&quot;uname&quot;) <> &quot;&quot; then
'check that the password match
if Request.Form(&quot;password1&quot;) = Request.Form(&quot;password2&quot;) then
'Build SQL command
strQuery = &quot;INSERT INTO login_names VALUES ('&quot; & Request.form(&quot;uname&quot;) & &quot;', '&quot; & Request.form(&quot;password1&quot;) & &quot;', '&quot; & Request.form(&quot;groupid&quot;) & &quot;')&quot;

' execute the SQL Command 
Set objRS = objConn.Execute(strQuery)
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
strConnection = &quot;DSN=CEOWeb;Database=CEOWEb;UID=WebApp;Password=WApword;&quot;
objConn.Open strConnection

Set objRS = Server.CreateObject(&quot;ADODB.recordset&quot;)
objRS.PageSize = 1
objRS.CacheSize = 1
objRS.CursorLocation = adUseClient

objRS.Open &quot;login_names&quot;, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect

Response.write(&quot;Record Added Successfully.&quot;)
else
Response.Write(&quot;Please reenter password details&quot;)
end if
end if
Case &quot;Delete&quot;
if request.form(&quot;uname&quot;) <> &quot;&quot; then
'Build SQL command
strQuery = &quot;DELETE FROM login_names WHERE username = '&quot; & Request.form(&quot;uname&quot;) & &quot;'&quot;

' execute the SQL Command 
Set objRS = objConn.Execute(strQuery)
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
strConnection = &quot;DSN=CEOWeb;Database=CEOWEb;UID=WebApp;Password=WApword;&quot;
objConn.Open strConnection

Set objRS = Server.CreateObject(&quot;ADODB.recordset&quot;)
objRS.PageSize = 1
objRS.CacheSize = 1
objRS.CursorLocation = adUseClient

objRS.Open &quot;login_names&quot;, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
Response.Write(&quot;Record deleted successfully.&quot;)
end if
Case &quot;Update&quot;
if request.form(&quot;uname&quot;) <> &quot;&quot; then
'Build SQL command for saving record
strQuery = &quot;UPDATE login_names SET username = '&quot; & Request.form(&quot;uname&quot;) & &quot;'&quot;
strQuery = strQuery & &quot;, password = '&quot; & Request.form(&quot;password1&quot;) & &quot;'&quot;
strQuery = strQuery & &quot;, group_id = &quot; & Request.form(&quot;groupid&quot;)
strQuery = strQuery & &quot; WHERE user_id = &quot;
strQuery = strQuery & request.form(&quot;form_user_id&quot;) & &quot;;&quot;

' execute the SQL Command 
Set objRS = objConn.Execute(strQuery)
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
strConnection = &quot;DSN=CEOWeb;Database=CEOWEb;UID=WebApp;Password=WApword;&quot;
objConn.Open strConnection

Set objRS = Server.CreateObject(&quot;ADODB.recordset&quot;)
objRS.PageSize = 1
objRS.CacheSize = 1
objRS.CursorLocation = adUseClient

objRS.Open &quot;login_names&quot;, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
Response.Write(&quot;Record updated successfully.&quot;)
end if
End Select

End If

If Not objRS.EOF Then
Dim intRec, intValue, strValue %>
<Head>
<H2><A NAME=&quot;header1&quot;></A>Web Users Administration</H2>

<SCRIPT language=&quot;javascript&quot;>
function EditUser(obj)
{
ChosenUser = obj.name;
strUserDetails = GetUserDetails(ChosenUser);

if (strUserDetails != '')
{
start = strUserDetails.split(&quot;,&quot;);
ilength = start.length;
if (ilength > 0)
{
i = 0;
strUserName = start[0];
strPassword = start[1];
strGroupID = start[2];
}

//add values to textboxes for editing
document.frmEditUser.uname.value = strUserName;
document.frmEditUser.password1.value = strPassword;
document.frmEditUser.groupid.value = strGroupID;

//add value to hidden field
document.frmEditUser.form_user_id.value = ChosenUser;
}
}
</SCRIPT>
<SCRIPT language=&quot;vbscript&quot;>
function GetUserDetails(UserId)

set Conn = CreateObject(&quot;ADODB.Connection&quot;)
strConn = &quot;DATABASE=CEOWeb;DSN=CEOWeb;UID=WebApp;Password=WApword;&quot;
Conn.Open strConn
'get the name of the report
SQLStmt = &quot;select username, password, group_id from login_names where user_id = &quot; & UserId

rstemp = Conn.Execute(SQLStmt)
'add values to variables
strUserName = rstemp(&quot;username&quot;)& &quot;,&quot;
strPassword = rstemp(&quot;password&quot;)& &quot;,&quot;
strGroupID = rstemp(&quot;group_id&quot;)
'close the database connection
Conn.close
'pass values back
GetUserDetails = trim(strUserName) & trim(strPassword) & strGroupID

end function
function DisableButtons
frmEditUser.submit.enabled = true
end function
</SCRIPT>

</head>
<body>

<FORM name=MyForm>
<table>
<tr>
<td bgcolor=&quot;#f7efde&quot; align=left style=&quot;FONT-SIZE: small&quot;><%Response.Write &quot;User Id:&quot;%></td>
<td bgcolor=&quot;#f7efde&quot; align=left style=&quot;FONT-SIZE: small&quot;><%Response.Write &quot;User Name:&quot;%></td>
<td bgcolor=&quot;#f7efde&quot; align=left style=&quot;FONT-SIZE: small&quot;><%Response.Write &quot;Password: &quot;%></td>
<td bgcolor=&quot;#f7efde&quot; align=left style=&quot;FONT-SIZE: small&quot;><%Response.Write &quot;Group Name: &quot;%></td>
</tr>
<%do until objRS.eof%>
<tr>
<td><%Response.Write objRS(&quot;group_id&quot;)%></td>
<td>
<INPUT type=&quot;text&quot; id=text1 name=user readonly=true align=middle value=<%Response.Write objRS(&quot;username&quot;)%>>
</td>
<td>
<INPUT type=&quot;password&quot; id=text1 name=pass readonly=true value=<%Response.Write objRS(&quot;password&quot;)%>>
</td>
<td>
<INPUT type=&quot;hidden&quot; id=text1 name=Group readonly=true value=<%Response.Write objRS(&quot;group_id&quot;)%>>
<%dim DBConn, strConn, rsName, SQLQuery
Set DBConn = CreateObject(&quot;ADODB.Connection&quot;)
strConn = &quot;DATABASE=CEOWeb;DSN=CEOWeb;UID=WebApp;Password=WApword;&quot;
DBConn.Open strConn
SQLQuery = &quot;select group_name from groups where group_id = &quot; & objRS(&quot;group_id&quot;)
Set rsName = DBConn.Execute(SQLQuery)%>

<%if not rsName.eof then%>
<INPUT type=&quot;text&quot; id=text1 name=GroupName readonly=true value=<%Response.Write rsName(&quot;group_name&quot;)%>>
<%end if
rsName.close
DBConn.Close
set rsName = nothing
set DBConn = nothing %>

</td>
<td>
<INPUT type=&quot;button&quot; value=&quot;Edit&quot; id=&quot;<%=objRS(&quot;user_id&quot;)%>&quot; name=&quot;<%=objRS(&quot;user_id&quot;)%>&quot; onclick=&quot;EditUser(this)&quot;>
</td>
</tr>
<%objRS.movenext
Loop
objRS.AbsolutePage = 1%>
</TABLE>

</FORM>
<FORM ACTION=&quot;<%=Request.ServerVariables(&quot;SCRIPT_NAME&quot;)%>&quot; METHOD=&quot;POST&quot; NAME=frmEditUser>
<INPUT TYPE=&quot;hidden&quot; NAME=&quot;form_user_id&quot;>
<HR>
Selected Details: <BR><BR>
<TABLE>
<tr>
<td>Username</td>
<td>Password</td>
<td>Re-Enter Password</td>
<td>Group Name</td>
</tr>
<tr>
<td><Input type=&quot;text&quot; name=&quot;uname&quot;><BR></td>
<td><Input type=&quot;password&quot; name=&quot;password1&quot;><BR></td>
<td><Input type=&quot;password&quot; name=&quot;password2&quot;><BR></td>
<td><SELECT id=select1 name=groupid>
<OPTION selected></OPTION>

<%dim RSArray, Number_Of_Records, i
'get group names
SQLStmt = &quot;select group_id, group_name from groups order by group_name&quot;
set rsGroups = objConn.Execute(SQLStmt)

RSArray = rsGroups.getrows
rsGroups.close

Number_Of_Records = Cdbl(UBound(RSArray, 2))
i = 0
do until i = Number_Of_Records + 1%>
<option value=<%=RSArray(0,i)%>><%=RSArray(1,i)%></option>
<%i = i + 1
loop%>
</SELECT>
</td>
</tr>
</table>

<INPUT TYPE=&quot;Submit&quot; VALUE=&quot;Update&quot; Name=&quot;Submit&quot; >
<INPUT TYPE=&quot;Submit&quot; VALUE=&quot;Delete&quot; Name=&quot;Submit&quot;>
<INPUT TYPE=&quot;Submit&quot; VALUE=&quot;Add&quot; Name=&quot;Submit&quot;>
</FORM>
<%Else
Response.Write &quot;No records found.&quot;
End If

objRS.Close
objConn.close
set objConn = nothing
Set objRS = Nothing%>
</body>
</html>
 
Upvote 0 Downvote
Try deleting line 30-31, since you repeat those at line 236 and 237, and rsGroups won't be used until line 239 and 240.
The way it is now, you don't close that recordset if the IF-test at line 110 &quot;fails&quot;. You should also set
Code: 
rsGroups = Nothing
after you close it.

Another problem is in the
Code: 
GetUserDetails
function, where you should close the
Code: 
rstemp
recordset before you close the
Code: 
conn
connection at line 158.

And finally, although it is none of my business, I would strongly advice you to use the ASPCrypt component (or something similar) and store encrypted password in your database.

Hope this helps,
Palooka
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Don Child
  • Locked
  • Question Question
Oracle connection works while debugging in VBSEdit, but not when run from the command line.
Replies
2
Views
674
Don Child
Don Child
dbcoh316
  • Locked
  • Question Question
Close concatenated odbc string connection with variable
Replies
2
Views
396
xwb
xwb
TomFra
  • Locked
  • Question Question
OCR Zoning - Field data for auto-sorting is not being considered in 1 of 2 possible cases.
Replies
9
Views
887
TomFra
TomFra
elly00
  • Locked
  • Question Question
extract zip file but not in a new folder
Replies
5
Views
587
strongm
strongm
fredmartinmaine
  • Locked
  • Question Question
Properly closing Outlook
Replies
1
Views
404
fredmartinmaine
fredmartinmaine

Part and Inventory Search

Sponsor

Back
Top