dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: conditional search

  1. #1
    Join Date
    Aug 2009
    Posts
    9

    conditional search

    Hi,

    I want to create a conditional search as folow but it doesn't work.

    Dim sql as string
    Dim restricao as string

    If text1(1).Text <> " " Then
    restricao1 = "([todas] like '*" & "," & Trim(text1(1).Text) & "," & "*')"
    End If
    If text1(2).Text <> " " Then
    restricao1 = " restricao and ([todas] like '*" & "," & Trim(text1(2).Text) & "," & "*')"
    End If
    If text1(3).Text <> " " Then
    restricao3 = " restricao and ([todas] like '*" & "," & Trim(text1(3).Text) & "," & "*')"
    End If
    If text1(4).Text <> " " Then
    restricao4 = " restricao and ([todas] like '*" & "," & Trim(text1(4).Text) & "," & "*')"
    End If

    restricao = restricao1 & restricao2 & restricao3 & restricao4

    sql = "SELECT * From dekafix1 " _
    & "INNER JOIN (dekafix2 INNER JOIN dekafix3 ON dekafix2.CodDekafix = dekafix3.CodDekafix) " _
    & "ON dekafix1.CodDekafix = dekafix2.CodDekafix " _
    & "WHERE restricao " _
    & "ORDER BY dekafix1.CodDekafix"

    Set rs_dekafix = db.OpenRecordset(sql, dbOpenDynaset)

    But on he other hand, if I make this way the program works

    sql = "SELECT * From dekafix1 " _
    & "INNER JOIN (dekafix2 INNER JOIN dekafix3 ON dekafix2.CodDekafix = dekafix3.CodDekafix) " _
    & "ON dekafix1.CodDekafix = dekafix2.CodDekafix " _
    & "WHERE ([todas] like '*" & "," & Trim(text1(1).Text) & "," & "*') " _
    & "and ([todas] like '*" & "," & Trim(text1(2).Text) & "," & "*') " _
    & "and ([todas] like '*" & "," & Trim(text1(3).Text) & "," & "*') " _
    & "and ([todas] like '*" & "," & Trim(text1(4).Text) & "," & "*') " _
    & "ORDER BY dekafix1.CodDekafix"

    Set rs_dekafix = db.OpenRecordset(sql, dbOpenDynaset)

    Could anybody hel me?

    Thanks a lot

    Edson

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by Edson_Abreu View Post
    But on he other hand, if I make this way the program works
    Pardon the confusion, but if it works doing it that way, then do it that way.

    If you have a solution, then I'm missing the point of the question.

  3. #3
    Join Date
    Aug 2009
    Posts
    9
    The problem is that I want to add conditionals (text1(1), text(2), text(3) and text(4)) that is not empty because now this causes errors in my program.

    Edson

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Ah...so it actually doesn't work.

    What is the error that is being thrown?

  5. #5
    Join Date
    Aug 2009
    Posts
    9
    The error showed is: Run-time rror 3075: Syntax error (missing operator) in query experssion '& restricao &'.

    Code:




    sql = "SELECT * From dekafix1 " _
    & "INNER JOIN (dekafix2 INNER JOIN dekafix3 ON dekafix2.CodDekafix = dekafix3.CodDekafix) " _
    & "ON dekafix1.CodDekafix = dekafix2.CodDekafix " _
    & "WHERE & restricao & " _
    & "ORDER BY dekafix1.CodDekafix"


    Thanks

  6. #6
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    Quote Originally Posted by Edson_Abreu View Post
    The error showed is: Run-time rror 3075: Syntax error (missing operator) in query experssion '& restricao &'.

    Code:
    Code:
       sql = "SELECT * From dekafix1 " _
       & "INNER JOIN (dekafix2 INNER JOIN dekafix3 ON dekafix2.CodDekafix = dekafix3.CodDekafix) " _
       & "ON dekafix1.CodDekafix = dekafix2.CodDekafix " _
       & "WHERE & restricao &  " _
       & "ORDER BY dekafix1.CodDekafix"
    Thanks
    Then shows us the value of restricao


  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Your WHERE clause is not syntactically correct. It should be:
    Code:
    "WHERE <fieldname> = " & restricao & " _
    That is, of course, if restricao is a number. If restricao is a string, then variable restricao would have to have single quotes around it.

  8. #8
    Join Date
    Aug 2009
    Posts
    9
    I found the solution.
    For those who want to know:


    sql = "SELECT * From dekafix1 " _
    & "INNER JOIN (dekafix2 INNER JOIN dekafix3 ON dekafix2.CodDekafix = dekafix3.CodDekafix) " _
    & "ON dekafix1.CodDekafix = dekafix2.CodDekafix " _
    & "WHERE ([todas] like '*" & "," & "*')"

    If text1(1).Text <> "" Then
    sql = sql & " and ([todas] like '*" & "," & Trim(text1(1).Text) & "," & "*')"
    End If
    If text1(2).Text <> "" Then
    sql = sql & " and ([todas] like '*" & "," & Trim(text1(2).Text) & "," & "*')"
    End If
    If text1(3).Text <> "" Then
    sql = sql & " and ([todas] like '*" & "," & Trim(text1(3).Text) & "," & "*')"
    End If
    If text1(4).Text <> "" Then
    sql = sql & " and ([todas] like '*" & "," & Trim(text1(4).Text) & "," & "*')"
    End If
    sql = sql & " ORDER BY dekafix1.CodDekafix"

    Edson

Similar Threads

  1. Customized Google Search Box
    By jabbarsb in forum ASP.NET
    Replies: 1
    Last Post: 08-20-2008, 07:50 AM
  2. Replies: 2
    Last Post: 02-07-2008, 12:37 AM
  3. Replies: 0
    Last Post: 10-11-2006, 04:10 PM
  4. Replies: 6
    Last Post: 07-08-2006, 10:48 PM
  5. Multi conditional search with SQL
    By Kara Shannon in forum ASP.NET
    Replies: 0
    Last Post: 05-01-2000, 03:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center