data entry via query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've made a database that allows data to be input through webpages. I want
to be able to make data entry as fool proof as possible by preventing people
from inputting data that causes impossible situations on the other end of a
calculation. For example, I'm having people input the length of their shift
and the amount of time a particular machine is down for a repair. In my
efficiency calculation problems arise if the person accidentally entered a
larger number for machine downtime than they did for their shift length. I
have thought about remedying the situation by linking the input blanks on the
website to a query rather than a table and putting validation rules on the
query saying that the difference between machine downtime and shift length
cannot be less than 0. Is it possible for data to be entered with a query?
Or is there a way to create calculated validation rules within the table that
the website is currently linked to? Thanks in advance.
 
Mark said:
I want
to be able to make data entry as fool proof as possible by preventing people
from inputting data that causes impossible situations on the other end of a
calculation. For example, I'm having people input the length of their shift
and the amount of time a particular machine is down for a repair. In my
efficiency calculation problems arise if the person accidentally entered a
larger number for machine downtime than they did for their shift length. I
have thought about remedying the situation by linking the input blanks on the
website to a query rather than a table and putting validation rules on the
query saying that the difference between machine downtime and shift length
cannot be less than 0. Is it possible for data to be entered with a query?
Or is there a way to create calculated validation rules within the table that
the website is currently linked to?
Click to expand...

You should always put validation rules in the database (e.g. CHECK
constraints) of course. To use a very simple example:

CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
CHECK (col1 < col2)
);

You could write a procedure (parameter query) like this:

CREATE PROCEDURE TestProc1 (
arg_col1 INTEGER,
arg_col2 INTEGER
) AS
INSERT INTO Test (col1, col2)
VALUES (arg_col1, arg_col2);

However, when the parameter values fail the check you get an error e.g.


EXECUTE TestProc1 1, 2; -- No error, one row affected
EXECUTE TestProc1 99, 3; -- Error

To answer your question, yes you can use a query to ensure only valid
data is used. To revise the above proc:

CREATE PROCEDURE TestProc2 (
arg_col1 INTEGER,
arg_col2 INTEGER
) AS
INSERT INTO Test (col1, col2)
SELECT DISTINCT arg_col1 AS col1, arg_col2 AS col2
FROM Test AS T1
WHERE arg_col1 < arg_col2;

Invalid (integer) values no long cause an error:

EXECUTE TestProc1 1, 2; -- No error, one row affected (insert
succeeds)
EXECUTE TestProc1 99, 3; -- No error, no rows affected (insert fails)

Jamie.

--
 
Back
Top