Results 1 to 15 of 15

Thread: how to put more than one condetion in sql query ?

  1. #1

    Thread Starter
    Hyperactive Member xray's Avatar
    Join Date
    Jun 2003
    Location
    EgYpT
    Posts
    312

    how to put more than one condetion in sql query ?

    peace be with you

    how to make a query with more than one condetion like :

    Code:
    select * from vb6 where code_name ='xray' and 'vbforums'
    when runing this query . it gave the " xray" results only ...

    thanks
    In the name of allah , the beneficent , the merciful

    Say (O Muhammad ): We believe in allah and that which is revealed unto us and that which was revealed unto abraham and ishmael and isaac and jacob and the tribes , and that which was vouchsafed unto moses and jesus and the prophets from their lord . we make no distinction between any of them , and unto him we have surrendered


    ---- Great Sites For You -------------------

    If you want to know some small things about islam ?

  2. #2
    Addicted Member
    Join Date
    Sep 2004
    Posts
    133

    Re: how to put more than one condetion in sql query ?

    I think you want results where code_name is either xray OR vbforums:

    Code:
    SELECT 
       * 
    FROM 
       vb6 
    WHERE 
          code_name='xray'
       OR
          code_name='vbforums'

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,425

    Re: how to put more than one condetion in sql query ?

    If you want the condition to be ethier or then

    VB Code:
    1. select * from vb6 where code_name ='xray' [B][COLOR=DarkRed][I]or[/I][/COLOR][/B] code_name = 'vbforums'
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: how to put more than one condetion in sql query ?

    VB Code:
    1. Dim sSQL As String
    2. sSQL = "SELECT field1, field2, field3 FROM tablename "
    3. sSQL = sSQL & "WHERE field1 = '" & Text1.Text & "' "
    4. sSQL = sSQL & "AND field2 = '" & Text2.Text & "' "
    5. sSQL = sSQL & AND field3 = '" & Text3.Text & "' "
    6.     If Option1.Value = True Then
    7.        sSQL = sSQL & "AND field4 = '" & Text4.Text & "' "
    8.     End If
    9.  
    10.     If Check1.Value = vbChecked Then
    11.        sSQL = sSQL & "AND field5 = '" & Text5.Text & "' "
    12.     Else
    13.        sSQL = sSQL & "AND field6 = '" & Text6.Text & "' "
    14.     End If
    15. 'etc and on forever if you want

  5. #5
    Addicted Member
    Join Date
    Sep 2004
    Posts
    133

    Re: how to put more than one condetion in sql query ?

    It is also STRONGLY recommended that you NEVER concatentate your SQL string like that. You should ALWAYS use either Parameters, or better yet stored procedures.

    SQL Injection attack anyone?

  6. #6
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: how to put more than one condetion in sql query ?

    Quote Originally Posted by schenz
    It is also STRONGLY recommended that you NEVER concatentate your SQL string like that. You should ALWAYS use either Parameters, or better yet stored procedures.

    SQL Injection attack anyone?
    Store procedures for something like that?

    Ok. How do I pass the checked or unchecked value of a checkbox or option button to a stored procedure?

    If I iterate through a checked ListBox, how do I pass the ListBox selected items to a stored procedure?

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,953

    Re: how to put more than one condetion in sql query ?

    Parameters..... not to sound snarky about that, but the way we've done it is to pass the list in as a CSV string.... then parse through the string, adding each item to a temp table. Then we can join to that table or use it as part of a WHERE X IN (SELECT B from tmpTable) sort of thing.

    However, I do admit that there are times when concat SQL strings are simply the Only option.... we;ve had a couple of those, but they are so few and far inbetween, I can't think of them off the top of my head. But in those cases at least, the concat was done in the SP, not in VB.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: how to put more than one condetion in sql query ?

    Quote Originally Posted by schenz
    It is also STRONGLY recommended that you NEVER concatentate your SQL string like that. You should ALWAYS use either Parameters, or better yet stored procedures.

    SQL Injection attack anyone?
    I once came across a database that had been passed from one developer to another... and they excessively used stored procedures (because they found it easier to create views in the DB IDE then bind these to the reports rather than learning a few SQL themselves). The newest front end application suite used only around 10 to 20% of the stored procedures and tables they created... THE DATABASE WAS OVERLOADED WITH JUNK DATA. For backward compatibility with the older software and reports (in case they still needed to be run), NONE OF THOSE VIEWS WERE REMOVED... and so the DB grew and grew.

    The REALLY SAD part was they also used data bound controls... so when they needed to add fields they could just add that to the existing stored procedures cause this will change the field list in their data bound controls/objects... yes, they always show all the fields in these controls. So their SOLUTION, was to CREATE ANOTHER stored procedure that was basically the SAME only with additional fields... They could have retained the original and just created an SQL query plus the field in the source code.

    Yes parameter queries are good but how are you using them? Are you exercising restraint?

    It would be easy to create several variations of INNER JOINS to the same table (say Client table + Sales or Reservations, etc) and make each of them a stored parameter query with the SAME parameters (say Client ID). When you could have created a single param query with only important fields (such as primary key) referencing the Client table WHICH would be inner joined to other tables to create the necessary variations.
    Last edited by leinad31; Apr 21st, 2006 at 09:39 AM.

  9. #9
    Addicted Member
    Join Date
    Sep 2004
    Posts
    133

    Re: how to put more than one condetion in sql query ?

    I would love to come to see one of these websites that is built on dynamic SQL in the program rather than using SP or Parameters. I would have a field day with walking through that data.....

  10. #10
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: how to put more than one condetion in sql query ?

    I still think it would be best to create stored procs only after the usefulness and the long lasting need of the query has been tested through actual use... unlike in what I related, wherein it was the first course of action... then later on the query was abandoned for one reason or another but the stored procedure was left in the DB.

  11. #11
    Addicted Member
    Join Date
    Sep 2004
    Posts
    133

    Re: how to put more than one condetion in sql query ?

    good point. I will admit that I use parameterized queries in development, and once I have it the way I want it then I move it over as a Stored Procedure.

  12. #12
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: how to put more than one condetion in sql query ?

    Quote Originally Posted by schenz
    I would love to come to see one of these websites that is built on dynamic SQL in the program rather than using SP or Parameters. I would have a field day with walking through that data.....
    I think we need to quit hijacking xray's thread with philosophical discussions on Stored Procedures vs In-Line SQL.

    One last thought however. Who said anything about websites? We dealing with VB6 here. Nobody develops websites with VB6. For a desktop application in-line SQL is just fine.

  13. #13
    Addicted Member
    Join Date
    Sep 2004
    Posts
    133

    Re: how to put more than one condetion in sql query ?

    I would still recommend against it, unauthorized access of a desktop application may still occur.

  14. #14
    Frenzied Member SeanK's Avatar
    Join Date
    May 2002
    Location
    Boston MA
    Posts
    1,160

    Re: how to put more than one condetion in sql query ?

    Quote Originally Posted by schenz
    I would still recommend against it, unauthorized access of a desktop application may still occur.
    Piffle. I've been using in-line SQL in VB .Exes for 10 years and have never had any kind of issue.
    Beantown Boy
    Please use [highlight=vb]your code goes in here[/highlight] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.

  15. #15
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,633

    Re: how to put more than one condetion in sql query ?

    The main reason you do not concatonate SQL string like that is due to SQL Injection attacks.
    Someone could easily toast the entire DB...if you're sloppy.
    Using the ADO command object and parameters prevents SQL Injection attacks.

    SP's...these can be used if you wish, but as far as the concat discussion goes, SP's have nothing to do with it.

    WOka

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width