Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL problem..help!!

    I have a form like the pic shows,there are many select box and input textfiled in the from.How can I produce the SQL query string dynamically after user selected all the boxes.
    The first approach way i use is if-else condition.
    Depending on the value get from the form and use the if-else condition to write the sql string.But that is an unefficient way because i have to write many if-else conditions(nested) so that the code is hard to read and maintain.
    Can someone help to solve this problem?
    Attached Thumbnails Attached Thumbnails SQL problem..help!!-snap.jpg  

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The way that i solve this (with single forms), is by naming the formfields according to the db-column name.
    Then i use a for each loop to proces all fields.

    like
    Code:
    dim element    'arbitrary variablename
    for each elemen in request.form
      your code
    next
    Now, you'll probably wan't to run different code depending on the fieldtype (checkboxes etc) so you can use a select case to find out which fieldtype the form-variable was (for instance by compising the formfield-names like : chkusername --> chk = checkbox, username=columnname in db. drp = dropdown etc

    then you can go
    Code:
    dim element    'arbitrary variablename
    for each elemen in request.form
      select case left(element, 3)
    	case "chk"
                        your code for checkboxes.
                       to get the variablename --> mid(element, 4)
    	case "drp"
                         your code for dropdowns
       end select
    next
    But you'll immedeately see that if you want somthing completely generic (so also dynamically build the form), that you need to store some data about the fieldtypes etc inside a db. So you need a metatable that describes your ttablecollumns + their form-fieldtypes

    At the moment, i'm finalysing a formengine in PHP, that is completely generic (automatically create tables and columns, lets you specify formfieldtypes, defaultvalues, valueformats (regex etc), automatically validates the forms and writes the values to the linked tables and columns + where you don't have a staight form, but where you have a (serie of) screen(s) with different tabs, so that you can have some interactivity and a logical screenflow.

    Just to say that there are almost unlimited possibilitys, but the more generic it gets, the less code and the more db-design and business-logic you'll need to set up.
    I've written an MsAccess-webfront about a year ago that was completely generic (but it didn't have any valuechecking and the form-structure was fixed). If you wan't to take a look at the code, then just let me know. I'll mail it to you or give you a downloadlink.

  • #3
    New Coder
    Join Date
    Nov 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help,raf.
    I am very insterestedn in the MsAccess-webfront your wrote,maybe you would like to give me the downloaded link.

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    http://www.raf.ithium.net/download/DBGates.zip (93kB)

    I thought i had a small get-started file with some info to set the metatable up, but i don't immedeately find it. I can look after it on my machine at work on friday.

    The zip just needs to be extracted and you then put all the files on your webserver. There is a loginscreen, but you can find the usernames and pwd's in the db.

    The pages that deal with dynamically creating and processing the fields --> take a look at admin_editrecord.asp and admin_update_record.asp .

    If you would be intrested in using it, let me know. I should add some extra functionalitys to it (creating new records for instance, a wizard to create the metatable for existing tables, ...), but i don' use it anymore so i stopped working on it.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •