ASP class help - For database & form with 120 fields

Discussion in 'ASP General' started by Dan, Apr 21, 2006.

  1. Dan

    Dan Guest

    (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.valname = Request.Form("name")
    thisForm.val1 = Request.Form("val1")
    thisForm.val2 = Request.Form("val2")
    thisForm.val3 = Request.Form("val3")
    thisForm.xyz = Request.Form("xyz")
    .....

    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?
    Dan, Apr 21, 2006
    #1
    1. Advertising

  2. Dan

    Mike Brind Guest

    Re: ASP class help - For database & form with 120 fields

    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.valname = Request.Form("name")
    > thisForm.val1 = Request.Form("val1")
    > thisForm.val2 = Request.Form("val2")
    > thisForm.val3 = Request.Form("val3")
    > thisForm.xyz = Request.Form("xyz")
    > ....
    >
    > 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
    Mike Brind, Apr 21, 2006
    #2
    1. Advertising

  3. Dan

    Dan Guest

    Re: ASP class help - For database & form with 120 fields

    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. itemMoreProperties - 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.
    Dan, Apr 21, 2006
    #3
  4. 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.valname = Request.Form("name")
    > thisForm.val1 = Request.Form("val1")
    > thisForm.val2 = Request.Form("val2")
    > thisForm.val3 = Request.Form("val3")
    > thisForm.xyz = Request.Form("xyz")
    > ....
    >
    > 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 RefreshFromRequest()
    dim i, req
    'First, set all the booleans to False
    for i = 0 to ubound(arBools)
    arBools(i)=false
    next
    'Then, set the ones that appear in Request to True
    Set req=Request.Form
    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_onstart()
    dim fld, xmldoc, root, node
    set xmldoc=createobject("msxml2.FreeThreadedDomDocument")
    set root=xmldoc.createelement("fields")
    set xmldoc.documentelement=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.createelement("field")
    node.setattribute "fieldname",fld.name
    root.appendchild node
    next
    'do the same for the subsequent tables - again, I would reconsider
    'the database design ...
    'then:
    Set Application("FieldNamesXML") = xmldoc
    end sub

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

    set dicBools=createobject("scripting.dictionary")
    set xmldoc=Application("FieldNamesXML")
    nodes=xmldocs.selectNodes("fields/field")
    for each node in nodes
    dicBools.Add node.getattribute("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.Form
    for each fieldname in dicBools
    dicBools(fieldname)=req(fieldname)
    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&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=

    Using a Command object to pass values to string containing parameter
    markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e


    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], Apr 22, 2006
    #4
  5. Re: ASP class help - For database & form with 120 fields

    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. itemMoreProperties - 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:

    boolPropertyNames
    boolName - Text, PK -
    used to control names used in BoolPropertyNames

    itemBoolProperties:
    itemID
    boolName - Text containing the name of the property
    foreign key to BoolPropertyNames table
    boolValue - Yes/No




    This will allow you to use
    Select boolName from BoolPropertyNames

    to get the boolean property names.

    To add a new property, just add a record to boolPropertyNames (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], Apr 22, 2006
    #5
  6. 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], Apr 22, 2006
    #6
  7. Re: ASP class help - For database & form with 120 fields

    Bob Barrows [MVP] wrote:
    >
    > To add a new property, just add a record to boolPropertyNames (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"
    Bob Barrows [MVP], Apr 22, 2006
    #7
  8. Dan

    Dan Guest

    Re: ASP class help - For database & form with 120 fields

    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.formID = Request.Form("formID")
    existForm.PopulateFromDB
    ' 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, populateFromHTTP

    For i = 0 to ubound(aryTableTwo)
    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(aryTableTwo)
    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?
    Dan, Apr 23, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Maper
    Replies:
    0
    Views:
    325
    Maper
    Jan 27, 2005
  2. F. GEIGER
    Replies:
    2
    Views:
    442
    Philipp Klaus Krause
    Jun 12, 2004
  3. mdh

    page 120 K&R

    mdh, Nov 27, 2007, in forum: C Programming
    Replies:
    46
    Views:
    1,117
    David Thompson
    Dec 24, 2007
  4. OSS
    Replies:
    0
    Views:
    288
  5. Ruby Quiz

    [QUIZ] Magic Fingers (#120)

    Ruby Quiz, Apr 13, 2007, in forum: Ruby
    Replies:
    13
    Views:
    340
    Jesse Merriman
    Apr 19, 2007
Loading...

Share This Page