Variable number of SQL query conditions

  • Thread starter Thread starter ime
  • Start date Start date
I

ime

Hi to all.
I'm making a web application in which users enter text
for a person's name, last name, sex, etc.
But user doesn't have to populate all text boxes, so I don't know how to
make query without these conditions (for example if I have an empty string
for a name) .
I've got 9 text boxes, so I can't write all combinations - it's to many of
them.
Thanks a lot
 
Hi Ime,

I generally use lots of ifs, i know you have to write a lot of cide, but it
works, and its less than doing 9 different select statements! eg (in C#,
sorry)

string query, where;
query "SELECT * FROM table WHERE (";

// textbox 1
if(textbox1.text != "")
where = "(field = '" + textbox1.text + "')";

// textbox 2
// textbox 3
// etc

// finalise
query += where + ") ORDER BY field ASC";

Of course, you need some code to and in " AND " into your where string, but
thats the basics. Then just query your database with the string query.

HTH


Dan
 
Thanks Dan

dhnriverside said:
Hi Ime,

I generally use lots of ifs, i know you have to write a lot of cide, but
it
works, and its less than doing 9 different select statements! eg (in C#,
sorry)

string query, where;
query "SELECT * FROM table WHERE (";

// textbox 1
if(textbox1.text != "")
where = "(field = '" + textbox1.text + "')";

// textbox 2
// textbox 3
// etc

// finalise
query += where + ") ORDER BY field ASC";

Of course, you need some code to and in " AND " into your where string,
but
thats the basics. Then just query your database with the string query.

HTH


Dan
Click to expand...
 
Make sure you validate any User Supplied Fields to guard against SQL
injection with this approach though!
 
Make sure you validate any User Supplied Fields to guard against SQL
injection with this approach though!
Click to expand...


Good point about injection! The secure way to insert is to us a
parameterized query. That said, I'm not going to use one, but
demonstrate a simple insert.

Why all the ifs?

Make sure your database fields can accept null values.

Then:


String var1, var2, var3, var4;

var1 = TextBox1.Text.Replace ("'","''");
var2 = TextBox2.Text.Replace ("'","''");
var3 ...

Then build your SQL string: (Linebreaks added for clarity).

sql = "INSERT INTO Table
(
field1,
field2,
field3,
field4
) VALUES ( " +
var1 + ", " +
var2 + ", " +
var3 + ", " +
var4 + ";"
)

My syntax may be off, but do you get the idea?

-- ipgrunt
 
Back
Top