Integer List parameters in a SqlDataSource

Posted on 2007-10-14
Last Modified: 2013-11-07
In ASP.NET 2.0, how do I use a SQLDataSource select parameter that is formatted as an integer list that I want to use in a SQL 'IN' statement?

Example code:
<asp:SqlDataSource ID="dsFiles" runat="server" ProviderName="System.Data.SqlClient"
    ConnectionString="<%$ ConnectionStrings:database %>"
                    SelectCommand="select * from Files
                    where fileId in (@fileIds)" >
        <asp:Parameter Name="fileIds" DefaultValue="1,2,3" />

This statement will execute, but will return with no records even though files 1, 2 and 3 exist.  Any advice?
Question by:TimDg
    LVL 14

    Expert Comment

    its becos 1,2,3 is taken as an integer value and a single quote is probably added '1,2,3' which becomes a single string
    can you change your query like
    string.format("select * from Files
                        where fileId in ({0})
                        order by srcId","1,2,3") without those select parameters...
    LVL 14

    Expert Comment

    or something like this...

    "select * from Files where fileId in (" + strDefaultvalue + ")"
    LVL 1

    Author Comment

    I need to use a select parameter for security restraints (SQL injection prevention).

    I figured that the param was being converted to '1,2,3' when passed to the database.  I also tried changing the param type to Int32, and expected an error when it tried to convert "1,2,3" to an int, but it came out with the same zero record result (no error).
    LVL 14

    Expert Comment

    hits may sound absurd...but you can give it a try
    change your @fields in the quiery to replace(@fields,'''','')
    LVL 1

    Author Comment

    It was worth a try, but no success.

    Actually, I was wrong on my errors.  I was executing the statement with an empty parameter, and was getting an empty record set.  With a correctly populated parameter, I get "Conversion failed when converting the nvarchar value '1,2,3' to data type int." from SQL Server with an untyped parameter, and "Input string was not in correct format" if I type the parameter as Int32.
    LVL 1

    Author Comment

    Actually solved this after thinking about it overnight.

    I programmatically modified the SQL and SelectParameters to add a parameters for each id in the string, rather than try to send all Ids in one parameter.

    The following snippet worked:
    string[] fileIds = fileCSVList.Split({ ',' }, 999);
    dsFiles.SelectCommand = "select * from files where fileId in (";
    for (int i = 0; i < fileIds.Length; i++)
      if (i > 0)
        dsFiles.SelectCommand += ",";
      dsFiles.SelectParameters.Add(i.ToString(), fileIds[i]);
      dsFiles.SelectCommand += "@" + i.ToString();
    dsFiles.SelectCommand += ") order by fileId";
    LVL 1

    Accepted Solution

    PAQed with points refunded (125)

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In my previous two articles we discussed Binary Serialization ( and XML Serialization ( In this article we will try to know more about SOAP (Simple Object Acces…
    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
    The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now