[thelist] comparing form data to database data

[Tab Alleman]

Do you *have* to verify the range before doing the insert?

Cause otherwise you can just insert everything into the database, and then
do a:

SELECT * FROM Table WHERE (SubstitutesStartDate BETWEEN ActivityStartDate
AND ActivityEndDate) AND (SubstitutesEndDate BETWEEN ActivityStartDate AND
ActivityEndDate)

Making up column names here, but hopefully you get the idea.

hth

-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of McCreath_David
Sent: Thursday, October 26, 2000 9:38 PM
To: 'thelist at lists.evolt.org'
Subject: [thelist] comparing form data to database data


ASP 3.0 / IIS 5 / SQLServer 7

Looking for some refinement hints. I'm comparing a date range from a form to
an existing date range in a table. It works like this:

The user fills out one form and provides a beginning date and an end date
for a school/student activity (it could be a multiple month activity, so the
date range could be rather large). This data is inserted into a table and
the activity is given an auto ID. (Each date goes into a separate column.)

The next form in the chain is a request for substitutes for a time within
that date range. That form has a pull-down list of all the activities
related to this school, built from the table mentioned above. In this form,
they enter a date range as well, and what I need to verify is that this date
range falls within the date range from the previous form.

I did it this way:

In the sub that does the insert for the second form, I pull a recordset of
just the two dates from the activity table and set them as variables then do
a simple client-side comparison in the regular form validation. It works,
but it seems kinda wonky to make two calls to the database in one sub.

Is there a way to do it in the stored procedure? I couldn't find any
examples of exactly what I was trying to do, and it seems like I ought to be
able to do it with SQL in the stored procedure, but I'm stopped in two
places. 1) I'm not quite sure what the actual syntax of the statement should
be and 2) I'm not sure how to report the error to the user.

Clear as mud?

Dave
----
home: http://home.gci.net/~mccreath/
work: http://www.asd.k12.ak.us/

---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !