ASP class help - For database & form with 120 fields

[Dan]

(Using Classic ASP & MS Access)

I have a page that has 120 fields on it (mostly checkboxes). I cannot
split this into smaller pages.

So what i want to do is write a class that handles this. in the code it
would be something like:

Set thisForm = new classBigForm

thisForm.valnam e = Request.Form("n ame")
thisForm.val1 = Request.Form("v al1")
thisForm.val2 = Request.Form("v al2")
thisForm.val3 = Request.Form("v al3")
thisForm.xyz = Request.Form("x yz")
.....

Writing the class wasn't too bad. But repeating the same code 120
times, the file got pretty big. Is there a better way to do this
dynamically?

Also in the same respect to the amount of fields, is there an easy way
to compare all of these values, so I can run an sql update only on the
ones i need to?

[Mike Brind]

Dan wrote:

(Using Classic ASP & MS Access)

I have a page that has 120 fields on it (mostly checkboxes). I cannot
split this into smaller pages.

So what i want to do is write a class that handles this. in the code it
would be something like:

Set thisForm = new classBigForm

thisForm.valnam e = Request.Form("n ame")
thisForm.val1 = Request.Form("v al1")
thisForm.val2 = Request.Form("v al2")
thisForm.val3 = Request.Form("v al3")
thisForm.xyz = Request.Form("x yz")
....

Writing the class wasn't too bad. But repeating the same code 120
times, the file got pretty big. Is there a better way to do this
dynamically?

Also in the same respect to the amount of fields, is there an easy way
to compare all of these values, so I can run an sql update only on the
ones i need to?

You'll need to provide a bit more information on the form fields. Are
they all Yes/No options? Or are you providing users with a series of
related options, or groups of related options? How do they map to
database fields?

Also, what is the purpose of your class? Outputting the checkboxes in
html? Data Inserts, updates, deletes?

--
Mike Brind

[Dan]

Yeah, i was trying to keep it short. I figured that any longer, then
people wont read it. :)

The database has four tables. I'll pretend that the data were working
with are "items".

1. items - about 10 fields. holds info like "itemID".

2. itemProperties - about 40 fields.
itemID (to match with that of the 'items' table), - AUTO NUMBER
3 text fields, and everything else is a yes/no

3. itemMorePropert ies - about 40 fields
itemID (to match with that of the 'items' table)
5 text, and everything else "yes/no"

4. another table also related to items. assorted data types
The class acts as a container. For a any item it can hold all of it's
data.

(in the most common scenario)
the script creates a new class and populates it from the database. At
this point we take any http posts, and compares with the value in the
class. If it is different, then it is stored in the class, and the
class puts in in the db.

[Bob Barrows [MVP]]

Dan wrote:

(Using Classic ASP & MS Access)

I have a page that has 120 fields on it (mostly checkboxes). I cannot
split this into smaller pages.

So what i want to do is write a class that handles this. in the code
it would be something like:

Set thisForm = new classBigForm

thisForm.valnam e = Request.Form("n ame")
thisForm.val1 = Request.Form("v al1")
thisForm.val2 = Request.Form("v al2")
thisForm.val3 = Request.Form("v al3")
thisForm.xyz = Request.Form("x yz")
....

Writing the class wasn't too bad. But repeating the same code 120
times, the file got pretty big. Is there a better way to do this
dynamically?
One thing that may help is to use an array or dictionary object to store all
these boolean values, instead of using indifidual properties. (it sounds as
if your database structure would benefit from the same thinking). Are they
really named val1,...,valx? Or do they have real names with/without numbers?
if the former, just use an array. For refreshing the array values from the
Request object, you would do something like:

function RefreshFromRequ est()
dim i, req
'First, set all the booleans to False
for i = 0 to ubound(arBools)
arBools(i)=fals e
next
'Then, set the ones that appear in Request to True
Set req=Request.For m
for i = 0 to ubound(arBools)
if len(req("val" & i)) > 0 then arBools(i)=true
next
end function

