ASP Code Help

B

Brian Piotrowski

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
%>
 
B

Brian Piotrowski

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

Bob Barrows [MVP]

'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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top