Help with messy loop?!

# Thread: Help with messy loop?!

1. Senior Member
Join Date
Dec 1969
Posts
310

## Help with messy loop?!

&#060;%<BR>Set rs = conn.Execute(sql)<BR><BR>tm = 0<BR><BR>For tm = 0 To 24*60-1 Step 30 &#039; every 30 minutes<BR> curTime = TimeSerial( 0, tm, 0 ) &#039; convert to HH:MM:SS<BR> nextTime = TimeSerial( 0, tm+29, 59 ) &#039; 29 minutes, 59 seconds later<BR> show = True<BR> If Not RS.EOF Then<BR> recTime = RS("fld_time")<BR> Do While recTime &#062;= curTime AND recTime &#060; nextTime<BR> &#039; don&#039;t show an option for this time period!<BR> show = False<BR> RS.MoveNext<BR> recTime = RS("fld_time")<BR> If RS.EOF Then Exit Do<BR> Loop<BR> End If<BR> If show Then<BR> showTime = FormatDateTime( curTime, vbLongTime )<BR> showTime = Replace( showTime, ":00 ", " " )<BR> Response.Write "&#060;OPTION value=""&showTime&""&#062;" & showTime & "&#060;/OPTION&#062; & vbNewLine<BR> End If<BR>Next<BR>%&#062;<BR><BR>Hi,<BR><BR>yesterday Bill W helped me out with thread:<BR>http://www.aspmessageboard.com/forum/asp.asp?M=668046&T=668046&F=20&P=1<BR><BR>and came up with the code above to help with the scenario (and thanks for that too!)<BR><BR>I&#039;ve been working with the code, but cannot for some reason get it to work out.<BR><BR>I have response.written the second "recTime = RS("fld_time")" which occurs withing the DO WHILE Loop, and even for a recordset which contains 5 records, the value is always the same, since it is taking the recTime from the first "recTime = RS("fld_time")" which occurs just after the &#039;If Not RS.EOF&#039; line.<BR><BR>Therefore even if e.g I have the following:<BR><BR>curTime: 00:30:00<BR>nextTime: 00:59:59<BR>recTime: 00:30<BR><BR>the line <BR><BR>Do While recTime &#062;= curTime AND recTime &#060; nextTime<BR><BR>is not satisfied, even though recTime is clearly = curTime, and is also clearly &#060; nextTime<BR><BR>Therefore Show does not get set to False, and all the slots are outputted to the SELECT field.<BR><BR>Any ideas?<BR><BR>Thanks<BR><BR>Jim<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Might be a data type problem...

Since you say <BR> recTime: 00:30<BR>that does *NOT* look like a valid time value! In VBS, if you simply do<BR> Response.Write someTimeValue<BR>you *WILL* get<BR> 00:30:00 AM<BR>[no AM if you are in a country that uses a 24-hour clock].<BR><BR>SO...<BR><BR>What kind of data field is "fld_time" in your DB??? I&#039;d have to guess that maybe it&#039;s (INCORRECTLY!) just a text field?<BR><BR>I&#039;d suggest changing it to a datetime field.<BR><BR>BUT...<BR><BR>In the meantime, if it is a text field, you should be able to simply change the line<BR> recTime = RS("fld_time")<BR>to<BR> recTime = CDate( RS("fld_time") )<BR>and get it to work.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
310

## RE: Might be a data type problem...

Hi Bill,<BR><BR>fld_time is a &#039;time&#039; field in the MySQL db.<BR><BR>I&#039;ll try your suggestion though. Thanks again for being such a big help.<BR><BR>Jim

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ahhh...MySQL...

