Stored procedure problem - update help needed

microsoft.public.sqlserver.programming

19 Jan 2006 6:55 PM

ROBinBRAMPTON

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
%>

19 Jan 2006 8:33 PM

Jim Underwood

You probably want to handle this in your ASP code, not the stored procedure.
The stored procedure is correctly returning an empty recordset. Your ASP
code is incorrectly trying to access this non-existent recordset.

Before you perform any functions on the recordset, check for
Sitters15_2.EOF, or check the state of Sitters15_2. It has been a long time
since I used classic ASP so I don't recall the exact syntax, but I think the
following worked.

if not Sitters15_2.EOF and not Sitters15_2.BOF then
(access your recordset now)
else
(display a user friendly message that no data was returned)
end if

This question should really be posted on a classic ASP news group, not a SQL
server newsgroup.

When you have a query or a stored procedure that is not doing what you want
when you run it in query analyzer, that is when you should post here. When
you get an error on your web page, you should be looking at your web code
first. Of course, you should be running all your SQL through Query Analyzer
before using it in your application, that way you only have to troubleshoot
one piece at a time.

Good luck.

Show quote

"ROBinBRAMPTON" <rkirkw***@sympatico.ca> wrote in message
news:1137696940.747150.8520@z14g2000cwz.googlegroups.com...
> 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
> %>
>

20 Jan 2006 3:01 PM

ROBinBRAMPTON

Hi Jim,

Thanks for your reply!

I just about have it resolved.

Robert