tableadapter query RRS feed

  • Question

  • Hi

    When I execute this code in sql, it gives to me 2 records
    SELECT     cod_area_ciudad, queue
    FROM         dbo.areas
    WHERE    cod_area_ciudad IN ('mexav08aa','bogav08aa')

    I have this in the query builder of a tableadapter

    SELECT     cod_area_ciudad, queue
    FROM         dbo.areas
    WHERE     cod_area_ciudad IN (@oficinas)

    when i do click in execute query, I put the value mexav08aa,bogav08aa in the value field of query parameters but I get no records.

    How do I to use a sql query with the IN command for search 2 or more offices???

    Sunday, June 28, 2009 10:40 PM

All replies

  • You cannot pass multiple values as a parameter in IN clause. You would need to use another way to do this. I have wrote sample long time ago for the SQL Server 2000, but similar approach works for SQL Server 2005 or newer, which allow to use XML. Here is the sample

    Val Mazur (MVP)
    Monday, June 29, 2009 10:26 AM
  •  Thanks for your help,

    I used the following solution with Select method from datatable

     Me.AreasTableAdapter1.Fill(dtAreas)           'select all

     stringOficinas = ObtenerStringOficinas(oficinasIncluidas)                 '  stringOficinas.ToString = 'mexav08aa','bogav08aa'.....

     areas = dtAreas.Select(String.Format("cod_area_ciudad IN ({0})", stringOficinas.ToString))


    Tuesday, June 30, 2009 11:17 PM
  • It could work as well, but be careful about dynamic SQL. If input for IN values comes from the client, application could introduce SQL injection vulnerability. You also need to escape single quotes, if they are in your values
    Val Mazur (MVP)
    Thursday, July 2, 2009 10:19 AM