ASP / SQL Problem

[dthmtlgod]

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>

 

[Bob Barrows [MVP]]

I think this is a ASP problem. Here is my SQL procedures my the SQL
server

Before I start reading through all this, I think you have a stored
procedure, correct?
Here is an easy way to execute a stored procedure via an ADO connection
object:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=OVlfw#[email protected]

 

[dthmtlgod]

It does execute a stored procedure, you are correct. The problem is it
doesn't return all the records it should in the ASP page, but it works fine
when the stored procedure is executed through the Query Analyzer.

There are four possible scenarios to return data. It only works on a couple
of them.

 

[Bob Barrows [MVP]]

Do you have a "SET NOCOUNT ON" statement in your stored procedure?

 

[dthmtlgod]

Yes, I have it set to ON

Do you have a "SET NOCOUNT ON" statement in your stored procedure?

http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=OVlfw#[email protected]
--
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.

 

[Bob Barrows [MVP]]

I think this is a ASP problem. Here is my SQL procedures my the SQL
server

OK, let's go back to this:

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

I take it these reflect what the stored procedure (it IS a single stored
procedure, isn't it?) is SUPPOSED to do.
You don't seem to provide any information as to what it is ACTUALLY doing
(or failing to do).

I suggest you use SQL Profiler to trace what exactly is occurring. You can
add the Stored Procedure Stmt_Starting and Stmt_Completed events to see the
statements as they are executing. Compare what is happening when you execute
the procedure from QA with what is happening when you 3execute it from ASP.
Bob Barrows
PS. I assume you are using a nonambiguous format to pass date values to your
procedure?
http://www.aspfaq.com/show.asp?id=2040

 

[dthmtlgod]

Thanks I will give it a try.

But here is what is happening. The pass date values are correct.

Four scenarios, all work in QA, but here are the results in ASP

One
From Date: blank
Thru Date: blank
Results: No records
Expected Results: Return all rows

Two
From Date: 12/01/2005
Thru Date: blank
Results: No records
Expected Results: All rows with a date greater than 12/01/05 should be
return

Three
From Date: 12/01/2005
Thru Date: 12/10/2005
Results: Returns the correct number of records
Expected Results: Correct

Four
From Date: blank
Thru Date: 12/10/2005
Results: Returns the correct number of records
Expected Results: Correct

It is weird that it returns results for scenario Four and not Two.

 

[Bob Barrows [MVP]]

Thanks I will give it a try.

But here is what is happening. The pass date values are correct.

Four scenarios, all work in QA, but here are the results in ASP

One
From Date: blank
Thru Date: blank
Results: No records
Expected Results: Return all rows

Two
From Date: 12/01/2005
Thru Date: blank
Results: No records
Expected Results: All rows with a date greater than 12/01/05 should
be return

Three
From Date: 12/01/2005
Thru Date: 12/10/2005
Results: Returns the correct number of records
Expected Results: Correct

Four
From Date: blank
Thru Date: 12/10/2005
Results: Returns the correct number of records
Expected Results: Correct

It is weird that it returns results for scenario Four and not Two.

Again, it seems to me that you are not passing your dates in a non-ambiguous
format (YYYYMMDD). For example, I have no idea 12/01/05 means 19050112
(12-Jan 1905) or 20051201 (1-Dec 2005). How do you expect SQL Server to
know? It is going to assume you are passing dates in m/d/yyyy format unless
you use a nonambiguous format like YYYYMMDD.

 

[Bob Barrows [MVP]]

Thanks I will give it a try.

But here is what is happening. The pass date values are correct.

Four scenarios, all work in QA, but here are the results in ASP

One
From Date: blank
Thru Date: blank
Results: No records
Expected Results: Return all rows

Oh, and I just noticed this. I need to see your CREATE PROCEDURE statement
where you declare your parameters, and I need to know the datatypes of the
columns involved in the WHERE clauses.

How are you passing "blank" dates? Are you passing empty strings? Or are you
passing Nulls? Your EXISTS statement is testing for Nulls, not empty strings

Bob Barrows