build conditional query

[Smee]

I am trying to build a conditional sql query. that is suppose a user had 5 fields to select from and they may select any number of fields from 1 to 5 joined with 'AND'. Is there a quick and dirty method of doing this?

TIA for any replies

 

[Erel]

Something like:

Dim where As StringBuilder
where.Initialize
For Each et As EditText In ArrayOfWhereEditTexts
 If et.Text.Length > 0 Then
   If where.Length > 0 Then where.Append(" AND ")
   'The field is saved in the Tag property.
   where.Append(et.Tag).Append("=").Append(et.Text) 'add single quotes if these are strings
End If
Next
If where.Length > 0 Then where.Insert(0, " WHERE ")

 

[mc73]

Think this will bring up a syntax error, since the first 'and' comes after 'where'. We should remove the very first 'and', if of course I am right. Everything else looks very good, though not 'dirty'

 

[Erel]

Think this will bring up a syntax error, since the first 'and' comes after 'where'.

AND is only added if where.Length > 0.

 

[mc73]

AND is only added if where.Length > 0.

Ah, true, sorry! I've missed the second 'If', cause we could simply avoid it by deleting the very first 'and' at the end of our loop, after inserting all 'and's.

 

[Smee]

Gr8 thank you both. i will play around with that. it sure beats using mutiple if then else statements