asked on

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)" >
<SelectParameters>
<asp:Parameter Name="fileIds" DefaultValue="1,2,3" />
</SelectParameters>
</asp:SqlDataSource>

This statement will execute, but will return with no records even though files 1, 2 and 3 exist. Any advice?

Jai S

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 S

or something like this...

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

TimDg

ASKER

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).

Jai S

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

TimDg

ASKER

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.

TimDg

ASKER

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.SelectParameters.Clear();
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";

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial