ASP / SQL Problem

Discussion in 'ASP General' started by dthmtlgod, Feb 2, 2006.

  1. dthmtlgod

    dthmtlgod Guest

    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>
     
    dthmtlgod, Feb 2, 2006
    #1
    1. Advertising

  2. 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=en&lr=&c2coff=1&selm=OVlfw#

    --
    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], Feb 2, 2006
    #2
    1. Advertising

  3. dthmtlgod

    dthmtlgod Guest

    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]" <> wrote in message
    news:...
    > 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=en&lr=&c2coff=1&selm=OVlfw#
    >
    > --
    > 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, Feb 2, 2006
    #3
  4. 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]" <> wrote in message
    > news:...
    >> 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=en&lr=&c2coff=1&selm=OVlfw#
    >>
    >> --
    >> 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.
     
    Bob Barrows [MVP], Feb 2, 2006
    #4
  5. dthmtlgod

    dthmtlgod Guest

    Yes, I have it set to ON


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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]" <> wrote in message
    > > news:...
    > >> 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=en&lr=&c2coff=1&selm=OVlfw#
    > >>
    > >> --
    > >> 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, Feb 2, 2006
    #5
  6. 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.
     
    Bob Barrows [MVP], Feb 2, 2006
    #6
  7. dthmtlgod

    dthmtlgod Guest

    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]" <> wrote in message
    news:...
    > 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, Feb 2, 2006
    #7
  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
    >
    > 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.
     
    Bob Barrows [MVP], Feb 2, 2006
    #8
  9. 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.
     
    Bob Barrows [MVP], Feb 2, 2006
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ecoolone
    Replies:
    0
    Views:
    782
    ecoolone
    Jan 3, 2008
  2. Ed Garcia
    Replies:
    4
    Views:
    193
    Guinness Mann
    Aug 7, 2003
  3. Belinda
    Replies:
    4
    Views:
    375
    Bob Barrows [MVP]
    Jun 11, 2004
  4. weiwei

    asp and sql statement in sql server db

    weiwei, Sep 22, 2004, in forum: ASP General
    Replies:
    3
    Views:
    211
    Jeff Cochran
    Sep 22, 2004
  5. Replies:
    10
    Views:
    282
    Bob Barrows [MVP]
    Jan 6, 2005
Loading...

Share This Page