Unbound Form Data Validation 1

[tis9700]

Morning,
I've been experimenting with unbound ADP forms using stored procedures for inserting records. And one of the things I've noticed is data validation in a bound form may not work as it did in a bound form. For example..

I have 5 unbound text boxes for collecting and storing data..
Caucasian
AfricanAmerican
Hispanic
NativeAm
Other

I have 1 unbound textbox as a calculated control that totals these amounts called ttlRace. In the control source...
=Nz([Caucasian],0) + Nz([AfricanAmerican],0) + etc.

I used to have some code in the Form Before Update event (when it was bound) that compared the ttlRace to another textbox called Enrolled....

If me.ttlRace.Value <> me.Enrolled.value then
Msgbox "Total Race must equal the amount of students enrolled."
Cancel = true
me.Caucasian.Setfocus
end if

Of course it doesn't work now because the form is unbound and the record is not inserted until a save button is clicked.

So my questions are..

What event could is use to fire this event before the record is saved?

And what advice does anyone have when dealing with data validation in unbound forms? Best Practices?

Thanks

 

[PWise]

in the code behind the save button
add this code at the start

If me.ttlRace.Value <> me.Enrolled.value then
 Msgbox "Total Race must equal the amount of students  enrolled."
  'Cancel = true
  me.Caucasian.Setfocus
  exit sub
end if

 

[tis9700]

Hi pwise,
Sorry for the delayed response. That did the trick. I'm now putting a lot of my validation in the Click fevent of my save button.
Thanks

 

[danvlas]

I would do the validation in the stored procedure.
Raiserror if validation fails and return the error to adp.
This way you eliminate the possibility of data being changed in tha lag between filling in the form and clicking the 'Save' button.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tis9700]

Hi danvlas,
So far I've set up a stored procedure that inserts the record and returns the scope_identity to the adp using an ADO recordset after inserting the data with the command object. Thanks to pwise. I've never attempted putting that type of validation in the stored procedure but would like to learn. Do you have an example or a link to an example that will get me started? I've used @@RAISERROR before a transaction...

But I've never returned that message to an ADP.

For example,

IF EXISTS (SELECT.....)
IF @@ERROR = 0 OR @@ROWCOUNT.....
BEGIN
RAISERROR('BLAH BLAH')
END

BEGIN TRAN

INSERT STATEMENT


ETC..

Thanks

 

[danvlas]

Check BOL for RAISERROR, especially for the 'severity' argument.

RAISERROR('BLAH BLAH', 16,1)
RETURN

will stop the execution of the stored procedure.
The error is transmitted via ADO to the adp (i assume you do have error handling).

MsgBox Err.Description will read 'BLAH BLAH'

In your case, the validation looks simple - which means I'm wrong with the assumptions:


Alter Proc SomeProc(@enrolled int)
As
Set Nocount On
Declare @servertotal int
Declare @result varchar(2000)

Select @servertotal=coalesce(caucasian, 0) + coalesce(AfricanAmerican,0) + ...
If @servertotal<>@enrolled
Begin
--build a comprehensive - or not - message to the user
select @result='There is a difference of: ' + cast(@servertotal-@enrolled as varchar) + ' between ' + cast(@servertotal as varchar) + ' - stored data - and ' + cast(@enrolled as varchar) + ' -received from your input'
raiserror(@result,16,1)
return
end

--the check has been performed, you can proceed now.


I just hope Caucasian, AfricanAmerican, Hispanic and so on are NOT fields in some table, but values of a field in a lookup table.

If your tables are normalized, you should have a simpler formula:

select @servertotal=sum(NumericField) From YourUnNamedTable Where SomeConditionIsMet,


instead of summing up 5 separate fields (what happens if they become 6...or 7?)



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tis9700]

Thanks danvlas,
So far that works great though I haven't added all my validation as of yet. But it's given me really good direction.

Question about the following statement..

"I just hope Caucasian, AfricanAmerican, Hispanic and so on are NOT fields in some table, but values of a field in a lookup table."

These are fields in my table that hold amounts not strings values so I'm not sure I understand why I would want a lookup table with numeric values.

Thanks

 

[danvlas]

So you have a table that looks like:

Race1 Race2 Race3 Race4
10 2 4 11

That is bad... What happens when you need to add another race? Like 'mongoloids' for instance (the most dense population on Earth, not -yet- included in your main list)? Change everything?
And then...you will want to track Bushmen among students...
After that...Central African pigmies...

I admit it sounds somehow paranoid. But you'd be surprised how often such changes are requested by users who acknowledge what they can get from a database...

You should normalize your database in such a way that it accepts any number of races - aliens included - without any structural and/or program change.

If you post the table structure I'm sure you will get some good directions on this particular issue.
And it will save you a lot of trouble and effort in the future.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tis9700]

Good morning danvlas,
It's another beautiful morning in Tennessee!

So are you saying I should have a table for each race?

Here's my table structure ...

