I have a scenario wherein with the following details:
I have a form that contains fields like firstname,middle name,lastname,dob,pin,address for Client 1.
Now this form has to cater more than one client . The problem statement is - different clients may require different number of fields to be displayed on front end. For eg: a 2nd client would want - Country field instead of pin, would not require address on the form.
Now when i submit the form , currently i have a constant query which takes values of - firstname,middle name,lastname,dob,pin,address and inserts it into database for Client 1. I want to develop a query in a way that it is created at runtime and inserts only the values that come from the form into the database..
I hope my problem is clear. Can anyone help?????????????
You need to think about why you are doing this.
It will get hideously complex with just person form, add in more and it will balloon big style.
Dynamically building queries isn't particularly complicated, but it's a lot of code to do it. Just think about each field you want to add to the query and it's type. What if a property on your person record was an image of them. Do you have a configuration of the form, is the promary key on the record an auto inc, is it a compound key, do you use defaults, are some fields not null. How are you going to bubble back referential integrity exceptions...
Do the all singing all dancing version and basically you reinvent something like the Access IDE....
Personally I'd have a Person object with a set of Person Properties, they would have name, a value and a boolean to say whether they'd been changed.
Once you have teh list of chnaged properties and beacseu you are in the Person object you know the table is persons, it's keyed by an autoinc bigint, gender is mandatory and defaults to Male...
You have a fighting chance.
Your query should use parameters
So it would be say Insert Persons(FirstName, LastName, PIN) Values(@FirstName,@LastName,@PIN)
Then you'd nip through your changed fields and add parameters with same name, type and value.
As I said you can really go to town with this one, may be it's time for a night in though.