Stored procedure problem update - help needed

Hello everyone,

I'm using the following procedure to do two functions.

1) Extract all zip codes from a database of American zip codes, which
falls within a 15 miles radius from a predefined zip code (variable
@zipcode) Then...
2) Compare the results to a zip code column in a customers table, for
matching records and provide a recorset including bsId and bsZipCode.

eg There are 2 sitters living within 15 miles of your zipcode area,
Which are bsID...

Now the procedure is working fine and returns the expected results as
long as the recordset has some records, but if the recordset has no
records (empty recordset) then I receive the following error message on
the webpage.

ERROR MESSAGE

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/Parents/Sign_up8.asp, line 41

Line 41 refers to this part of the code
....
' set the record count
Sitters15_2_total = Sitters15_2.RecordCount


Could anyone tell me what is causing this error? Do I need to add
something to the proc for error handling if the recordset is empty?

I'm very new to this and not the one who wrote the proc, so a full
explanation, or actual fix would be greatly appreciated.


FOLLOWING IS THE STORED PROCEDURE AS WELL AS THE ASP CODE CALLING THE
PROCEDURE ON MY PAGE.

I'M USING DREAMWEAVER - VBSCRIPT - SQL2000


Thanks, Robert



CREATE PROCEDURE dbo.psFindSitters15miles2
(@zipcode char(7))
AS
SET NOCOUNT ON
Declare @Result varchar(90)
Declare @lat decimal(9,6)
Declare @lng decimal(9,6)
Declare @Latitude decimal(9,6)
Declare @Longitude decimal(9,6)
set @Result = 'err';
--find the parent
if (@zipcode<>0) begin --find LAT and LNG
select @lat=[Latitude],@lng=[Longitude] FROM tbZip WHERE ZipCode =
@zipcode
--select @lat=[Latitude], @lng=[Longitude] FROM tbZip WHERE ZipCode =
90210
end;
if (@lat<> 0) begin --another query
SELECT bsID, bsZipCode FROM tbSitters WHERE bsZipCode in (SELECT
ZipCode FROM tbZip WHERE 15 > 3959 * ACOS(SIN(@Lat/57.3) *
SIN(Latitude/57.3) + COS(@Lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@Lng/57.3))) )
end;

GO



cODE CALLING THE PROCEDURE

<%
Dim Command1__zipcode
Command1__zipcode = "90210"
if(Request("zipcode") <> "") then Command1__zipcode =
Request("zipcode")

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_Phoneababysitter_STRING
Command1.CommandText = "dbo.psFindSitters15miles2"
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3,
4)
Command1.Parameters.Append Command1.CreateParameter("@zipcode", 129,
1,7,Command1__zipcode)
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
set Sitters15_2 = Command1.Execute
Sitters15_2_numRows = 0

%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
Sitters15_2_numRows = Sitters15_2_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats
variables

Dim Sitters15_2_total
Dim Sitters15_2_first
Dim Sitters15_2_last

' set the record count
Sitters15_2_total = Sitters15_2.RecordCount

' set the number of rows displayed on this page
If (Sitters15_2_numRows < 0) Then
Sitters15_2_numRows = Sitters15_2_total
Elseif (Sitters15_2_numRows = 0) Then
Sitters15_2_numRows = 1
End If

' set the first and last displayed record
Sitters15_2_first = 1
Sitters15_2_last = Sitters15_2_first + Sitters15_2_numRows - 1

' if we have the correct record count, check the other stats
If (Sitters15_2_total <> -1) Then
If (Sitters15_2_first > Sitters15_2_total) Then
Sitters15_2_first = Sitters15_2_total
End If
If (Sitters15_2_last > Sitters15_2_total) Then
Sitters15_2_last = Sitters15_2_total
End If
If (Sitters15_2_numRows > Sitters15_2_total) Then
Sitters15_2_numRows = Sitters15_2_total
End If
End If
%>

<%
' *** Recordset Stats: if we don't know the record count, manually
count them

If (Sitters15_2_total = -1) Then

' count the total records by iterating through the recordset
Sitters15_2_total=0
While (Not Sitters15_2.EOF)
Sitters15_2_total = Sitters15_2_total + 1
Sitters15_2.MoveNext
Wend

' reset the cursor to the beginning
If (Sitters15_2.CursorType > 0) Then
Sitters15_2.MoveFirst
Else
Sitters15_2.Requery
End If

' set the number of rows displayed on this page
If (Sitters15_2_numRows < 0 Or Sitters15_2_numRows >
Sitters15_2_total) Then
Sitters15_2_numRows = Sitters15_2_total
End If

