Object is closed. Why?

[myself1]

:( Hi,
I get the following message:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/TheMiddlePath/searchresults1.asp, line 16

myCount = myRS.RecordCount

I have a Select Case at the bottom:

<%
Select Case myGen
Case "brother" %>
<!--#include file="tab1.asp" -->
<% Case Else %>
<!--#include file="tab2.asp" -->
<% end Select %>

Any clues, please?

[Terrorke]

What is in your files tab1.asp and tab2.asp?

Maybe your closing the object in one of these files??

[myself1]

When I remove: myCount = myRS.RecordCount
It works fine, but no count, obviously.

The inc files both have the usual 'close' command at the bottom, eg:

<%
Wend
myRS.Close
Set myRS = Nothing
myCon.Close
Set myCon = Nothing
%>

?

[Terrorke]

Did you try to open your recordset before u use RecordeCount?

Try this :

myRS.Open
myCount = myRS.RecordCount

[mattyblah]

If there are no records the recordset will be closed by default, I believe.

[Terrorke]

I'm not sure if that's is true. But it might be :)

Here some additional information on recordcount :
Returns a long value that is the count of how many records are in a Recordset
object.

The RecordCount property returns a long value that is the number of records in the Recordset object. The Recordset must be open to use this property, otherwise a run-time error will be generated. If the provider does not support this property or the count cannot be done, a value of -1 will be returned.

The type of cursor being used by the Recordset affects whether this property can return a valid count. In general, you can obtain the actual count for a keyset and static cursor. However, you may get either a -1 or the count if a dynamic cursor is being used, and you cannot get a count if a forward-only cursor is being used (-1 is returned).

[myself1]

Hmm.
Well, when I enter a certain criteria in the form (that does not match), I only get the table headings, so the page does display, but without any data/records.

[myself1]

Strange, still not working...here's the code (still as Object Closed):

Set myRS = Server.CreateObject("ADODB.Recordset")
myGen = Request("gender")
myMin = CInt(Request("minAge"))
myMax = CInt(Request("maxAge"))
myCount = myRS.RecordCount

IF myGen = "brother" THEN
mySQLb = "SELECT * From tbl_brother WHERE BrAge >='" & myMin & "' AND BrAge <='" & myMax & "'"
myRS.Open mySQLb, myCon,3,3,1
ELSE
mySQLs = "SELECT * From tbl_sister WHERE SrAge >='" & myMin & "' AND SrAge <='" & myMax & "'"
myRS.Open mySQLs, myCon,3,3,1
END IF
%>

Then page tab1.asp inserted as inc file:
<%= myCount %>
<tr><% While Not myRS.EOF %>
<td><div align="center"><%= "BR" & Right("0000" & myRS("Br_ID"), 4) %></div></td>
<td><div align="center"><%= myRS("BrAge")%></div></td>
<td><div align="center"><%= myRS("BrStatus")%></div></td>
<td><div align="center"><%= myRS("BrOrigin")%></div></td>
</tr>
<%
myRS.MoveNext
Wend
myRS.Close
Set myRS=Nothing
myCon.Close
Set myCon=Nothing
%>

Any clues now?

[mattyblah]

If that is your whole page then it's obvious that the recordset is not open and it isn't even on the end of a connection.execute or recordset.open. What exactly are you using it for? I don't even see the myCount variable getting written to the page.

[myself1]

Here we go...
<%
Dim myCon, myRS, mySQLb, mySQLs, myGen, myMin, myMax, myCount
Set myCon = Server.CreateObject("ADODB.Connection")
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=T:\WEBSITE\TheMiddlePath\access\Mat.mdb;" &_
"Persist Security Info=FALSE"
Set myRS = Server.CreateObject("ADODB.Recordset")

myGen = Request("gender")
myMin = CInt(Request("minAge"))
myMax = CInt(Request("maxAge"))
myCount = myRS.RecordCount

IF myGen = "brother" THEN
mySQLb = "SELECT * From tbl_brother WHERE BrAge >='" & myMin & "' AND BrAge <='" & myMax & "'"
myRS.Open mySQLb, myCon,3,3,1
ELSE
mySQLs = "SELECT * From tbl_sister WHERE SrAge >='" & myMin & "' AND SrAge <='" & myMax & "'"
myRS.Open mySQLs, myCon,3,3,1
END IF
%>

ONE of the inc files (tab1.asp):
<caption>Returned <%= myCount %> Records.</caption><br />
<tr>
<th width="15%" scope="col">REFERENCE</th>
<th width="10%" scope="col">AGE</th>
<th width="15%" scope="col">STATUS</th>
</tr>
<tr><% While Not myRS.EOF %>
<td><div align="center"><%= "BR" & Right("0000" & myRS("Br_ID"), 4) %></div></td>
<td><div align="center"><%= myRS("BrAge")%></div></td>
<td><div align="center"><%= LCase(myRS("BrStatus"))%></div></td>
</tr>
<%
myRS.MoveNext
Wend
myRS.Close
Set myRS=Nothing
myCon.Close
Set myCon=Nothing
%>

[Terrorke]

Does it work now?

[myself1]

Only works when I remove myCount = myRS.RecordCount, ie the records display. If I leave in the code, it returns as object closed.

Tab1.asp (as shown) is almost as identical as tab2.asp. Fields that are Br... are Sr... & both have:
<%
myRS.MoveNext
Wend
myRS.Close
Set myRS=Nothing
myCon.Close
Set myCon=Nothing
%>
at the bottom.

[mattyblah]

Try this:

Here we go...
<%
Dim myCon, myRS, mySQLb, mySQLs, myGen, myMin, myMax, myCount
Set myCon = Server.CreateObject("ADODB.Connection")
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=T:\WEBSITE\TheMiddlePath\access\Mat.mdb;" &_
"Persist Security Info=FALSE"
Set myRS = Server.CreateObject("ADODB.Recordset")

myGen = Request("gender")
myMin = CInt(Request("minAge"))
myMax = CInt(Request("maxAge"))

IF myGen = "brother" THEN
mySQLb = "SELECT * From tbl_brother WHERE BrAge >='" & myMin & "' AND BrAge <='" & myMax & "'"
myRS.Open mySQLb, myCon,3,3,1
ELSE
mySQLs = "SELECT * From tbl_sister WHERE SrAge >='" & myMin & "' AND SrAge <='" & myMax & "'"
myRS.Open mySQLs, myCon,3,3,1
END IF
myCount = myRS.RecordCount
%>

ONE of the inc files (tab1.asp):
<caption>Returned <%= myCount %> Records.</caption><br />
<tr>
<th width="15%" scope="col">REFERENCE</th>
<th width="10%" scope="col">AGE</th>
<th width="15%" scope="col">STATUS</th>
</tr>
<tr><% While Not myRS.EOF %>
<td><div align="center"><%= "BR" & Right("0000" & myRS("Br_ID"), 4) %></div></td>
<td><div align="center"><%= myRS("BrAge")%></div></td>
<td><div align="center"><%= LCase(myRS("BrStatus"))%></div></td>
</tr>
<%
myRS.MoveNext
Wend
myRS.Close
Set myRS=Nothing
myCon.Close
Set myCon=Nothing
%>

[myself1]

BINGO!!

Well done & thanks (to all...).