Writing times to Access using ASP

Hello all!

I'm working on a system for my colleagues to record meetings with clients.
I've done plenty of ASP pages before and have had run-ins with dates before,
but have never done anything with a time in before.

I've got a table called tbl_times with has one field, in short time format,
that contains the available slots in the working day starting at 9am - 9:00,
9:15, 9:30, etc.

I've got another table called tbl_meeting which has fields for the staff id,
who they're visiting, the date, etc., and also the time, also in short time
format. There is a relationship between the two time fields, which has
"enforce referential integrity" set.

When entering data directly into the tables, this works fine - I can enter a
meeting for any time that exists in tbl_times, but not other times, as you'd
expect.

I've created ASP scripts - one for data entry with combo boxes, one of which
is a list of times from tbl_times so that the time of the meeting can be
selected from a list, and one to save the form data into tbl_meeting.

If I enter a meeting at 09:00 through the ASP form it all works perfectly
and the record gets written to the database. However, if I select 09:15 (or
any other time, for that matter) I get the following error:

"You cannot add or change a record because a related record is required in
table 'tbl_times'."

09:15 is in tbl_times, because that's the table used to fill the combo. Why
does it work for 09:00, but not other times? I've looked at the source of
the page and all of the times look like they're formatted in the same way.
It's not to do with the minutes, because 10:00 doesn't work either, and it's
not to do with the database structure because I can enter 09:15 into the
table.

I've had a look through other group postings about time problems and tried
trim() and timevalue() when saving the times to the database, but they don't
make any difference.

I know that I can get around the problem by removing the relationship
between tbl_meeting and tbl_times as it's not really needed if I'm using a
combo for the time, but I'm curious about what's going on!

Andrew

Re: Writing times to Access using ASP

Andrew Virnuls wrote:
> Hello all!
>
> I'm working on a system for my colleagues to record meetings with
> clients. I've done plenty of ASP pages before and have had run-ins
> with dates before, but have never done anything with a time in before.
>
> I've got a table called tbl_times with has one field, in short time
> format, that contains the available slots in the working day starting
> at 9am - 9:00, 9:15, 9:30, etc.
>
> I've got another table called tbl_meeting which has fields for the
> staff id, who they're visiting, the date, etc., and also the time,
> also in short time format.

I am going to assume that this is a date/time field, correct? if so, you
have to realize: there is no such thing as storing only time in a date/time
field. The Format property only controls what is displayed, not what is
stored.

Jet (Access) stores date/times as Double, with the whole number portion
containing the number of days since the seed date, and the decimal portion
containing the time of day (.0 = midnight, .5 = noon)

> There is a relationship between the two
> time fields, which has "enforce referential integrity" set.

That's part of the problem. Double values are subject to the floating-point
vagaries inherent in all computer systems since they use binary to store
values. See: http://www.aspfaq.com/show.asp?id=2477

>
> When entering data directly into the tables, this works fine - I can
> enter a meeting for any time that exists in tbl_times, but not other
> times, as you'd expect.
>
> I've created ASP scripts - one for data entry with combo boxes, one
> of which is a list of times from tbl_times so that the time of the
> meeting can be selected from a list, and one to save the form data
> into tbl_meeting.
>
> If I enter a meeting at 09:00 through the ASP form it all works
> perfectly and the record gets written to the database. However, if I
> select 09:15 (or any other time, for that matter) I get the following
> error:
>
> "You cannot add or change a record because a related record is
> required in table 'tbl_times'."
>
> 09:15 is in tbl_times, because that's the table used to fill the
> combo. Why does it work for 09:00, but not other times? I've looked
> at the source of the page and all of the times look like they're
> formatted in the same way. It's not to do with the minutes, because
> 10:00 doesn't work either, and it's not to do with the database
> structure because I can enter 09:15 into the table.

09:15 (0.38541666666666666666666666666667) cannot be stored exactly in
binary, so the comparison will fail.

You have two options (which one you pick depends on the other ways you wish
to use these values):
a. store the times as Text instead of date/time.
b. store the times as integer representations: 0 = midnight, 915 = 9:15
c. store the times as integers representing the number of minutes since
midnight(0) 9:15 would be stored as 555.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Writing times to Access using ASP

Bob Barrows [MVP] wrote:
> You have two options (which one you pick depends on the other ways
> you wish to use these values):
> a. store the times as Text instead of date/time.
> b. store the times as integer representations: 0 = midnight, 915 =
> 9:15
> c. store the times as integers representing the number of minutes
> since midnight(0) 9:15 would be stored as 555.
>


I feel like the cardinal in the Monty Python Spanish Inquisition sketch ...
--
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.

Re: Writing times to Access using ASP

"Bob Barrows [MVP]" wrote in message
news:%23oqgjkjLGHA.1088@tk2msftngp13.phx.gbl...
>
> I am going to assume that this is a date/time field, correct? if so, you
> have to realize: there is no such thing as storing only time in a
date/time
> field. The Format property only controls what is displayed, not what is
> stored.
>
> Jet (Access) stores date/times as Double, with the whole number portion
> containing the number of days since the seed date, and the decimal portion
> containing the time of day (.0 = midnight, .5 = noon)

Thanks for your help! It all makes sense, apart from one thing...

Why does it behave differently when you use ASP?! Why does 09:15 typed into
an Access table work without a problem, and 09:15 typed into a web-page and
then inserted into a table not work? Is the conversion to a double done by
the ODBC driver? And does it do it differently from the Access application?

I know I'm probably expecting too much as I had a similar problem with
dates - they appear in tables in dd/mm/yyyy format, come out on ASP pages in
dd/mm/yyyy format, but if you take a date from the result of a query and
stick it back in to a table, it swaps around to mm/dd/yyyy - but it would be
nice if these things worked in a common-sense way!

Andrew

Re: Writing times to Access using ASP

Andrew Virnuls wrote:
> "Bob Barrows [MVP]" wrote in message
> news:%23oqgjkjLGHA.1088@tk2msftngp13.phx.gbl...
>>
>> I am going to assume that this is a date/time field, correct? if so,
>> you have to realize: there is no such thing as storing only time in
>> a date/time field. The Format property only controls what is
>> displayed, not what is stored.
>>
>> Jet (Access) stores date/times as Double, with the whole number
>> portion containing the number of days since the seed date, and the
>> decimal portion containing the time of day (.0 = midnight, .5 = noon)
>
> Thanks for your help! It all makes sense, apart from one thing...
>
> Why does it behave differently when you use ASP?! Why does 09:15
> typed into an Access table work without a problem, and 09:15 typed
> into a web-page and then inserted into a table not work? Is the
> conversion to a double done by the ODBC driver? And does it do it
> differently from the Access application?
>
The rounding occurs when the data is stored. Different systems will have
different results when rounding.

> I know I'm probably expecting too much as I had a similar problem with
> dates - they appear in tables in dd/mm/yyyy format, come out on ASP
> pages in dd/mm/yyyy format, but if you take a date from the result of
> a query and stick it back in to a table, it swaps around to
> mm/dd/yyyy - but it would be nice if these things worked in a
> common-sense way!
>
http://www.aspfaq.com/show.asp?id=2040

Bob Barrows
--
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.