inetserver asp db: 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

[quoted text, click to view]

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)

[quoted text, click to view]

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

[quoted text, click to view]

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"

[quoted text, click to view]

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

[quoted text, click to view]
The rounding occurs when the data is stored. Different systems will have
different results when rounding.

[quoted text, click to view]
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.

[quoted text, click to view]

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