bad SP, or calling a stored procedure problem
am 18.01.2006 20:24:49 von rkirkwoodHi Everyone,
Can any one spot the error is this procedure, or the ASP code to call
it below. The procedure consists of two parts.
1) Seach function to extract all zipcodes from a zip code database,
which fall within a 15 miles radius from a predefined zip code
(variable @zipcode)
2) Then compare the list to a customers table, for matching records to
provide a COUNT value. eg There are 25 members within 15 miles of your
zipcode area.
The error message i'm receiving when calling the procedure is...
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'psFindSitters15miles' expects parameter '@zipcode', which was not
supplied.
/Parents/Sign_up7.asp, line 21
Line 21 refers to this code
Sitters15.Open()
i'm absolutely certain a parameter is supplied by a form post.
Following is the stored procedure in question
CREATE PROCEDURE dbo.psFindSitters15miles
(@count int OUTPUT,
@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 @count=count(*) 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))) )
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))) )
SET NOCOUNT OFF
return;
end;
GO
And here the code for how its being called on my ASP page.
<%
Dim Sitters15__zipcode
Sitters15__zipcode = "90210"
If (Request("zipcode") <> "") Then
Sitters15__zipcode = Request("zipcode")
End If
%>
<%
Dim Sitters15
Dim Sitters15_numRows
Set Sitters15 = Server.CreateObject("ADODB.Recordset")
Sitters15.ActiveConnection = MM_Phoneababysitter_STRING
Sitters15.Source = "{call dbo.psFindSitters15miles('" +
Replace(Sitters15__zipcode, "'", "''") + "')}"
Sitters15.CursorType = 0
Sitters15.CursorLocation = 2
Sitters15.LockType = 3
Sitters15.Open()
Sitters15_numRows = 0
%>
Any help would be greatly appreciated!!!
Thanks, Robert