<asp:SqlDataSource ID="mySQL" runat="server"
ConnectionString="<%$ ConnectionStrings:mySQLConnectionString %>"
SelectCommand="SELECT device FROM device_table WHERE (device_id IN (@.devicelist))">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="devicelist" Type="String" />
</SelectParameters>
</asp:SqlDataSource
The problem is getting the "IN" clause of the select statement to work via the ASP page with code behind in C#. If I test the query in the page designer when configuring the SQL control by inserting 'device-1','device-2','device-3' as the value for the "devicelist" parameter, everything works as expected. However, when that same value is written programmatically into the DefaultValue of the parameter, the resulting query contains no results (as if the string contents are invalid). If I alter my program to supply a single entry (with no commas) it seems to work as well. I suspect the comma (and possibly the single-quote) characters in my string when multiple entries are involved are causing the issue.
Is there a proper technique for getting the requisite quote-delimited, comma-separated list into the SQL control command parameter correctly? Should I be using some sort of escape sequence for the single-quote and comma characters? Should I be using another "type" for the parameter; one that accepts an array of entries? I have not been able to find any examples or documentation that provides an answer.
A slight adjustment to the statements made in my earlier post: Using the "Test query" in the page design view for the control does NOT work. What does work is using SQL Server 2005 to run an equivalent query. So the page design view test query functionality and the ASP page functionality do indeed match, unlike what I stated earlier. I apologize for any confusion this may have caused.
But the problem still remains: how can I get the list of values passed into the control via the parameter?
No comments:
Post a Comment