Displaying two list from two tables 1

[ceeleelewis]

Hello All,

I want to display two separate list from information on two tables on my dB on one page. So far, I can generate one list from one of my tables. But when I try to display the second list along with the first, I get the following error:

ADODB.Recordset error '800a0e79' Operation is not allowed when the object is open. ?

Below is my code for review. Note: I don't want to "join" the two tables. I want two separate lists.

Sub subListUserInfo

Dim i, intFieldCount, intFieldCount_i, ii

'Define SQL Query
strSQL = "SELECT Username, Password, ID, URL FROM users ORDER BY Username"

'Open recordset passing the SQL to the connection object.
'Open as Static to be able to execute more Move commands in the recordset.
'
objRS.open strSQL, objConn, adOpenStatic, adLockOptimistic

'Check for errors in objConn
subErrorCheck

intFieldCount = objRS.Fields.Count

%>
<p align=&quot;center&quot;><a href=&quot;aspccAdmin.asp?Add=1&quot;>Add New</a></p>
<table align=&quot;center&quot; cellspacing=&quot;0&quot; cellpadding=&quot;3&quot; border=&quot;1&quot;>
<tr>
<% For i = 0 To intFieldCount -2 %>
<th><%= objRS(i).Name %></th>
<% Next %>
<th>&nbsp;</th>
</tr>

<% Do Until objRS.EOF %>
<tr>
<% For i = 0 To intFieldCount -2 %>
<td><%= objRS(i)%></td>
<% Next %>
<td>
<a href=&quot;aspccAdmin.asp?Edit=<%= objRS(&quot;ID&quot %>&quot;>Edit</a> |
<a href=&quot;aspccAdmin.asp?Delete=<%= objRS(&quot;ID&quot %>&quot;>Delete</a>
</td>

</tr>
<% objRS.Movenext %>
<% Loop %>

</table>
<%

'Define SQL Query
strSQL = &quot;SELECT Username, Password,URL, reqFormId FROM users_II ORDER BY Username&quot;

'Open recordset passing the SQL to the connection object.
'Open as Static to be able to execute more Move commands in the recordset.
'
objRS.open strSQL, objConn, adOpenStatic, adLockOptimistic

'Check for errors in objConn
subErrorCheck

intFieldCount = objRS.Fields.Count

%>
<p align=&quot;center&quot;><a href=&quot;aspccAdmin.asp?Add=1&quot;>Add New</a></p>
<table align=&quot;center&quot; cellspacing=&quot;0&quot; cellpadding=&quot;3&quot; border=&quot;1&quot;>
<tr>
<% For ii = 0 To intFieldCount_i -2 %>
<th><%= objRS(ii).Name %></th>
<% Next %>
<th>&nbsp;</th>
</tr>

<% Do Until objRS.EOF %>
<tr>
<% For ii = 0 To intFieldCount_i -2 %>
<td><%= objRS(ii)%></td>
<% Next %>
<td>
<a href=&quot;aspccAdmin.asp?Edit=<%= objRS(&quot;ID&quot %>&quot;>Edit</a> |
<a href=&quot;aspccAdmin.asp?Delete=<%= objRS(&quot;ID&quot %>&quot;>Delete</a>
</td>

</tr>
<% objRS.Movenext %>
<% Loop %>

</table>


<%
End Sub

 

[TonyU]

[tt]
check this link

http://aspfaq.com/show.asp?id=2307

for the error...

* * * * * * * * * * *
<%=Tony%>

​

 

[jeepxo]

you are trying to open the recordset twice without closing it.

you can do 1 of 2 things

Add this before this second opening of the recordset
objRS.close
objRS.open strSQL, objConn, adOpenStatic, adLockOptimistic


or change the second recordset from
objRS.open strSQL, objConn, adOpenStatic, adLockOptimistic
to
objRS2.open strSQL, objConn, adOpenStatic, adLockOptimistic

You should always explicitly close any connections to the database that you make when you are done with it. Then you should set the object to nothing to free up the memory.

VB/VBS doesn't have constructors and destructors like C++, it is suppose to handle that for you, however it doesn't handle it gracefully if you don't kill any unused objects.

 

[Tarwn]

Actually, to clarify, it is not the language (VBS) that has a problem handling object destruction and memory cleanup. VBS does allow constructors and destructors,
for example:
Dim re
Set re = New RegExp

It is just the server that has (known) issues with doing timely cleanup behind the language.

Another note on constructors and destructors as they apply to VBS, technically a Recordset is not a VBS object, it is a COM object, and therefore we are talking of two different types of objects.

Sorry to get off topic, just wanted to clarify for other readers.
-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting