Results between two dates, ASP, SQL, Dates

[specialist]

greetings-

Hello there!! Thanks for looking at my post

Here's my dilemma:

I am trying to display database (SQL2K) results based on a stored procedure. The results to be displayed, should only calculate data between a start date and an End Date. These two dates are set by our friend "End User" via a form. Alas, I am unable to get results!

However, I was able to get results from another stored procedure which did not require date parameters, so I believe the SQL permissions are all set. My best guess is it has something to do with the ASP page itself.

Hopefully, someone can help as I have been looking at this for quite some time now and have a horrible headache.


Here are the details:


<%
'*** Initialize variables from test_new2.asp test page form

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

 

[Genimuse]

I've never called a stored procedure that way (I've only used the create parameters method), and I'm not sure why you're using a stored procedure instead of a simple select (since that's all the procedure does), but Access likes # signs around dates, so you might need to use:

Recordset1.Source = "{call dbo.sp_food([COLOR=blue][b]#[/b][/color]" + Replace(Recordset1__StartDate, "'", "''") + "[COLOR=blue][b]#[/b][/color],[COLOR=blue][b]#[/b][/color]" + Replace(Recordset1__EndDate, "'", "''") + "[COLOR=blue][b]#[/b][/color])}"

 

[rac2]

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

The Replace() puzzles me. When I get dates out of a form, and they have been entered nicely, I expect them to be strings that look like this -

11/23/2004

So there is no need to replace single quotes with double quotes.

There is however the need to surround that string with single quotes in order for SQL Server to treat it as a string. So -

"{call dbo.sp_food( '" + Recordset1__StartDate + "', '" + Recordset1__EndDate + "' )}"

Let me know whether that helps.

 

[Genimuse]

Missed that it was SQL Server, yup, single quotes are good, too.

However, since the dates are coming from a user via a form, the Replace is essential to avoid SQL injection. Definitely leave it or do it prior to the query.

 

[Genimuse]

Erm... actually, in retrospect, I think that stored procedure will protect you from SQL injection (it will fail because of the datetime declaration). Still, good practice (and I'm not certain).

 

[Genimuse]

Ok, one last clarification: much better SQL injection protection comes from verifying the contents, a la If Not IsDate(thing) Then 'do error thing. It prevents other SQL injection like multiple commands, unions, etc.

 

[SatishPutcha]

Well, I have not worked with SQL SP much but your SQL Select might need a little modification

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")

I have added brackets to the SQL for the date condition. I do this in Access queries.

Also try to send the date as dd-mmm-yyyy(e.g. 01-Jan-2004) in the SQL.

Regards
Satish