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> 8 1325
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.
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.
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.
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.
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.
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.
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Kostatus |
last post by:
I have a virtual function in a base class, which is then overwritten by a
function of the same name in a publically derived class. When I call the
function using a pointer to the derived class...
|
by: Peter Olcott |
last post by:
www.halting-problem.com
|
by: Ian Stanley |
last post by:
Hi,
Continuing my strcat segmentation fault posting-
I have a problem which occurs when appending two sting literals using
strcat.
I have tried to fix it by writing my own function that does the...
|
by: Jon Davis |
last post by:
If I have a class with a virtual method, and a child class that overrides
the virtual method, and then I create an instance of the child class AS A
base class...
BaseClass bc = new ChildClass();...
|
by: Ammar |
last post by:
Dear All,
I'm facing a small problem.
I have a portal web site, that contains articles, for each article, the end
user can send a comment about the article.
The problem is:
I the comment length...
|
by: Dany |
last post by:
Our web service was working fine until we installed .net Framework 1.1 service pack 1. Uninstalling SP1 is not an option because our largest customer says service packs marked as "critical" by...
|
by: Mike Collins |
last post by:
I cannot get the correct drop down list value from a drop down I have on my
web form. I get the initial value that was loaded in the list.
It was asked by someone else what the autopostback was...
|
by: =?Utf-8?B?am8uZWw=?= |
last post by:
Hello All,
I am developing an Input Methop (IM) for PocketPC / Windows Mobile (PPC/WM).
On some
devices the IM will not start. The IM appears in the IM-List but when it is
selected from the...
|
by: sherifbk |
last post by:
Problem description
==============
- I have 4 clients and 1 server (SQL server)
- 3 clients are Monitoring console 1 client is operation console
- Monitoring console collects some data from...
|
by: AceKnocks |
last post by:
I am working on a framework design problem in which I have to design a C++ based framework capable of solving three puzzles for now but actually it should work with a general puzzle of any kind and I...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |