ASP Code Help

Discussion in 'ASP General' started by Brian Piotrowski, Jan 12, 2006.

  1. Hi All,

    I'm wondering if a fresh set of eyes can help me with this program. In my
    program, it queries an iSeries database to retrieve a code and count for a
    series of records. When I run the code in ASP with an entered date, I get a
    "no records" message back. However, if I take the SQL string and run it in
    our interpreter, I get back a series of records. Can someone please have a
    look and see if there is any obvious problems?

    Thanks!

    Brian.

    CODE:

    <% KLSA="select krcode,count(krcode) CA from spsl.kls_data where
    concat(char(krdate), concat(' ',char(krtime))) > '" & sdate & " 15:31:00'
    and concat(char(krdate), concat(' ',char(krtime))) < '" & edate & "
    02:00:00' and kplcd='" & request.form("plantcode") & "' group by krcode"
    Set rsKLSA = Server.CreateObject("ADODB.Recordset")
    rsKLSA.Open KLSA, adoConA
    If rsKLSA.eof = True Then
    response.write("No Records")
    Else
    Do While rsKLSA.eof = False
    response.write(rsKLSA("krcode") & ": " & rsKLSA("CA") & "<BR>")
    rsKLSA.movenext
    Loop
    End if
    %>
     
    Brian Piotrowski, Jan 12, 2006
    #1
    1. Advertising

  2. You need to show us the result of
    Response.Write KLSA

    Brian Piotrowski wrote:
    > Hi All,
    >
    > I'm wondering if a fresh set of eyes can help me with this program.
    > In my program, it queries an iSeries database to retrieve a code and
    > count for a series of records. When I run the code in ASP with an
    > entered date, I get a "no records" message back. However, if I take
    > the SQL string and run it in our interpreter, I get back a series of
    > records. Can someone please have a look and see if there is any
    > obvious problems?
    >
    > Thanks!
    >
    > Brian.
    >
    > CODE:
    >
    > <% KLSA="select krcode,count(krcode) CA from spsl.kls_data where
    > concat(char(krdate), concat(' ',char(krtime))) > '" & sdate & "
    > 15:31:00' and concat(char(krdate), concat(' ',char(krtime))) < '" &
    > edate & " 02:00:00' and kplcd='" & request.form("plantcode") & "'
    > group by krcode" Set rsKLSA =
    > Server.CreateObject("ADODB.Recordset") rsKLSA.Open KLSA,
    > adoConA If rsKLSA.eof = True Then
    > response.write("No Records")
    > Else
    > Do While rsKLSA.eof = False
    > response.write(rsKLSA("krcode") & ": " & rsKLSA("CA") &
    > "<BR>") rsKLSA.movenext
    > Loop
    > End if
    > %>


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

  3. Hi Bob,

    Here's a result of KLSA when I run it. This works fine in the SQL
    interpreter, but returns "no records" in my ASP code:

    select krcode,count(krcode) CA from spsl.kls_data where concat(char(krdate),
    concat(' ',char(krtime))) > '10/01/06 15:31:00' and concat(char(krdate),
    concat(' ',char(krtime))) < '11/01/06 02:00:00' and kplcd='2' group by
    krcode

    Here's what is returned in my SQL interpreter from that statement:
    REASON CODE CA
    SPSM 7
    EAH 27
    PQRJ 14
    HCMR 7
    INH 3
    HCMC 3
    UNK 14
    LD 3
    CPI 1
    INV 5
    SA 17
    TR 2

    I'm sure it's something stupidly simple, but my tired eyes cannot see the
    issue.

    Thanks,

    Brian.


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > You need to show us the result of
    > Response.Write KLSA
    >
    > Brian Piotrowski wrote:
    >> Hi All,
    >>
    >> I'm wondering if a fresh set of eyes can help me with this program.
    >> In my program, it queries an iSeries database to retrieve a code and
    >> count for a series of records. When I run the code in ASP with an
    >> entered date, I get a "no records" message back. However, if I take
    >> the SQL string and run it in our interpreter, I get back a series of
    >> records. Can someone please have a look and see if there is any
    >> obvious problems?
    >>
    >> Thanks!
    >>
    >> Brian.
    >>
    >> CODE:
    >>
    >> <% KLSA="select krcode,count(krcode) CA from spsl.kls_data where
    >> concat(char(krdate), concat(' ',char(krtime))) > '" & sdate & "
    >> 15:31:00' and concat(char(krdate), concat(' ',char(krtime))) < '" &
    >> edate & " 02:00:00' and kplcd='" & request.form("plantcode") & "'
    >> group by krcode" Set rsKLSA =
    >> Server.CreateObject("ADODB.Recordset") rsKLSA.Open KLSA,
    >> adoConA If rsKLSA.eof = True Then
    >> response.write("No Records")
    >> Else
    >> Do While rsKLSA.eof = False
    >> response.write(rsKLSA("krcode") & ": " & rsKLSA("CA") &
    >> "<BR>") rsKLSA.movenext
    >> Loop
    >> End if
    >> %>

    >
    > --
    > 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.
    >
    >
     
    Brian Piotrowski, Jan 12, 2006
    #3
  4. '10/01/06 15:31:00'??

    Have you never heard of the y2k bug? :)

    I suspect the OLE DB provider you are using is doing something funky to the
    dates. Does iSeries have a SQL Profiler or Trace utility to allow you to see
    the actual SQL it is executing? If so, you should be firing it up now.

    If not, you might try using some different date formats. I would start with
    the ISO format:
    '2006-01-10 15:31:00'

    if this does not work, then try the following:
    2006-01-10T15:31:00
    20060110 15:31:00

    Not being familiar with how iSeries treats dates, I'm a little curious how
    it is possible for your string comparisons to return the correct results.
    Have you tried dates earlier than 10 in your SQL Interpreter? Something
    like:

    where concat(char(krdate),
    concat(' ',char(krtime))) > '6/01/06 15:31:00' and concat(char(krdate),
    concat(' ',char(krtime))) < '7/01/06 02:00:00'
    Does the query work as expected in that case?

    Bob Barrows
    Brian Piotrowski wrote:
    > Hi Bob,
    >
    > Here's a result of KLSA when I run it. This works fine in the SQL
    > interpreter, but returns "no records" in my ASP code:
    >
    > select krcode,count(krcode) CA from spsl.kls_data where
    > concat(char(krdate), concat(' ',char(krtime))) > '10/01/06 15:31:00'
    > and concat(char(krdate), concat(' ',char(krtime))) < '11/01/06
    > 02:00:00' and kplcd='2' group by krcode
    >
    > Here's what is returned in my SQL interpreter from that statement:
    > REASON CODE CA
    > SPSM 7
    > EAH 27
    > PQRJ 14
    > HCMR 7
    > INH 3
    > HCMC 3
    > UNK 14
    > LD 3
    > CPI 1
    > INV 5
    > SA 17
    > TR 2
    >
    > I'm sure it's something stupidly simple, but my tired eyes cannot see
    > the issue.
    >
    > Thanks,
    >
    > Brian.
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> You need to show us the result of
    >> Response.Write KLSA
    >>
    >> Brian Piotrowski wrote:
    >>> Hi All,
    >>>
    >>> I'm wondering if a fresh set of eyes can help me with this program.
    >>> In my program, it queries an iSeries database to retrieve a code and
    >>> count for a series of records. When I run the code in ASP with an
    >>> entered date, I get a "no records" message back. However, if I take
    >>> the SQL string and run it in our interpreter, I get back a series of
    >>> records. Can someone please have a look and see if there is any
    >>> obvious problems?
    >>>
    >>> Thanks!
    >>>
    >>> Brian.
    >>>
    >>> CODE:
    >>>
    >>> <% KLSA="select krcode,count(krcode) CA from spsl.kls_data
    >>> where concat(char(krdate), concat(' ',char(krtime))) > '" & sdate &
    >>> " 15:31:00' and concat(char(krdate), concat(' ',char(krtime))) < '"
    >>> & edate & " 02:00:00' and kplcd='" & request.form("plantcode") & "'
    >>> group by krcode" Set rsKLSA =
    >>> Server.CreateObject("ADODB.Recordset") rsKLSA.Open KLSA,
    >>> adoConA If rsKLSA.eof = True Then
    >>> response.write("No Records")
    >>> Else
    >>> Do While rsKLSA.eof = False
    >>> response.write(rsKLSA("krcode") & ": " & rsKLSA("CA") &
    >>> "<BR>") rsKLSA.movenext
    >>> Loop
    >>> End if
    >>> %>

    >>
    >> --
    >> 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], Jan 12, 2006
    #4
  5. Brian Piotrowski

    Guest

    , Jan 16, 2006
    #5
    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. =?Utf-8?B?Q2FybG8gTWFyY2hlc29uaQ==?=

    Fire Code behind code AND Javascript code associated to a Button Click Event

    =?Utf-8?B?Q2FybG8gTWFyY2hlc29uaQ==?=, Feb 10, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    21,298
    =?Utf-8?B?Q2FybG8gTWFyY2hlc29uaQ==?=
    Feb 11, 2004
  2. Tee
    Replies:
    1
    Views:
    4,150
    Raterus
    Jun 24, 2004
  3. Phil Winstanley [Microsoft MVP ASP.NET]

    Re: help with repeater, how to code it in code-behind page

    Phil Winstanley [Microsoft MVP ASP.NET], Jun 24, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    478
    Ireney Berezniak
    Jun 25, 2004
  4. keithb
    Replies:
    1
    Views:
    940
    Bruce Barker
    Mar 29, 2006
  5. tone
    Replies:
    4
    Views:
    461
    Hywel Jenkins
    Nov 19, 2003
Loading...

Share This Page