Results 1 to 6 of 6

Thread: SQL IN operator

  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Question Unanswered: SQL IN operator

    The code in VB.NET:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE (state IN :PARAM1)"

    In a web browser, user will need to type state.
    Here I tried to enter
    ('CA', 'MN') or
    'CA', 'MN' or
    CA, MN
    I got no result.
    But if I entered only CA (not 'CA'), it worked.

    Can somebody help?
    Thanks.

  2. #2
    Join Date
    Feb 2003
    Location
    San Antonio, TX
    Posts
    31

    Re: SQL IN operator

    Originally posted by flc
    The code in VB.NET:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE (state IN :PARAM1)"

    In a web browser, user will need to type state.
    Here I tried to enter
    ('CA', 'MN') or
    'CA', 'MN' or
    CA, MN
    I got no result.
    But if I entered only CA (not 'CA'), it worked.

    Can somebody help?
    Thanks.
    Did you try entering the following?
    (CA, MN)

    You might also try changing the select statement to:
    "SELECT authors FROM a WHERE state IN (:PARAM1)"

    That may not help but it shouldn't hurt.
    Ralph D. Wilson II
    email: rwilson@thewizardsguild.com
    URL: http://thewizardsguild.com

    "Any sufficiently advanced technology is indistinguishable from magic." A.C. Clark

  3. #3
    Join Date
    Apr 2004
    Posts
    5

    Re: SQL IN operator

    yeah..i've tried those two..
    still doesn't work.
    thanks anyway


    Originally posted by RDWilson2
    Did you try entering the following?
    (CA, MN)

    You might also try changing the select statement to:
    "SELECT authors FROM a WHERE state IN (:PARAM1)"

    That may not help but it shouldn't hurt.

  4. #4
    Join Date
    Feb 2003
    Location
    San Antonio, TX
    Posts
    31

    Re: SQL IN operator

    Originally posted by flc
    yeah..i've tried those two..
    still doesn't work.
    thanks anyway
    Sounds to me like it may be time to add a bit more code to the processing. You could let them put in, for instance,

    CA, NV, CO

    and, in the code, check for a comma in the string and, based on that, create the full SQL statement.

    For example:

    If there is a comma:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE state IN (" & :PARAM1 &") "

    If there is no comma:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE state = " & :PARAM1

    Of course, you may need to adjust the actual phrasing of the code.
    Ralph D. Wilson II
    email: rwilson@thewizardsguild.com
    URL: http://thewizardsguild.com

    "Any sufficiently advanced technology is indistinguishable from magic." A.C. Clark

  5. #5
    Join Date
    Apr 2004
    Posts
    5

    Re: SQL IN operator

    But how can I add parameter in code if I need to add unknown number of states?
    Me.OracleSelectCommand1.Parameters.Add(":PARAM1", OracleType.VarChar, 255, "state"))
    The Add.Value should be "CA", "NV", "CO" respectively, not a whole string.

    Thanks.


    Originally posted by RDWilson2
    Sounds to me like it may be time to add a bit more code to the processing. You could let them put in, for instance,

    CA, NV, CO

    and, in the code, check for a comma in the string and, based on that, create the full SQL statement.

    For example:

    If there is a comma:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE state IN (" & :PARAM1 &") "

    If there is no comma:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE state = " & :PARAM1

    Of course, you may need to adjust the actual phrasing of the code.

  6. #6
    Join Date
    Feb 2003
    Location
    San Antonio, TX
    Posts
    31

    Clarification

    Let me clarify a bit here:

    I had used the ":Param1" as, in effect a place holder and not as an actual SQL parameter. Let's assume that the text field where the user enters the stae or states is "YourField", then you would actually code something like:

    If there is a comma:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE state IN (" & YourField.text &") "

    If there is no comma:
    Me.OracleSelectCommand1.CommandText = "SELECT authors FROM a WHERE state = " & YourField.text

    In other words, build the whole SQL statement in yoru code. Admittedly, this is a little less efficient than using a parameterized SQL statement but there comes a time when one sacrifices absolute effeiciency for "works good enough and is easier to code".
    Ralph D. Wilson II
    email: rwilson@thewizardsguild.com
    URL: http://thewizardsguild.com

    "Any sufficiently advanced technology is indistinguishable from magic." A.C. Clark

Posting Permissions

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