• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1374
  • Last Modified:

Integer List parameters in a SqlDataSource

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?
  • 3
  • 3
1 Solution
Jai STech ArchCommented:
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...
Jai STech ArchCommented:
or something like this...

"select * from Files where fileId in (" + strDefaultvalue + ")"
TimDgAuthor Commented:
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).
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Jai STech ArchCommented:
hits may sound absurd...but you can give it a try
change your @fields in the quiery to replace(@fields,'''','')
TimDgAuthor Commented:
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.
TimDgAuthor Commented:
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";
PAQed with points refunded (125)

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now