View Full Version : SQL problem..help!!

11-21-2003, 09:31 AM
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?

11-21-2003, 02:19 PM
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.


dim element 'arbitrary variablename
for each elemen in request.form
your code

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

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

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.

11-24-2003, 02:26 AM
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.

11-24-2003, 08:05 PM
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.