Query statement assistance


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Query statement assistance

  1. #1
    Join Date
    Apr 2005
    Posts
    6

    Query statement assistance

    I am new to SQL so please bare with me.
    What I am tring to achieve is a SQL query that displays results based on one category in which the rest of the query will display results based on the initial category or group. The initial category is a drop down box. Then from there I created a navigation menu with radial button in which refines the search.
    For example this is the drop down selections:

    dim unitquery as string
    if (unit.text = "ALL Model Years" ) then
    unitquery = "[Auto_year] BETWEEN" & chr(39) & "1970" & chr(39) & "AND" & chr(39) & "2005" & chr(39)
    else if (unit.text = "Mustang Only" )
    unitquery = "[model]= 'Mustang' "
    else if (unit.text = "Ranger Only" )
    unitquery = "[model]= 'Ranger' "
    else if (unit.text = "Ranger Only" )
    unitquery = "[model]= 'Explorer' "
    End if

    strSearch = gen.Text

    dbcommand = New SqlDataAdapter( "SELECT model, color, engine, navgtn_sys, timestamp " _
    & "FROM tbl_name " _
    & "WHERE ( " & unitquery & ") AND model LIKE '%" & Replace(strSearch, "'", "''") & "%'" _
    & "OR color LIKE '%" & Replace(strSearch, "'", "''") & "%'" _
    & "OR engine LIKE '%" & Replace(strSearch, "'", "''") & "%'" _
    & "OR navgtn_sys LIKE '%" & Replace(strSearch, "'", "''") & "%'",bcon )


    What happens is the farther the query is refined it displays all models not just Mustang or Ranger. This is an example not the actual data I am seeking.
    Any ideas where I can improve on this query string to seperate the queries by model as the search is refined?

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    I think the problem you're having is that your OR statements are causing you to pick up any model because they are being evaluated in conjunction with your AND statement. In other words, if the unitquery condition is true and any of the conditions that follow it are true, a row is returned.

    If you need help fixing this post a follow-up.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Apr 2005
    Posts
    6
    Thanks for the response, unfortunately I an very new to SQL and i have tried various other combinations of this query string with no luck. What could I use instead of "OR"? Should I incorporate more "if else" statements?

  4. #4
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    It seems like all of your "OR"s should be "AND"s...
    Bob Rouse
    Dimension Data

  5. #5
    Join Date
    Apr 2005
    Posts
    6
    It appears I figured it out..
    Thank you for your assistance gentleman!

  6. #6
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    So what was it? Enquiring minds want to know...
    Bob Rouse
    Dimension Data

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