CREATE TABLE [Classes] (
[classID] [int] IDENTITY (1, 1) NOT NULL ,
[instID] [int] NOT NULL CONSTRAINT [DF__CLASSES__InstID__07F6335A] DEFAULT (0),
[classDate] [datetime] NOT NULL ,
[classType] [tinyint] NOT NULL ,
[courseType] [tinyint] NOT NULL ,
[classCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ttlHrsTaught] [smallint] NOT NULL ,
[Enrolled] [smallint] NOT NULL ,
[Graduated] [smallint] NOT NULL CONSTRAINT [DF__CLASSES__Graduat__08EA5793] DEFAULT (0),
[schoolHrs] [bit] NOT NULL CONSTRAINT [DF__CLASSES__Schooll__09DE7BCC] DEFAULT (0),
[Males] [smallint] NOT NULL CONSTRAINT [DF__CLASSES__Males__0AD2A005] DEFAULT (0),
[Caucasian] [smallint] NOT NULL CONSTRAINT [DF__CLASSES__Caucasian__0BC6C43E] DEFAULT (0),
[AfricanAmerican] [smallint] NOT NULL CONSTRAINT [DF__CLASSES__AfircanAm__0CBAE877] DEFAULT (0),
[NativeAmerican] [smallint] NOT NULL CONSTRAINT [DF__CLASSES__NativeAm__0DAF0CB0] DEFAULT (0),
[Hispanic] [smallint] NOT NULL CONSTRAINT [DF__CLASSES__Hispani__0EA330E9] DEFAULT (0),
[Other] [smallint] NOT NULL CONSTRAINT [DF__CLASSES__Other__0F975522] DEFAULT (0),
[classComments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtCreated] [datetime] NOT NULL CONSTRAINT [DF_CLASSES_dtCreated] DEFAULT (getdate()),
[racfID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CLASSES_racfID] DEFAULT (suser_sname()),
CONSTRAINT [aaaaaCLASSES_PK] PRIMARY KEY NONCLUSTERED
(
[classID],
[instID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks

 

[danvlas]

NO, NO, NO, NO...

Instead of 'Caucasian', 'AfricanAmerican' and so on
you should have a RACE field, which should be a foreign key pointing to a RACES table.

You already have something like that: instID field that looks like pointing to another table.

Races table could have only one column - primary key - containing all races as rows. But my personal preference is to set a user-meaningless identity column as a primary key.


However, there are many flaws in your table design:
1. some column names contain prefixes, others don't. You should make your decision and stick to it: either use them or not

2. You have a 'strange' primary key, based on 'classID' and 'instID'. Since ClassID is an identity field, any combination of it with any other fields will be unique by nature. You should search the natural key that makes the record unique WITHOUT using an identity field.

3. Enrolled, Graduated and Males look like summary data. Especially 'Males' would trouble you in the future.

I believe 'Race' and 'Sex' would be attributes of a separate 'Students' table

I'd see a structure like:

Students table:
stdID (PK)
stdName (maybe split into first/middle/last)
stdSex (either M or F)
stdRaceID (FK to Races table)

Classes
clsID (PK)
clsInstID (you know better what this is, as I have no idea)
clsName - unique constraint
clsType (FK to a ClassTypes table)
clsCourseType (this one might belong to ClassTypes table...)
clsDate
clsHoursTaught

ClassStudents table (many to many between Students and Classes)

cssID (PK)
cssStudentID (FK to Students)
cssClassID (FK to Classes)
cssDateEnrolled not null
cssDateGraduated
cssGrade

Races table
racID (PK) - personal preference to use identity primary keys
racName - unique index or constraint

This way you have everything you need to construct all you need.

Select clsName, Count(cssID) As Enrolled, Sum(Case When stdSex = 'M' then 1 else 0 end As Males), racName As Race From
Classes Inner join ClassStudents on clsID=cssClassID
inner join Students on cssStudentID=stdID
Inner Join races on stdRaceID=racID
group by clsName, racName


is an example of SQL that would give you the result that you seek, IF you normalize your structures.

Trust me, it's the most important part in database design.





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tis9700]

Ok danvlas,

I see what you mean about Races. That would be the idea situation. My only concern is that they been collecting this data for the 15 to 20 years. It's on a mainframe and they NEVER attached the race to a specific student record only class records. In fact, there's conflict as to whether they should have been collecting it at all. Which is why they had Instructors collect it rather than asking the student. Can anyone say "State Government"?

You're right I do need to stick to a naming standard as far as my fields are concerned.

I removed Primary off of instID. I do not have any one unique natually ocurring attribute, so what about using a composite?

Thanks for the instruction.

 

[danvlas]

That indeed changes the situation.
I didn't take it into account because I live in a part of the world where race is of no importance, probably due to the fact that more than 95% of the population is Caucasian.

However:

A table ClassRaces could do:

clrID (PK)
clrClassID (FK to Classes)
clrRaceID (FK to Races)
clrNoOfStudents

would still be better than having them horizontally.

I never said the natural key had to be made of one field only. As a matter of fact, there are few situations when it's even possible.

I use an Identity as primary key on each table, combined with a multi-field unique index that makes sure no duplicate records are entered

I prefer it that way because it simplifies the joins and relationships in the majority of situations and it can be easily standardized.



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tis9700]

Thanks danvlas,
I'm going to use those suggestions. Thanks for all the instruction.
Have a nice day!