Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP / SQL Problem

Reply
Thread Tools

ASP / SQL Problem

 
 
dthmtlgod
Guest
Posts: n/a
 
      02-02-2006
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>


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-02-2006
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.


 
Reply With Quote
 
 
 
 
dthmtlgod
Guest
Posts: n/a
 
      02-02-2006
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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-02-2006
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]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.


 
Reply With Quote
 
dthmtlgod
Guest
Posts: n/a
 
      02-02-2006
Yes, I have it set to ON


"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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]" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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.
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-02-2006
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.


 
Reply With Quote
 
dthmtlgod
Guest
Posts: n/a
 
      02-02-2006
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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-02-2006
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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-02-2006
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
MS Access SQL > ASP SQL problem.... david@scene-double.co.uk ASP General 10 01-06-2005 12:23 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server Belinda ASP General 4 06-11-2004 12:16 PM
ASP SQL - using variables in SQL select screen Ed Garcia ASP General 4 08-07-2003 07:41 PM



Advertisments