Object is closed until page is refreshed. How to fix? 1

[elleme]

I am getting the following message when I load a page:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/teddy/Screening.asp, line 281

The strange thing is that I didn't make any changes to the connection, just changed the criteria in the stored procedure and added some text to the page. It was working fine before.

Another strange thing is that if I refresh the page using the browser refresh button, it works fine. I tried putting in code to refresh the page, but that did not work using this code in the head:

<META http-EQUIV="Pragma" CONTENT="no-cache">
<META http-EQUIV="Expires" CONTENT="-1">

The purpose of this page is to provide users with a way to look up people, either from a dropdown list or by entering some of the last name or the ID #.

Here is the relevant code:

<%
set ListScreened = Server.CreateObject("ADODB.Command")
ListScreened.ActiveConnection = MM_Teddy_STRING
ListScreened.CommandText = "dbo.pr_ListScreeningStatus"
ListScreened.CommandType = 4
ListScreened.CommandTimeout = 0
ListScreened.Prepared = true
ListScreened.Parameters.Append ListScreened.CreateParameter("@name", 200, 1,4,Session("Name"))
ListScreened.Parameters.Append ListScreened.CreateParameter("@who", 129, 1,7,Session("Who"))
ListScreened.Parameters.Append ListScreened.CreateParameter("@idDCC", adInteger, 1,4,Session("ID"))
set ScreenedList = ListScreened.Execute
ScreenedList_numRows = 0
%>

<%
If request.Form("name") = "" Then
Session("Name") = null
Else
Session("Name") = request.Form("name")
End If

If Request.Form("Who") = "" Then
Session("Who") = null
Else
Session("Who") = request.Form("Who")
End If

If Request.Form("ID") = "" Then
Session("ID") = null
Else
Session("ID") = request.Form("ID")
End If
%>

<%
If request.Form("Next") <> "" THEN
response.Redirect("Screening.asp")
End If
%>

<%
If request.Form("Search") <> "" Then
varIdDCC = request.Form("idDCC")
Session("idDCC") = varIdDCC
response.Redirect("ViewScreened.asp")

End if
%>

<%
If request.Form("Clear") <> "" Then
Session("Name") = null
Session("Who") = null
Session("ID") = null
response.Redirect("Screening.asp")
End If
%>

<form action="" method="post" name="formLookup" id="formLookup">
<table width="650" border="0">
<tr>
<td colspan="3" class="StyleLabelsSmall">Search for a screened subject:</td>
<td width="50">&nbsp;</td>
</tr>
<tr>
<td width="200" class="StyleLabelsSmall">By last name: <br>
<span class="StyleText"><em>(name at time of screening)</em></span></td>
<td width="220" class="StyleText">Subject or Mother </td>
<td width="100"><select name="Who" id="Who">
<option value="Subject">Subject</option>
<option value="Mother">Mother</option>
</select></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td class="StyleText">First letters of last name </td>
<td><input name="Name" type="text" class="StyleText" id="Name" size="6" maxlength="4">
<span class="StyleText">(up to 4) </span> </td>
<td>&nbsp;</td>
</tr>
<tr>
<td class="StyleLabelsSmall">By DCC ID#: </td>
<td>&nbsp;</td>
<td><input name="ID" type="text" id="ID" size="10" maxlength="8"></td>
<td><input name="Next" type="submit" id="Next" value="Next"></td>
</tr>
<tr>
<td colspan="4" bgcolor="#CCCCCC">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td colspan="2"><select name="idDCC" id="idDCC">
<%
While (NOT ScreenedList.EOF)
***** ^^^^^This is the line 281 from the error message*****
%>
<option value="<%=(ScreenedList.Fields.Item("idDCC").Value)%>"><%=(ScreenedList.Fields.Item("Fullname").Value)%></option>
<%
ScreenedList.MoveNext()
Wend
If (ScreenedList.CursorType > 0) Then
ScreenedList.MoveFirst
Else
ScreenedList.Requery
End If
%>
</select>&nbsp; <input name="Search" type="submit" id="Search" value="Select">&nbsp;
<input name="Clear" type="reset" id="Clear" value="Clear"></td>
<td>&nbsp;</td>
</tr>
</table>
</form>

Thanks in advance!!

 

[gmmastros]

I suggest you modify your stored procedure so that the first line (immediately following the AS) is:

SET NOCOUNT ON

Assuming, of course, that your database is Microsoft SQL Server.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

make sure you put the following line in your stored procedure at the top...

SET ANSI_WARNINGS OFF

-DNG

 

[DotNetGnat]

and also

SET NOCOUNT ON

-DNG

 

[elleme]

Hmm, those lines were already in the stored procedure (and yes, it's SQL server). Here is the SQL code:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC dbo.pr_ListScreeningStatus (
@name varchar (4)='',
@who char (7)='',
@idDCC int =''
)
/*returns a list of names (last, first, MI) for a subject or mother of subject
web interface allows input of up to 4 char of subject's or mother's last name or subject's DOB
as an initals search parameter to populate an abbreviated dropdown menu

For any subject EVER screened, regardless of status
Subject must have already been uploaded to DCC*/

AS

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

IF @Who = 'Subject' OR @Who Is null
--Returns subject names, sorted by name
BEGIN
SELECT idDCC, (lastname + ', ' + firstname + ', ' +
CASE WHEN LEN(MI) <> 0 THEN MI ELSE '' END ) AS Fullname

FROM tblScreening
WHERE (@Name Is NULL OR lastname like @name + '%') AND
(@idDCC Is Null OR idDCC = @idDCC)
Order By lastname, firstname, MI
END

IF @Who = 'Mother'
BEGIN
--Returns mothers' names, sorted by name
SELECT idDCC, (MOMlast + ', ' + MOMfirst + ', ' +
CASE WHEN LEN(MomMI) <> 0 THEN MomMI ELSE '' END ) AS Fullname

FROM tblScreening

WHERE (@Name Is NULL OR Momlast like @name + '%')
Order By MOMlast
END

IF @Who = 'Subject ID'
BEGIN
--Returns Local codes sorted by DCC id
SELECT LocalCode,idDCC, Lastname,firstname
FROM tblScreening
Order By idDCC
END

SET NOCOUNT OFF


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[Sheco]

This stored procedure can return one recordset, two recorsets, or zero recordsets.

 

[Sheco]

sorry, just 1 or none

 

[Sheco]

OK, first your stored procedure defaults are an empty string but then later the code is written like they could possibly be null. Ok so since the default means they can't be null by just being skipped then the only way they will be null is if they are explicitly set to null. So unless the value NULL is in the session variable there is pretty much no way that could happen. So it seems like the parameter defaults should be NULL.

The second thing is that it is possible to pass all the way through the stored procedure and return absolutly nothing at all... like for instance if the input parameters have empty strings it would fall all the way through... there should be something at the bottom of the procedure that returns an empty recordset when @who doesnt match any of your IF conditions...

Third if you are going to have a "catch-all' at the bottom then you also need to exit the procedure in places along the way when you DO get a match to @who... so add some RETURN O lines in there to do these exits.

Fourth it will be a lot easier in the long run if the recordset returned by your procedure always has the same fields... not a different set of fields depending on the value of @who parameters... sometimes this would not be a good idea but in this case there are not many fields and they are very similar. So always return the same fields and add a new field that indicates which type of search was performed.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
returns a list of names (last, first, MI) for a subject or mother of subject
web interface allows input of up to 4 char of subject's or mother's last name 
or subject's DOB as an initals search parameter to populate an abbreviated 
dropdown menu

For any subject EVER screened, regardless of status
Subject must have already been uploaded to DCC
*/
ALTER PROC dbo.pr_ListScreeningStatus 
    @name  varchar(4) = Null,
    @who   char (7)   = Null,
    @idDCC int        = Null
AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

IF (@who Is null) SET @who = 'Subject'

IF @who = 'Subject' 
--Returns subject names, sorted by name
BEGIN
  SELECT 
    @who As SearchType
    ,LocalCode
    ,idDCC 
    ,Lastname
    ,Firstname 
    ,(Lastname + ', ' 
     + Firstname 
     + CASE WHEN LEN(MI) <> 0 THEN MI ELSE '' END
     ) AS Fullname
  FROM 
    tblScreening 
  WHERE   
    (@Name Is NULL OR Lastname like @name + '%')
    AND (@idDCC Is Null OR idDCC = @idDCC)
  ORDER BY
    lastname, 
    firstname, 
    MI

  -- Exit stored procedure here
  RETURN 0
END



IF @who = 'Mother'
BEGIN 
--Returns mothers' names, sorted by name
  SELECT 
    @who As SearchType
    ,LocalCode   
    ,idDCC
    ,MOMlast As Lastname
    ,MOMfirst As Firstname 
    ,(MOMlast + ', ' 
     + Firstname 
     + CASE WHEN LEN(MomMI) <> 0 THEN MomMI ELSE '' END
     ) AS Fullname
  FROM 
    tblScreening 
  WHERE 
    (@Name Is NULL OR Momlast like @name + '%')
  ORDER BY
    MOMlast

  -- Exit stored procedure here
  RETURN 0
END



IF @who = 'Subject ID'
BEGIN 
--Returns Local codes sorted by DCC id
  SELECT 
    @who As SearchType
    ,LocalCode
    ,idDCC
    ,Lastname
    ,Firstname 
    ,(Lastname + ', ' 
     + Firstname 
     + CASE WHEN LEN(MI) <> 0 THEN MI ELSE '' END
     ) AS Fullname
  FROM 
    tblScreening 
  ORDER BY
    idDCC

  -- Exit stored procedure here
  RETURN 0
END



-- If we got this far down then we have no search
-- but we still return an empty recordset:
SELECT
  Null As SearchType
  ,Null As LocalCode
  ,Null As idDCC
  ,Null As Lastname
  ,Null As Firstname
  ,Null As Fullname
WHERE
  1 = 0


SET NOCOUNT OFF


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO