Building a WHERE clause in sql

F

Fuma

Executive Member
Joined
Jul 9, 2007
Messages
5,365
Reaction score
346
Location
Pretoria
I have 3 text boxes (txtID, txtName, and txtSurname) in my webform and I populate a repeater based on the values in those text boxes. I need to build a WHERE condition and I want to avoid this:
Code: 
dim WhereCondition as String = ""
  
if txtID.Text <> "" AND txtName.Text <> "" AND txtSurname.Text <> "" then
  WhereCondition = " id = '" & txtID.Text & "' AND name = '" & txtName.Text & "' AND surname = '" & txtSurname.Text & "'"
end if
if txtID.Text <> "" AND txtName.Text = "" AND txtSurname.Text = "" then
  WhereCondition = " id = '" & txtID.Text & "'"
end if
if txtID.Text = "" AND txtName.Text <> "" AND txtSurname.Text = "" then
  WhereCondition = " name = '" & txtName.Text & "'"
end if

sql = "SELECT * from table WHERE " & WhereCondition
Surely I can use a better combination of building the WhereCondition string.
 
I forgot the ElseIf part

So if I have 5 more text boxes, I have to redo the combination? No man
 
Code: 
Dim WhereCondition as String = ""
Dim selectWord as String = "where"

if txtID.Text <> "" then
  WhereCondition = WhereCondition & selectWord & " id = '" &  txtID.Text &"'\n"
  selectWord = "and"
end if

if txtName.Text <> "" then
  WhereCondition = WhereCondition & selectWord & " name = '" &  txtName.Text &"'\n"
  selectWord = "and"
end if

if txtSurname.Text <> "" then
  WhereCondition = WhereCondition & selectWord & " surname = '" &  txtSurname.Text &"'\n"
  selectWord = "and"
end if

Dim sql as String = "SELECT * from table " & WhereCondition
 
Last edited:
Roo! has it, except for the bug. Can you spot the bug?

Well, there are two.
 
Yeah may end up with the sql string having WHERE in it more than once.
 
selectword begins set to WHERE. And you have a WHERE on the last line.
so, it will be WHERE WHERE

the second bug is if there are no fields set, then you will have "WHERE WHERE" with nothing after it.
 
Try this - VBscript is not my language of choice so you may need to tweak it slightly:

Code: 
dim whereCondition as String = ""

if txtID.Text <> "" then
  whereCondition = " id = '" & txtID.Text & "'

if txtName.Text <> "" then
begin
  if whereCondition <> "" then
  begin
    whereCondition = whereCondition & " and"
  end

  whereCondition = whereCondition & " name = '" & txtName.Text & "'"
end

if txtSurname.Text <> "" then
begin
  if whereCondition <> "" then
  begin
    whereCondition = whereCondition & " and"
  end

  whereCondition = whereCondition & " surname = '" & txtSurname.Text & "'"
end

if whereCondition <> "" then
  whereCondition = " where" & whereCondition

sql = "SELECT * from table" & whereCondition

Be advised that this code is vulnerable to SQL injection; I would strongly advise sanitising the inputs before just throwing them into a string you're going to run against your database.
 
Roo! said:
Code: 
Dim WhereCondition as String = ""
Dim selectWord as String = "where"

if txtID.Text <> "" then
  WhereCondition = WhereCondition & selectWord & " id = '" &  txtID.Text &"'\n"
  selectWord = "and"
end if

if txtName.Text <> "" then
  WhereCondition = WhereCondition & selectWord & " name = '" &  txtName.Text &"'\n"
  selectWord = "and"
end if

if txtSurname.Text <> "" then
  WhereCondition = WhereCondition & selectWord & " surname = '" &  txtSurname.Text &"'\n"
  selectWord = "and"
end if

Dim sql as String = "SELECT * from table " & WhereCondition
Click to expand...


Send me an invoice.
Tx.
 
The_Assimilator said:
Be advised that this code is vulnerable to SQL injection; I would strongly advise sanitising the inputs before just throwing them into a string you're going to run against your database.
Click to expand...

how much damage could someone do with Select anyway?
 
This is so wrong on so many level :erm:

You may want to consider parametrising your query

In any case take a look at the ffg:

USE the COALESCE operator. I do think that there needs to some additional checking involved though.

Also check this link out on dynamic SQL
Code: 
sql = "SELECT * from table
where 
id = COALESCE( " & txtID.Text & ", id ) and
name= COALESCE(" & textName.Text & " , name) and
surname = COALESCE(" & txtSurname.Text &" , surname) "
 
Last edited:
crazy_cat said:
This is so wrong on so many level :erm:

You may want to consider parametrising your query

In any case take a look at the ffg:

USE the COALESCE operator. I do think that there needs to some additional checking involved though.

Also check this link out on dynamic SQL
Code: 
sql = "SELECT * from table
where 
id = COALESCE( " & txtID.Text & ", id ) and
name= COALESCE(" & textName.Text & " , name) and
surname = COALESCE(" & txtSurname.Text &" , surname) "
Click to expand...

Doesn't the COALESCE database dependent?
Can you use it in Ms Access or some other funny dbms?

I will try it though. Tx
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X