2. ASPRunnerPro General questions
  3. Handling time values
This topic is locked

Handling time values

 		3/5/2006 10:15:18 PM
ASPRunnerPro General questions
B
 berkeleyjw author

I am discovering limitations with how ASPRunner generates code for handling date/time fields when only a "time" value is required.
This issue was mentioned in the following post:

http://www.asprunner.com/forums/index.php?...0&hl=time+field
I have a date/time field that I use to store time values (i.e. begin time / end time).

In SQL Server, ou can just insert a value like "08:00" and it will convert to " 01/01/1900 8:00:00 " in the table.
I tried changing the edit controls in ASP Runner to simply be unformatted text fields. After the page was generated, I tried putting in 08:00 as a value. In the BeforeAdd event, I wanted to tack on the 01/01/1900 date.
However, the dict("start_time") field is NULL in the BeforeAdd event (I used a response.write to display the value). So, something happens to the field before the BeforeAdd event is processed.
I have heard suggestions on this post that time fields should just be stored as text fields in the underlying database. This does not seem like an acceptable solution, especially when any sort of time-checking / conflict algorithm is involved in the business rules.
Time fields are very important in a lot of applications. In my case, I have to set up class meeting start and end times.
One should be able to handle time fields and use a time picker control to select valid time values. I can't imagine that the only solution would be to change the data type structure of the underlying table. I would be interested to hear from other users who have experienced similar issues and how it was handled. Thanks.
B
 		berkeleyjw author 3/5/2006

FYI Here is what I tried to do to work around the time field issue.

  1. Created a new table called SRM_TIME_TBL. 3 fields:

    hour_string (char)

    minute_string (char)

    time_value (date/time)
  2. time_value is read only. hour_string and minute_string are populated with hard-coded items from a lookup list.
  3. In BeforeAdd, I put the following code:

    dict("time_value") = CDate( dict("hour_string") & ":" & dict("minute_string"))
  4. When I add a record to this table via the ASPRunner-generated pages, it correctly reads the hour and minute strings and places the default year into the date/time field. vbScript seems to make the default date December 30, 1899. So, if I use hour_string = "8"; minute_string = "25"; the date/time field is populated as "1899-12-30 08:25:00.000". The time_value field displays as "8:25 AM" so this must be correct.
    So far, I believe I have created a lookup table that I can use in the table at hand, called SRM_CRSE_SCTN_MTG.
  5. In SRM_CRSE_SCTN_MTG, I have the begin_time and end_time fields set up to use the SRM_TIME_TBL as the lookup table. I also have the field "view as" settings to "date time".
  6. When adding data to SRM_CRSE_SCTN_MTG through the ASPRunner-generated pages, the drop-down lists for begin-end date look great. They even display values like "2:45 PM", "8:25 AM", etc.
  7. When I attempt to add the record, I get an error:

    Error Type:

    Provider (0x80020005)

    Type mismatch.

    /ASPRunnerPro/SRM_CRSE_SECT_MTG_add.asp, line 271
    Code line in question reads as follows:

    rs("begin_time") = strValue
    There seems to be something awry with how the dictionary is being populated, from what I can tell. Please advise. Thanks.
B
 		berkeleyjw author 3/6/2006

I set the BeforeAdd event on SRM_CRSE_SECT_TBL as follows:
response.write "Begin Time " & dict("begin_time")

BeforeAdd = False
Now, I select the date/time value "8:25:00 AM" from the drop-down list as the value for begin_time. Behind the scenes, the selections in the list are date/time fields. The selection is stored internally as "1899-12-30 08:25:00.000".
When I attempt to add the record, it will now display a message instead of performing the save (just as I coded it to do). Here is what the message on the screen says:
Begin Time 1899-12-30 AM
As you can see, the time information seems completely removed from the dictionaty entry, but the AM/PM designator is not. This is why actually performing the database save results in a type mismatch error.
Sergey Kornilov admin 3/6/2006

Jim,
most database do not have a designated data type to store time values. Use simple text field for this purpose.
B
 		berkeleyjw author 3/6/2006

Hi, Sergey.
This still does not address the underlying problem. Which, specifically, is what happens to the date/time value when it is passed to the dictionary object. SQL server does not have a specific "time" data type, but it does have a "datetime" data type (as most other database systems also have).
What I am basically trying to do is store a datetime value from a list to a datetime field. This seems like it should work but it does not; somehow, the time portion of the field is not present in the field when it is put into the dictionary.
However, through later testing, I discovered that this is ONLY a problem when the date portion is 1899-12-30. This appears to be related to how ASP represents the base date with SQL Server. As such, this may not be under your control, but rather illustrates a "quirk" with how ASP works internally with SQL Server date/time fields.
After more experimentation, I was able to successfully create a functioning time picker. To do this, I revised the procedures as follows:

  1. Created a new table called SRM_TIME_TBL. 3 fields:

    hour_string (char)

    minute_string (char)

    time_value (date/time)
  2. time_value is read only. hour_string and minute_string are populated with hard-coded items from a lookup list.
    3. In BeforeAdd, I put the following code:

    dict("time_value") = "1900-01-01 " & CDate( dict("hour_string") & ":" & dict("minute_string"))
     4. When I add a record to this table via the ASPRunner-generated pages, it correctly reads the hour and minute strings and places these, along with the date 1900-01-01 into the date/time field. The time_value field displays as "1/1/1900 8:25 AM" .
    So far, I believe I have created a lookup table that I can use in the table at hand, called SRM_CRSE_SCTN_MTG.
  3. In SRM_CRSE_SCTN_MTG, I have the begin_time and end_time fields set up to use the SRM_TIME_TBL as the lookup table. I also have the field "view as" settings to "date time".
    ** 6. When adding data to SRM_CRSE_SCTN_MTG through the ASPRunner-generated pages, the drop-down lists display values like "1/1/1900 2:45 PM", "1/1/1900 8:25 AM", etc.
    I am then able to save these fields successfully. From a stylistic point of view, I would rather not have the "1/1/1900" portion of the field displayed in the list, but that is not a major concern.
    I hope this information is helpful to anyone else dealing with storing time values using ASP and SQL Server.