Results 1 to 9 of 9

Thread: IIF function

  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: IIF function

    I have a question about IIF funciton, we use vb dotnet, could you please check the follow:

    dataset = "select * from table1

    &IIF(parameters!state.value="all", "", "where state = "&
    parameters!state.value)

    Thanks for your help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The VB is wonderful, the SQL... well, not so much!
    Code:
    dataset = "SELECT * FROM table1 " _
    &   IIF(parameters!state.value="all" _
    ,      "" _
    ,      " WHERE state = " & Chr(34) & parameters!state.value) & Chr(34)
    Just an observation, but this code is vulnerable to SQL-Injection. I would STRONGLY suggest that you consider using parameterized queries instead of dynamic SQL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    Thanks. The "all" works fine. But when i select another state, it showed error message that has syntax error.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your original code should show an error message. I think that my posted code should fix that problem. Let me know if it does not.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2004
    Posts
    660
    Yes, i just tried the code you gave it to me. It still not working. Thanks again.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, please try:
    Code:
    dataset = "SELECT * FROM table1 " _
    &   IIF(parameters!state.value="all" _
    ,      "" _
    ,      " WHERE state = " & Chr(39) & parameters!state.value) & Chr(39)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Mar 2004
    Posts
    660
    Thank you very very much! It works now. Perfit. Have a great day!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Keep in mind my earlier note about SQL-Injection. I almost never write code this way anymore because it is unsafe. That is why I made a small but critical mistake because the acceptable delimiters have changed.

    I would STRONGLY recommend that you consider using parameterized queries instead of dynamic SQL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Mar 2004
    Posts
    660
    Thanks again. I got it.

Posting Permissions

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