...indeed might be a translation problem in the type, then.<BR><BR>If you want to play it absolutely safe, try<BR> TimeValue( CDate( RS("fld_time") )<BR><BR>If that doesn&#039;t work, then go the ugly route:<BR> TimeValue( CDate( CStr( RS("fld_time") ) )<BR><BR>That converts the value from MySQL first to a string (same as you get if you Response.Write it), then to a datetime value, and finally TimeValue strips off the date to leave only the time.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Hah! Another thought!

It might be a data conversion problem.<BR><BR>Despite the fact that the time *appears* as "00:30:00" from MySQL, since internal to VBS all time values are *FRACTIONAL* floating point numbers.<BR><BR>So the MySQL value that is *ROUNDED* to appear as<BR> 00:30:00<BR>might *actually* be<BR> 00:29:59.99997<BR><BR>SO...it might be safer to allow a tiny bit of "slop"?<BR><BR>So we maybe could do:<BR> curTime = TimeSerial( 0, tm, -1 ) &#039; convert to HH:MM:SS<BR><BR>Then curTime will be<BR> 00:29:59<BR>and now if the time that shows up from MySQL is actually<BR> 00:29:59.99997<BR>the &#062;= test *will* work.<BR><BR>Hmmm??<BR><BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
310

## RE: Hah! Another thought!

Hi,<BR><BR>thanks - I have tried out your suggestion, but it still prints all the slots, and the options look like this:<BR><BR>&#060;OPTION value=00:00:01&#062;00:00:01&#060;/OPTION&#062;<BR>&#060;OPTION value=00:29:59&#062;00:29:59&#060;/OPTION&#062;<BR>&#060;OPTION value=00:59:59&#062;00:59:59&#060;/OPTION&#062;<BR>&#060;OPTION value=01:29:59&#062;01:29:59&#060;/OPTION&#062;<BR>&#060;OPTION value=01:59:59&#062;01:59:59&#060;/OPTION&#062;<BR><BR>Also! I tried out your suggestion re. the MySQL conversion, but I got the &#039;exception occurred&#039; error for both types of conversion.<BR><BR>I then messed about for a while trying to use the method you used in my query last week, where you converted the 48 half hour slots into 48 periods, starting from 0 going through to 47, and trying to convert the db time into a period, and comparing that with the period number in the loop, but made a horrible mess of that one.<BR><BR>Thanks<BR><BR>Jim

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well, nuts! Tougher, but...

Let&#039;s try another approach.<BR><BR>Get rid of the -1 in the TimeSerial.<BR><BR>Then add some debug:<BR><BR>&#060;%<BR>...<BR>rectime = RS("fld_time")<BR>Response.Write "TEST: " & Hour(rectime) & ":" & Minute(rectime) & ":" & Second(rectime) & "&#060;BR/&#062;"<BR>Response.Write "AS FLOAT: " & CDBL(rectime) & "&#060;P&#062;"<BR>Response.Write "CurTime as FLOAT: " & CDBL(curtime) & "&#060;HR&#062;"<BR><BR>You can do that ahead of the loop, or even put Response.End right after those lines. We don&#039;t care about the loop output until we figure out what is going on.<BR><BR><BR><BR>...<BR>

8. Senior Member
Join Date
Dec 1969
Posts
310

## here is the output....

I did<BR><BR>&#060;%<BR>tm = 0<BR><BR>For tm = 0 To 24*60-1 Step 30 &#039; every 30 minutes<BR> &#039;curTime = TimeSerial( 0, tm, 0 ) &#039; convert to HH:MM:SS<BR> curTime = TimeSerial( 0, tm, 0 )<BR> nextTime = TimeSerial( 0, tm+29, 59 ) &#039; 29 minutes, 59 seconds later<BR> show = True<BR> If Not RS.EOF Then<BR> recTime = RS("fld_time")<BR><BR> &#039;BW----------------<BR> Response.Write "TEST: " & Hour(rectime) & ":" & Minute(rectime) & ":" & Second(rectime) & "&#060;BR/&#062;"<BR> Response.Write "AS FLOAT: " & CDBL(rectime) & "&#060;BR/&#062;"<BR> Response.Write "CurTime as FLOAT: " & CDBL(curtime) & "&#060;HR&#062;"<BR> &#039;BW----------------<BR><BR> Do While recTime &#062;= curTime AND recTime &#060; nextTime<BR> &#039; don&#039;t show an option for this time period!<BR> show = False<BR> RS.MoveNext<BR> recTime = RS("fld_time")<BR> If RS.EOF Then Exit Do<BR> Loop<BR> End If<BR> If show Then<BR> showTime = FormatDateTime( curTime, vbLongTime )<BR> showTime = Replace( showTime, ":00 ", " " )<BR> Response.Write "&#060;OPTION value="&showTime&"&#062;" & showTime & "&#060;/OPTION&#062;" & vbNewLine & vbNewLine<BR> End If<BR>Next<BR>%&#062;<BR><BR>and got:<BR><BR> TEST: 0:0:0<BR>AS FLOAT: 38021<BR>CurTime as FLOAT: 0<BR>--------------------------------<BR>00:00:00 TEST: 0:0:0<BR>AS FLOAT: 38021<BR>CurTime as FLOAT: 2.08333333333333E-02<BR>--------------------------------<BR>00:30:00 TEST: 0:0:0<BR>AS FLOAT: 38021<BR>CurTime as FLOAT: 4.16666666666667E-02<BR>--------------------------------<BR>01:00:00 TEST: 0:0:0<BR>AS FLOAT: 38021<BR>CurTime as FLOAT: 0.0625<BR>--------------------------------<BR>01:30:00 TEST: 0:0:0<BR>AS FLOAT: 38021<BR>CurTime as FLOAT: 8.33333333333333E-02<BR><BR>interesting that the top line has nothing printed in print of &#039;TEST&#039;, though I don&#039;t know why the other lines have something printed since the debug starts with &#039;Response.Write "TEST: "&#039;<BR><BR>anyway, what do you reckon?<BR><BR>Thanks<BR><BR>Jim

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## *BINGO*

Your fld_time field *DOES* have BOTH date and time in it, for starters.<BR><BR>And it just so happens to contain *ZERO* for the time!<BR><BR>To see what I mean, try <BR> &#060;%= CDBL(Date()) %&#062;<BR>guess what you&#039;ll get?<BR><BR>Yep. 38021.<BR><BR>So if you take <BR> TimeValue( rectime ) <BR>you *should* get<BR> 00:00:00<BR>for that first record.<BR><BR>So I can only guess that we/you goofed when we tried the TimeValue() function version of this stuff.<BR><BR>Maybe it would be helpful to do something simple as a test:<BR><BR>&#060;%<BR>...<BR>Do Until RS.EOF<BR> Response.Write RS("fld_time") & " --&#062;&#062; " & TimeValue( RS("fld_time") ) & "&#060;br/&#062;"<BR> RS.MoveNext<BR>Loop<BR>Response.End<BR>%&#062;<BR> <BR>??? See what that tells us.<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
310

## RE: *BINGO*

Hi,<BR><BR>strange this:<BR><BR>04/02/2004 --&#062;&#062; 00:00:00<BR>04/02/2004 02:00:00 --&#062;&#062; 02:00:00<BR>04/02/2004 03:30:00 --&#062;&#062; 03:30:00<BR><BR>when I look in the db, the time values are:<BR><BR>00:00:00 <BR>02:00:00 <BR>03:30:00 <BR><BR>Strange that the first response.write is minus a time. Is this because there is no such time as 00:00:00?<BR><BR>I had this before, I think it was to do with the way to MySQL ODBC driver interpreted the MySQL time field.<BR><BR>I did some silly thing to output ONLY the time, instead of the time and date (don&#039;t laugh):<BR><BR>Function timeonly(str)<BR> w = str<BR> w_h = hour(w)<BR> if len(w_h) = 1 then w_h = "0" & w_h<BR> w_m = minute(w)<BR> if len(w_m) = 1 then w_m = "0" & w_m<BR> timeonly = w_h & ":" & w_m<BR>End Function

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•