Count Number of Records between Start and End date from Form

[specialist]

Greetings-

I am having a tough time writing a stored procedure which will return results between a Start Date and End Date from a form. Simple Question:

How can I do this?

I have a stored prodcedure which returns the results of the column in question, but it returns ALL of the results. How can I add the StartDate and EndDate paramaters in the Stored procedure to make this work?

Any advide on this matter would be warmly welcomed.

Many thanks,

Michael

Procedure:

CREATE PROCEDURE sp_food
AS
SELECT COUNT (Entry_Number) AS CAT3_4
FROM tblInfo
WHERE Area = ("S") AND Category = ("3") AND Type = ("Food") AND Complete = ("1")
GO

-------

Would like to add these parameters:

StartDate
EndDate

 

[gradley]

See if this works..

CREATE PROCEDURE sp_food
 @dteStartDate datetime,
 @dteEndDate datetime
AS
SELECT COUNT (Entry_Number) AS CAT3_4
FROM tblInfo
WHERE Area = ("S") AND Category = ("3") AND Type = ("Food")  AND Complete = ("1") 
and tabledate between @dteStartDate and @dteEndDate
GO

 

[specialist]

greetings-

Thank you for the reply. Unfortunatly, this does not seem to work for one reason or another. I am able to get results from another stored procedure, which does not require dates. This makes me think two thinkgs:

1. The permissions are OK
2. There is a prodlem with the ASP code

I redesignate the startdate and enddate variables from the form to the results page as so:
<%
'*** Initialize variables from test_new2.asp test page

StartDate = cDate(Request.Form("StartDate3"))
EndDate = cDate(Request.Form("EndDate3"))
%>


The Stored Procedure is :

CREATE PROCEDURE sp_food
@StartDate datetime,
@EndDate datetime
AS
SELECT COUNT (Entry_Number) AS CAT3_4
FROM tblInfo
WHERE Area = ("S") AND Category = ("3") AND Type = ("Food") AND Prep between @StartDate and @EndDate AND Complete = ("1")

GO

The ASP code is this:

<%
Dim Recordset1__StartDate
Recordset1__StartDate = "10"
If (Request("StartDate") <> "") Then
Recordset1__StartDate = Request("StartDate")
End If
%>
<%
Dim Recordset1__EndDate
Recordset1__EndDate = "10"
If (Request("EndDate") <> "") Then
Recordset1__EndDate = Request("EndDate")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_good_STRING
Recordset1.Source = "{call dbo.sp_food(" + Replace(Recordset1__StartDate, "'", "''") + "," + Replace(Recordset1__EndDate, "'", "''") + ")}"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim Recordset1_total
Dim Recordset1_first
Dim Recordset1_last

' set the record count
Recordset1_total = Recordset1.RecordCount

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

' set the first and last displayed record
Recordset1_first = 1
Recordset1_last = Recordset1_first + Recordset1_numRows - 1

' if we have the correct record count, check the other stats
If (Recordset1_total <> -1) Then
If (Recordset1_first > Recordset1_total) Then
Recordset1_first = Recordset1_total
End If
If (Recordset1_last > Recordset1_total) Then
Recordset1_last = Recordset1_total
End If
If (Recordset1_numRows > Recordset1_total) Then
Recordset1_numRows = Recordset1_total
End If
End If
%>

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

If (Recordset1_total = -1) Then

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

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

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

' set the first and last displayed record
Recordset1_first = 1
Recordset1_last = Recordset1_first + Recordset1_numRows - 1

If (Recordset1_first > Recordset1_total) Then
Recordset1_first = Recordset1_total
End If
If (Recordset1_last > Recordset1_total) Then
Recordset1_last = Recordset1_total
End If

End If
%>

I seem to have the parameters set in the query, but the ASP code is not firing. Perhaps I am not designating the variable correctly in the ASP code?


Any advice as to where, how, why, and anything else would be appreciated. Many thanks for any direction (especially the right one!

-Michael

 

[gradley]

When calling the stored procedure, try passing the parameters delimited by commas. Do not wrap in parenthesis since this is a procedure not a function. Also try the sp in Query Analyzer to see if it works there first.

EX: "Exec dbo.spfood @StartDate, @EndDate"

Also if you have problems with passing dates as type datetime (sometimes this is a problem when passing from an ASP page), then try passing as a varchar(25) and convert to a datetime in the stored procedure. Just make sure the values you are passing are valid dates.

 

[gradley]

One last note. The line below:

Recordset1.Source = "{call dbo.sp_food(" + Replace(Recordset1__StartDate, "'", "''") + "," + Replace(Recordset1__EndDate, "'", "''") + ")}"

That sets the recordset source, probably does not need brackets around it and should use "exec" install of "call".

 

[gradley]

Sorry -- that's "exec" instead of "call