none
Parameter with multiple values RRS feed

  • Question

  • Hi, I have a dataset on design mode with a tableadapter with this query:

     

    SELECT color AS article_color
    FROM articles

    WHERE  color IN (?)

     

    Then, on my form I have:

     

    Dim color AS String="'Green','Blue','Red'"

    Dim color2 AS String="Green"

    Me.ArticleTableAdapter.Fill(Me.DataSet1.Article, color)

     

    When I execute this code it dont't return me an error but it won't work at all. If i use the color2 String it works OK, but i really need a multivalue parameter to work with color! If i do manually the query on an sql processor:

     

    SELECT color AS article_color
    FROM articles

    WHERE  color IN ('Green','Blue','Red')

     

    It runs just fine!

     

    Anybody can help me?

    Thanks


     

    Friday, January 4, 2008 9:21 PM

Answers

All replies

  • It won't work like that. Since it's a parameter, it's actually escaped so it's treated as one value. Just to prove it, run a Trace in Profiler and verify what's being sent back to the server (although in this case, I can assure you it's getting sent all as one value). I wrote a piece on the KnowledgeBase that addresses this.

    Sunday, January 6, 2008 6:44 AM
  • If it is SQL Server you could do it using XML as described in my KB article

     

    http://support.microsoft.com/kb/555266/en-us

     

    Monday, January 7, 2008 11:29 AM
    Moderator
  • I am using Firebird!

    I am trying to use the William Ryan's  KnowledgeBase but no sucess, because sql server have many functions different then Firebird. (like create a temporary table or the CHARINDEX function).

    Any more ideias? I am tryuing to use this as like a report parameter, and i need that query accepts many one parameters with many values.

    Thanks anyway for your help!!

     

    Monday, January 7, 2008 9:44 PM
  • Below is a link which answers your question:

     

    http://www.firebirdfaq.org/faq138/

     

    Tuesday, January 8, 2008 1:25 PM
  • Thanks Paul, your post was helpful, it's that what i need, but they are very Brief. I am really using Firebird 2.0.
    Can you give me more directions? i am newby to stored procedures.
    Thanks
    Tuesday, January 8, 2008 2:51 PM
  • I'll take a look at the Firebird documentation, although I've never used it before and am not familiar with Interbase (the product it was derived from).

     

    Wednesday, January 9, 2008 1:46 PM
  • Thank u everybody, i just found the solution on this post:

    http://forums.microsoft.com/MSDN/showpost.aspx?postid=2647504&SiteID=1
    Tuesday, January 15, 2008 9:56 AM