469,282 Members | 2,045 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

ASP / SQL Problem

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>
Feb 2 '06 #1
8 1251
dthmtlgod wrote:
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=e...TNGP12.phx.gbl

--
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.
Feb 2 '06 #2
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:e6**************@TK2MSFTNGP12.phx.gbl...
dthmtlgod wrote:
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=e...TNGP12.phx.gbl
--
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.

Feb 2 '06 #3
Do you have a "SET NOCOUNT ON" statement in your stored procedure?
dthmtlgod wrote:
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:e6**************@TK2MSFTNGP12.phx.gbl...
dthmtlgod wrote:
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=e...TNGP12.phx.gbl

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


--
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.
Feb 2 '06 #4
Yes, I have it set to ON
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ot*************@TK2MSFTNGP12.phx.gbl...
Do you have a "SET NOCOUNT ON" statement in your stored procedure?
dthmtlgod wrote:
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:e6**************@TK2MSFTNGP12.phx.gbl...
dthmtlgod wrote:
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=e...TNGP12.phx.gbl

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


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

Feb 2 '06 #5
dthmtlgod wrote:
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
--
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.
Feb 2 '06 #6
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:OE**************@TK2MSFTNGP12.phx.gbl...
dthmtlgod wrote:
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
--
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.

Feb 2 '06 #7
dthmtlgod wrote:
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.
--
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.
Feb 2 '06 #8
dthmtlgod wrote:
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
--
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.
Feb 2 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Kostatus | last post: by
117 posts views Thread by Peter Olcott | last post: by
18 posts views Thread by Ian Stanley | last post: by
28 posts views Thread by Jon Davis | last post: by
6 posts views Thread by Ammar | last post: by
2 posts views Thread by Mike Collins | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.