Building a WHERE clause in sql

[Fuma]

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:

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.

 

[Drake2007]

Else If

but if your code already is working then so what?

 

[Fuma]

I forgot the ElseIf part

So if I have 5 more text boxes, I have to redo the combination? No man

 

[Roo!]

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

 

[Drake2007]

You could use Case instead if it's unwieldly

 

[Moederloos]

Roo! has it, except for the bug. Can you spot the bug?

Well, there are two.

 

[Roo!]

Roo! has it, except for the bug. Can you spot the bug?

Well, there are two.

And now?

 

[Drake2007]

Yeah may end up with the sql string having WHERE in it more than once.

 

[Roo!]

Yeah may end up with the sql string having WHERE in it more than once.

I disagree.

 

[Moederloos]

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.

 

[Roo!]

Ctrl+F5

 

[The_Assimilator]

Try this - VBscript is not my language of choice so you may need to tweak it slightly:

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.

 

[Fuma]

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

Send me an invoice.
Tx.

 

[Drake2007]

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.

how much damage could someone do with Select anyway?

 

[Moederloos]

how much damage could someone do with Select anyway?

Oh boy.
If you ask that, go read about the attack.

 

[crazy_cat]

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

sql = "SELECT * from table
where 
id = COALESCE( " & txtID.Text & ", id ) and
name= COALESCE(" & textName.Text & " , name) and
surname = COALESCE(" & txtSurname.Text &" , surname) "

 

[Fuma]

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

sql = "SELECT * from table
where 
id = COALESCE( " & txtID.Text & ", id ) and
name= COALESCE(" & textName.Text & " , name) and
surname = COALESCE(" & txtSurname.Text &" , surname) "

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

I will try it though. Tx

 

[crazy_cat]

For other similar functions on different DBMS see http://www.w3schools.com/SQL/sql_isnull.asp

Btw - what is a funny DBMS?

 

[Fuma]

For other similar functions on different DBMS see http://www.w3schools.com/SQL/sql_isnull.asp

Btw - what is a funny DBMS?

Informix. jk

I think I will go with Roo!bos tea's suggestion for now.

 

[Roo!]

what is a funny DBMS?

Anything that is not ANSI compliant!