Hi 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.

<!--#include file="../Connections/MYCONNECTION STRING VALUE.asp" -->

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

[quoted text, click to view]

Nothing to do with your problem, but:
http://www.aspfaq.com/show.asp?id=2126


[quoted text, click to view]

How have you verified this? Have you used response.write to write it to
Response?
[quoted text, click to view]

Don't do this! Always create an explicit Connection object and use it to
open subsequent objects. Looks like another Dreamweaver travesty ...

[quoted text, click to view]

Your procedure needs two parameter arguments: @count and @zipcode. This
statement only supplies one. Prove it with:

Response.Write Sitters15.Source
Response.End

Also, the syntax is not correct: you do not surround the parameter values
with parentheses in t-sql.
I don't see where you tried to use the @count output value. Are you sure you
need that? You will not get an output value returned without using an
explicit Command object. Here is a link about executing stored procedures
from vbscript in asp pages:
http://tinyurl.com/jyy0


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.