title: An example of advanced database search
body: <p>im looking for an example script. I saw one yesterday but for the life of me I can't find it again today.</p>
  
  <p>The task I have is to allow the user to search 1 database table via input controls on an aspx page where they can select and , or , equals to combine fields, generating the sql on the fly with concat/stringbuilder or similar. (it runs behind the corp firewall)</p>
  
  <p>Please can someone point me in the right direction of an example or tutorial</p>
  
  <p>I've been working on the page, but have run into problems. Here is the Page_load;</p>
  
  <pre><code> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      Dim sql As String = ("Select * From Table Where ")
  
      'variables to hold the and or values between fields
      Dim andor1v As String = AndOr1.SelectedValue.ToString()
      Dim andor2v As String = AndOr2.SelectedValue.ToString()
      Dim andor3v As String = AndOr3.SelectedValue.ToString()
      Dim andor4v As String = AndOr4.SelectedValue.ToString()
      Dim andor5v As String = AndOr5.SelectedValue.ToString()
      Dim andor6v As String = AndOr6.SelectedValue.ToString()
  
      'variables to stop web control inputs going direct to sql 
      Dim name As String = NameSearch.Text.ToString()
      Dim email As String = EmailSearch.Text.ToString()
      Dim city As String = CitySearchBox.Text.ToString()
      Dim province As String = ProvinceSelect.SelectedValue.ToString()
      Dim qualifications As String = QualificationsObtained.Text.ToString()
      Dim competencies As String = CompetenciesDD.SelectedValue.ToString()
      Dim expertise As String = Expertiselist.SelectedValue.ToString()
  
      If NameSearch.Text IsNot String.Empty Then
          sql += "Surname LIKE '%" &amp; name &amp; "%' "
      End If
  
      If EmailSearch.Text IsNot String.Empty Then
          sql += andor1v &amp; " Email LIKE '%" &amp; email &amp; "%' "
      End If
  
      If CitySearchBox.Text IsNot String.Empty Then
          sql += andor2v &amp; " City LIKE '%" &amp; city &amp; "%' "
      End If
  
      If QualificationsObtained.Text IsNot String.Empty Then
          sql += andor3v &amp; " (institutionquals1 LIKE '%" &amp; qualifications &amp; "%') OR " &amp; _
          "(institutionquals2 LIKE '%" &amp; qualifications &amp; "%') OR " &amp; _
          "(institutionquals3 LIKE '%" &amp; qualifications &amp; "%') OR " &amp; _
          "(institutionquals4 LIKE '%" &amp; qualifications &amp; "%') "
      End If
  
      Dim selectedrow As String = CompetenciesDD.SelectedValue.ToString
      Dim selectedquals As String = NQFlevel.SelectedValue.ToString
      If CompetenciesDD.SelectedValue.ToString IsNot "0" And selectedquals = 0 Then
          sql += (selectedrow &amp; " = 1 ")
  
      ElseIf selectedrow = "assessortrue" And selectedquals IsNot "0" Then
          sql += andor4v &amp; (" assessortrue=1  and assessorlvl=" &amp; selectedquals)
      ElseIf selectedrow = "coordinatortrue" And selectedquals IsNot "0" Then
          sql += andor4v &amp; ("coordinatortrue=1 and coordinatorlvl=" &amp; selectedquals)
      ElseIf selectedrow = "facilitatortrue" And selectedquals IsNot "0" Then
          sql += andor4v &amp; ("facilitatortrue=1 and facilitatorlvl=" &amp; selectedquals)
      ElseIf selectedrow = "moderatortrue" And selectedquals IsNot "0" Then
          sql += andor4v &amp; ("moderatortrue=1 and moderatorlvl=" &amp; selectedquals)
      ElseIf selectedrow = "productdevelopertrue" And selectedquals IsNot "0" Then
          sql += andor4v &amp; ("productdevelopertrue=1 and productdeveloperlvl=" &amp; selectedquals)
      ElseIf selectedrow = "projectmanagertrue" And selectedquals IsNot "0" Then
          sql += andor4v &amp; ("projectmanagertrue=1 and projectmanagerlvl=" &amp; selectedquals)
      End If
  
      Response.Write(sql)
  
  End Sub
  </code></pre>
  
  <p>After an hours tinkering the code is now looking as it does above ^</p>
  
  <p>Now the problem im faced with is if a user does not enter a value for surname (the first field) but does enter a value for email (or any subsequent fields), the sql produced has an extra and like this;</p>
  
  <pre><code>Select * From Table Where And Email LIKE '%test%' 
  </code></pre>
  
  <p>I'm also looking for a way to take the OR option into account. Do you think this should be done as Martin says where the whole query is either an and or an or and not a mix of the 2? Then I should be able to take out all the and/or drop downs?</p>
  
  <p>Thanks.</p>
  
  <p>NB: I'm not really looking for comments on how I should parameterise or about sql injection.</p>