Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP Code Help

Reply
Thread Tools

ASP Code Help

 
 
Brian Piotrowski
Guest
Posts: n/a
 
      01-12-2006
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
%>


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


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



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


 
Reply With Quote
 
paul@bullschmidt.com
Guest
Posts: n/a
 
      01-16-2006
And for related links:

It suggests yyyy-mm-dd for Access and yyyymmdd for SQL Server.
How do I delimit/format dates for database entry?
http://www.aspfaq.com/show.asp?id=2023

It suggests yyyymmdd for SQL Server.
Could I get a little help with dates?
http://www.aspfaq.com/show.asp?id=2040

Best regards,
-Paul
www.Bullschmidt.com - Freelance Web and Database Developer
www.Bullschmidt.com/DevTip.asp - ASP Design Tips

 
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
what is the difference between code inside a <script> tag and code in the code-behind file? keithb ASP .Net 1 03-29-2006 01:00 AM
Call a DLL LIB function in ASP.NET (Code Behind) or VB.NET (The same code is working fine in VB 6.0) Peri ASP .Net 2 07-20-2005 03:13 AM
server side code access the text of <asp:label> changed by client-side javascript code? nick ASP .Net 3 12-15-2004 06:26 PM
HTML code warnings in asp.net html code view Craig Kenisston ASP .Net 3 10-07-2004 04:05 PM
Fire Code behind code AND Javascript code associated to a Button Click Event =?Utf-8?B?Q2FybG8gTWFyY2hlc29uaQ==?= ASP .Net 4 02-11-2004 07:31 AM



Advertisments