Can Multiple Selection Controls be use with SqlDataSource?

 NET 2.0 

I am using Visual Studio Express 2005 for database web developement.

I have created a database with 5 tables. Two are associative tables.

They are

Software
PK SoftwareID
Title

SoftwareSolution
FK SoftwareID
FK SolutionID

Solution
PK SolutionID
Title

CategorySolution
FK Category
FK Solution

Category

PK Category
Title

Criteria for a search with three sources of input into a SqlDataSource attached to a FormView for paged out.

The following are the Search Criteria input sources:
1) ListBox in Multiple Selection Mode
2) CheckBoxList with Mutiple checks posible
2) TextBox with key word search in Title.

Each Solution has 0 or many Categories and 0 or many Software.

When the records are entered in the database, the associative information for Categories and Software are populated to filter the
returned values.

In NET 1.1, I would create a search string like the following in code behind by looping through the CheckBoxList and ListBox finally adding the TextBox:

SELECT Solution.SolutionID, Solution.Title
FROM Solution
WHERE
Solution.SolutionID IN ('2','3','5')
AND
Solution.Title LIKE '%Math%'

I am unable to update the SelectCommand in the ASPX page.

However, when I use the I have used the
System.Web.UI.WebControls.SqlDataSources
"SelectQuery Builder"
I receive the following in the Source for ASPX:

 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT Solution.SolutionID, Solution.Title FROM Solution INNER JOIN CategorySolution ON Solution.SolutionID = CategorySolution.SolutionID INNER JOIN SoftwareSolution ON Solution.SolutionID = SoftwareSolution.SolutionID"> <SelectParameters> <asp:ControlParameter ControlID="CheckBoxList1" DefaultValue="%" Name="CategoryID" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="ListBox1" DefaultValue="%" Name="SoftwareID" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="txtTitleSearch" DefaultValue="%" Name="Title" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource>
 

I receive duplicate records and not sure if the code generated accesses the Mutiple Selected values in the CheckBoxList1 and ListBox1.

Question:

1) If the SelectQuery generator will not access multiple selected values from the CheckBoxList1, how can I use code behind to replace

SelectCommand in the ASPX page?

2) What is the best solution for using the CheckBoxList1, ListBox1, and TextBox for filtering my results?

 Sample code, references to solutions, corrections on logic, a new approach, how to use the wizard correctly would all be greatly appreciated.

3) Can Multiple Selection Controls be use with a SqlDataSource SELECT Command?

Thanks for your time,

Sincerely,

Unhistoric

Not, these are a summary of my unsuccessful results and may have a few unintentional syntax errors as a result of the interface of publishing on this website.

After further testing, I have not found a solution upto this point.

I have tried using a SessionParameter of type string to update ('4','20') in the following code in my ASPX file:

I set session variables in the Page_PreInit on the ASPX.VB for testing purposes.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Solution] @.inputSolutionID"> <SelectParameters> <asp:SessionParameter Name="inputSolutionID" SessionField="SolutionSearch" type="String" /> </SelectParameters></asp:SqlDataSource>

Only Works with: session("SolutionSearch") ="" resulting in unfiltered results.

   -----------------------------------------------------------

  
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Solution] WHERE ((CAST([SolutionID] AS Nvarchar)) IN (@.inputSolutionID))"> <SelectParameters> <asp:SessionParameter Name="inputSolutionID" SessionField="SolutionSearch" type="String" /> </SelectParameters></asp:SqlDataSource>
 Only Works with a single assignment: session("SolutionSearch") ="'1'" resulting in result and not with = "'1','20'"

------------------------------------------------------------- 

The following static code works:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="SELECT * FROM [Solution] WHERE ([SolutionID] IN ('4', '20'))"> </asp:SqlDataSource>

 ****Is there a way to dynamically update the proceeding from code ASPX.VB behind?**************

Sincerely frustrated,

Unhistoric

|||

The answer to the problem was resolved with the following link:

http://forums.asp.net/thread/1604679.aspx

It was pointed out the SelectCommand could be set from the code behind in the asp.vb. The key was to leave out the SelectCommand in the "ASPX" code file and set the initial SelectCommand in the Page_Load Event. From my understanding, I was not able to set the SelectCommand initially in my tests due to the fact it existed in part in the "ASPX" code.

Special Thanks to "Yani Dzhurov",

Sincerely,

Unhistoric