Database connection not closing

[JanS]

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

 

[BoghieWanKanobe]

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

 

[FengShui1998]

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

 

[JanS]

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>

 

[Palooka]

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

rsGroups = Nothing

after you close it.

Another problem is in the

GetUserDetails

function, where you should close the

rstemp

recordset before you close the

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