I think this is a ASP problem. Here is my SQL procedures my the SQL server
I can run these in SQL Query Analyzer and get the correct results. However,
when I try to run them through my ASP page, the first two do not return any
results. I can't figure out why.
SQL Statement 1. Returns all records
SQL Statement 2. Returns records after begDate
SQL Statement 3. Returns records after begDate and before endDate
SQL Statement 4. Returns records before endDate
IF EXISTS (SELECT * FROM LITDB_tblCalendar WHERE @begDate is NULL and
@endDate is NULL)
BEGIN
SELECT HrngID, AdjName, DispDueDate, ClaimNumber, Juris, HearingDate,
HearingType,
ClaimantName, Location, HearingTime, HearingPart, Counsel, DispDone,
Canceled, WasRescheduled
FROM LITDB_tblCalendar
WHERE DispDone = 0 or DispDone IS NULL
ORDER BY DispDueDate DESC
END
IF EXISTS (SELECT * FROM LITDB_tblCalendar WHERE @begDate is NOT NULL and
@endDate is NULL)
BEGIN
SELECT HrngID, AdjName, DispDueDate, ClaimNumber, Juris, HearingDate,
HearingType,
ClaimantName, Location, HearingTime, Counsel, DispDone, Canceled,
WasRescheduled
FROM LITDB_tblCalendar
WHERE DispDueDate >= @begDate and (DispDone = 0 or DispDone IS NULL)
ORDER BY DispDueDate DESC
END
IF EXISTS (SELECT * FROM LITDB_tblCalendar WHERE @begDate is NOT NULL and
@endDate is NOT NULL)
BEGIN
SELECT HrngID, AdjName, DispDueDate, ClaimNumber, Juris, HearingDate,
HearingType,
ClaimantName, Location, HearingTime, Counsel, DispDone, Canceled,
WasRescheduled
FROM LITDB_tblCalendar
WHERE DispDueDate >= @begDate and DispDueDate <= @endDate and (DispDone =
0 or DispDone IS NULL)
ORDER BY DispDueDate DESC
END
IF EXISTS (SELECT * FROM LITDB_tblCalendar WHERE @begDate is NULL and
@endDate is NOT NULL)
BEGIN
SELECT HrngID, AdjName, DispDueDate, ClaimNumber, Juris, HearingDate,
HearingType,
ClaimantName, Location, HearingTime, Counsel, DispDone, Canceled,
WasRescheduled
FROM LITDB_tblCalendar
WHERE DispDueDate <= @endDate and (DispDone = 0 or DispDone IS NULL)
ORDER BY DispDueDate DESC
END
Here is the code in ASP that corresponds
<%@ LANGUAGE="VBSCRIPT" %>
<%
FromDate = Request.Form("FromDate")
ThruDate = Request.Form("ThruDate")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=sqloledb;" & _
"Data Source=SQLF;" & _
"Initial Catalog=NYSA;" & _
"User Id=APP_NYSA;" & _
"Password=$NYS"
strSQL = "EXEC dbo.litDispositionsDue" & _
" @FromDate = '" & FromDate & "', " & _
" @ThruDate = '" & ThruDate & "'"
Set rs = Conn.Execute (strSQL)
%>
<form action="lstDispositionsDue.asp" method="post">
<table align=center width="85%">
<%
if not rs.eof then
rsArray = rs.GetRows()
nr = UBound(rsArray, 2) + 1
With RS
.MoveFirst
do while not .eof
%>
<tr>
<td align=center width=50>
<%
response.write "<A HREF='EditHearingNotice.asp?id=" & rs("HrngID") &
"'>"%><% =rs("HrngID") %></a></td>
<td align=center width=100><% = rs("DispDueDate") %></td>
<td align=center width=100><% = rs("ClaimNumber") %></td>
<td align=center width=200><% = rs("AdjName") %></td>
<td align=center width=100><% = rs("HearingDate") %></td>
<td align=center width=50><% = rs("Juris") %></td>
<td align=center width=175><% = rs("Location") %></td>
<td align=center width=300><% = ucase(rs("Counsel")) %></td>
</tr>
<%
.movenext
loop
end with
end if
%>
</table>