none
VB 2005 TableAdapter and the SQL IN keyword RRS feed

  • Question

  • I have a dataset created in VB 2005 that I would like to fill with a table adapter.  This dataset is bound to a grid by the way.  I want to include the keyword IN in the select command like:  VariantID IN (@List)

    This list would be a string like "1,2,3,4,5" 

    This does not seem to work and If I create a new tableadapter and try to fill the existing dataset from it, nothing shows up.  It does not seem to fill it.

     

    What is the best way to do this?

    Thanks

    Friday, February 15, 2008 6:34 PM

All replies

  • Create a stored procedure and pass list as a string and create add a query to the table adapter for the stored procedure

    be aware of SQL injection attacks.
    Friday, February 15, 2008 11:38 PM
  • Hi,

     

    I know this is not the SQL forum but here's how I would do it. Instead of using concatenated queries (due to sql injection possible attacks) or procedures that calls sp_executesql (as it might be disabled on some sql servers or introduce security issues) I would recommend using a temporary table.

     

    Suppose you select data from a table named 'dbo.Customer' using a stored procedure named 'dbo.GetCustomers'.

        1. Declare a temporary table

        2. Split the parameter into separate values and insert them into the temporary table

        3. Select the data from the desired table and joining it with the teporary table.

     

    Code Snippet

    CREATE PROCEDURE dbo.GetCustomers(@ids NVARCHAR(1000))

    AS

    BEGIN

    -- Declare a temporary table

    DECLARE @TempTable TABLE(

    CustomerID INT

    )

    -- Split the string parameter into separate values and insert them into the temporary table

    --TODO: Split the string

    -- Here's an example on how to split the string: http://www.devx.com/tips/Tip/20009

    -- Select data using a join

    SELECT C.CustomerID FROM Customers C

        JOIN @TempTable T ON T.CustomerID = C.CustomerID

    END

     

     

     

     

    Saturday, February 16, 2008 9:41 AM
  • When I try to create just a stored procedure and pass it in a string like that, it says it can't convert the string to the expected integer type

     

    Wednesday, February 20, 2008 6:51 PM
  • Here is my article about how you could do this, but it is not using strongly typed DataTable.

     

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

    Thursday, February 21, 2008 11:07 AM
    Moderator
  • In the stored procedure,

    1) Declare a string. For example, SQL_STR VARCHAR2 (1000);

    2) Put the query in the string. For example, 

        SQL_STR := 'SELECT EMPNO, NAME FROM EMPLOYEE_DETAILS WHERE EMPNO IN '  || LIST_PARAMS ;

    3) OPEN CURSOR_LIST FOR SQL_STR;

    4) The input LIST_PARAMS your list should come in the form of  ('1', '2', '3')

     

    Thursday, February 21, 2008 11:26 AM