' set the first and last displayed record
Sitters15_2_first = 1
Sitters15_2_last = Sitters15_2_first + Sitters15_2_numRows - 1

If (Sitters15_2_first > Sitters15_2_total) Then
Sitters15_2_first = Sitters15_2_total
End If
If (Sitters15_2_last > Sitters15_2_total) Then
Sitters15_2_last = Sitters15_2_total
End If

End If
%>

Re: Stored procedure problem update - help needed

ROBinBRAMPTON wrote:
> Hello everyone,
>
> I'm using the following procedure to do two functions.
>
> 1) Extract all zip codes from a database of American zip codes, which
> falls within a 15 miles radius from a predefined zip code (variable
> @zipcode) Then...
> 2) Compare the results to a zip code column in a customers table, for
> matching records and provide a recorset including bsId and bsZipCode.
>
> eg There are 2 sitters living within 15 miles of your zipcode area,
> Which are bsID...
>
> Now the procedure is working fine and returns the expected results as
> long as the recordset has some records, but if the recordset has no
> records (empty recordset) then I receive the following error message
> on the webpage.
>
> ERROR MESSAGE
>
> ADODB.Recordset error '800a0e78'
>
> Operation is not allowed when the object is closed.
>
> /Parents/Sign_up8.asp, line 41
>
> Line 41 refers to this part of the code
> ...
> ' set the record count
> Sitters15_2_total = Sitters15_2.RecordCount
>
>
> Could anyone tell me what is causing this error? Do I need to add
> something to the proc for error handling if the recordset is empty?

This error does not mean the recordset is empty. It means that a closed
recordset was returned. I'm not exactly sure why this would be the case.
Have you tested this procedure with Query Analyzer? I have no idea whether
to blame the procedure or to blame your asp code.

I cannot emphasize enough the importance of testing stored procedures
(actually ALL queries) with Query Analyzer BEFORE attempting to run them in
ASP. I know you said in the other newsgroup that somehow you were not able
to use QA against the server. You MUST resolve this situation if you are to
have any hope of being able to develop ASP pages against that database. Even
if it means getting a free copy of MSDE and creating a duplicate of that
database on your development machine.

>
> I'm very new to this and not the one who wrote the proc, so a full
> explanation, or actual fix would be greatly appreciated.

This is not possible: we do not have access to your database so we cannot
test your code or your procedure! It may be time to admit to whoever has
given you this task that you may not be up to it ... However, read on:

> CREATE PROCEDURE dbo.psFindSitters15miles2
> (@zipcode char(7))
> AS
> SET NOCOUNT ON

> if (@lat<> 0) begin --another query
> SELECT bsID, bsZipCode FROM tbSitters WHERE bsZipCode in (SELECT
> ZipCode FROM tbZip WHERE 15 > 3959 * ACOS(SIN(@Lat/57.3) *
> SIN(Latitude/57.3) + COS(@Lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@Lng/57.3))) )
> end;

You do not have an else branch to handle the case where @lat contains zero.
This may be why you got the error about the "closed" recordset. You should
add something like:

else
SELECT bsID, bsZipCode FROM tbSitters WHERE 1=2

This will make sure a recordset gets returned (it will be empty, but at
least it will be open).

OK, you got rid of the output parameter. That means you no longer need the
explicit Command object. If I were writing this code, it would look
something like this

> <%
> Dim Command1__zipcode
> Command1__zipcode = "90210"
> if(Request("zipcode") <> "") then Command1__zipcode =
> Request("zipcode")
>
> %>
<%
dim cn, rs
set cn=createobject("adodb.connection")
on error resume next
cn.open MM_Phoneababysitter_STRING
if err <> 0 then
response.write "The following error occurred while trying to " & _
"open the database connection:
"
response.write err.description
else
set rs=createobject("adodb.recordset")
rs.cursorlocation=3 'adUseClient, to make sure RecordCount works
err.clear
cn.psFindSitters15miles2 Command1__zipcode, rs
if err <> 0 then
response.write "The following error occurred while trying to " & _
"open the recordset:
"
response.write err.description
else
'check to verify that the recordset contains records:
if rs.eof then
response.write "The recordset contains no records"
else
'do all your other stuff here
end if

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Stored procedure problem update - help needed

When I was facing this error I noticed that my page was timing out. The
records were there but it was taking a lot of time to return the result
set and thus it was timing out. So I increased the connection time out
and it worked for me.

I'm not sure if this solution will work for you or not but it worked
for me! :D

All the best!

Re: Stored procedure problem update - help needed

Hi Bob,

Thanks for the in-depth response. The problem is almost resolved.

Robert