if the latter, you will need to persist the field names in order to avoid
going to the database every time you need to use the dictionary. You could
do this in a file or in in an xml document kept in Application (don't even
think about storing a Dictionary object in Application or Session). Let's
say you do the xml in Application route. You would do something like this in
global.asa (untested - I did not refer to the documentation so there may be
minor syntax errors - you can find te documentation at
http://msdn.microsoft.com/library):

sub application_ons tart()
dim fld, xmldoc, root, node
set xmldoc=createob ject("msxml2.Fr eeThreadedDomDo cument")
set root=xmldoc.cre ateelement("fie lds")
set xmldoc.document element=root
'connect to the database and open a recordset on the first table
'Use a "where 1=2" clause to prevent records from being retrieved -
'we only want the field names at this point
'then
for each fld in rs.Fields
set node=xmldoc.cre ateelement("fie ld")
node.setattribu te "fieldname",fld .name
root.appendchil d node
next
'do the same for the subsequent tables - again, I would reconsider
'the database design ...
'then:
Set Application("Fi eldNamesXML") = xmldoc
end sub

This will allow you to do this in your class's init sub:

set dicBools=create object("scripti ng.dictionary")
set xmldoc=Applicat ion("FieldNames XML")
nodes=xmldocs.s electNodes("fie lds/field")
for each node in nodes
dicBools.Add node.getattribu te("fieldname") ,false
next
set BoolVals = dicBools

To refresh it from Request:
dim dicBools, req,fieldname
set dicBools = BoolVals
'since it's already initialized in the init sub, no need to set
'all the values to false

Set req=Request.For m
for each fieldname in dicBools
dicBools(fieldn ame)=req(fieldn ame)
next

Also in the same respect to the amount of fields, is there an easy way
to compare all of these values, so I can run an sql update only on the
ones i need to?

I would not consider doing dynamic sql for this. Just create a single
parameterized statement or saved query and pass all the values to it. You
are not going to save much processing time by only updating the ones that
have changed.

Here are some links explaining this approach:
Saved parameter queries:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

Using a Command object to pass values to string containing parameter
markers:
http://groups-beta.google.com/group/...e36562fee7804e
I will reply to your follow-up with a suggestion for your database design.

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"

[Bob Barrows [MVP]]

Dan wrote:

Yeah, i was trying to keep it short. I figured that any longer, then
people wont read it. :)

The database has four tables. I'll pretend that the data were working
with are "items".

1. items - about 10 fields. holds info like "itemID".

2. itemProperties - about 40 fields.
itemID (to match with that of the 'items' table), - AUTO NUMBER
3 text fields, and everything else is a yes/no

3. itemMorePropert ies - about 40 fields
itemID (to match with that of the 'items' table)
5 text, and everything else "yes/no"

4. another table also related to items. assorted data types
The class acts as a container. For a any item it can hold all of it's
data.

(in the most common scenario)
the script creates a new class and populates it from the database. At
this point we take any http posts, and compares with the value in the
class. If it is different, then it is stored in the class, and the
class puts in in the db.

The flaw with this design is that any time you need to add a new propery,
you have to modify the database structure, as well as all the code that
accesses this data. A better design would be:

boolPropertyNam es
boolName - Text, PK -
used to control names used in BoolPropertyNam es

itemBoolPropert ies:
itemID
boolName - Text containing the name of the property
foreign key to BoolPropertyNam es table
boolValue - Yes/No


This will allow you to use
Select boolName from BoolPropertyNam es

to get the boolean property names.

To add a new property, just add a record to boolPropertyNam es (You should
probably strictly control the names that get entered here - I would not
allow users to create properties - that could lead to chaos). Nothing else
needs to change.

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"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

if the latter, you will need to persist the field names in order to
avoid going to the database every time you need to use the dictionary. You
could do this in a file or in in an xml document kept in Application
(don't
even think about storing a Dictionary object in Application or Session).
Let's say you do the xml in Application route.

BTW, I am not married to the use of the Dictionary object. By adding an
attribute to the xml document, you could use the xml document to store your
values, instead of incurring the overhead of the dictionary object.

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"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

To add a new property, just add a record to boolPropertyNam es (You
should probably strictly control the names that get entered here - I
would not allow users to create properties - that could lead to
chaos). Nothing else needs to change.

If storing the names in Application, you will need to either restart your
application or create a page to refresh then names in the application object
whenever you add a new property name to this table.

--
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"

[Dan]

Thanks for all the advice. I think i want to go somewhere where bob
was mentioning, but im still not exactly sure.

As for the field names they are not all 'val1', 'val2'.. I believe all
of them are 3 letters long and all letters.

Here is a little more info on the class, and how i want to use it.

'1. grab the data and put it into a class
set existForm as new BigForm
existForm.formI D = Request.Form("f ormID")
existForm.Popul ateFromDB
' this would select * from...
'2.
set newForm as new BigForm
' copy all the values from existForm to newForm.
'3. I would have an array that lists all the fields.
' this part in not sure what the proper syntax would be. this would be
a function in the class called, populateFromHTT P

For i = 0 to ubound(aryTable Two)
newForm.i = Request.Form(i)
Next
'4. Finally we update the database, again using the array for the
field names.

For i = 0 to ubound(aryTable Two)
If existForm.i != newForm.i Then
' There has been a change.
'strSQL = strSQL & "UPDATE tableTwo Set " & i & " = newform.i &
" WHERE formID = " & newform.formid & ";"
End IF
Next

I think this method should work for the majority or the items. A few
things will need some special handling but i can work that out. Does
anybody know what the proper syntax for the above idea would be?