[kmc10314]

Currently, I'm storing multiple values into a session.
(using listbox) I'm using those data to pull data from database
(values in session is used in WHERE statement)
I'm not quite sure how to retrieve data from session value
so that I can use it in sql statement.

Can you help me? I'm posting code and display page code

Session["genderList"] = genderList.Text;
        Session["hairList"] = hairList.Text;
        Session["eyeList"] = eyeList.Text;

        List<ListItem> selectedItems = new List<ListItem>();
        
        foreach (ListItem item in this.state.Items)
        {
            if (item.Selected) selectedItems.Add(item);
        }
        Session.Add("state", selectedItems);
        Response.Redirect("search_result.aspx");

Below code is for display page (Just the sql code)

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyConnection %>" 
            
            SelectCommand="SELECT [FirstName], [LastName],[Age], [Gender],[HairColor], [EyeColor], [State] FROM TalentRegistration WHERE 
ISNULL([Gender],'') = COALESCE(nullif(@Gender,''),Gender,'') 
AND ISNULL([HairColor],'') = COALESCE(nullif(@HairColor,''),hairColor,'')
AND ISNULL([EyeColor],'') = COALESCE(nullif(@EyeColor,''),EyeColor,'')
AND
( ISNULL([State],'') in( @State1, coalesce(nullif(@State1,'')))
)
">

            <SelectParameters>
                <asp:SessionParameter Name="Gender" SessionField="genderList" 
                    ConvertEmptyStringToNull="False" Type="Empty" />
                <asp:SessionParameter Name="HairColor" SessionField="hairList" 
                    ConvertEmptyStringToNull="False" Type="Empty" />
                <asp:SessionParameter Name="EyeColor" SessionField="eyeList" 
                    ConvertEmptyStringToNull="False" Type="Empty" />
                <asp:SessionParameter Name="State" SessionField="state" 
                    ConvertEmptyStringToNull="False" Type="Empty" />
            </SelectParameters>

This post has been edited by kmc10314: 21 September 2010 - 11:07 AM

[Sergio Tapia]

While I can't really help you with your problem, I can offer a suggestion that will hopefully make your code cleaner and easier to use.

Why not create a holder class to hold all of the session variables you need? Then you will a strongly typed helper class with intellisense help. Plus, you will only have to save a single class object to the Session.

[keakTheGEEK]

There are a couple things in the code that you provided that are red flags to me. The biggest issue that stands out the most is this parameter here:

<asp:SessionParameter Name="State" SessionField="state"  
ConvertEmptyStringToNull="False" Type="Empty" />  

"state" is the key for a .NET List<> object stored in your session. In the last line of your SQL statement, you have this:

( ISNULL([State],'') in( @State1, coalesce(nullif(@State1,''))))  

Is @State1 supposed to be the parameter that holds your state List<> object? If so, then you need to make sure that the names match (one is State, and the other one is State1). Also if it is, then here is another problem with this. Generally, you can't have a statement like this in SQL:

SELECT [field1], [field2], [field3] FROM table
WHERE [field2] in (@Parameter)

And have @Paramter be a .NET List<> object. MS SQL Server doesn't know what a .NET List<> object is or even know how to handle one. The IN function in MS SQL Server either expects a sub query that returns a result set or a comma separated list of values. The IN function for MS SQL Server would need to be in a form similar to this:

SELECT [field1], [field2], [field3] FROM table
WHERE [field2] in ('value1', 'value2', 'value3')

So essentially, you have 3 parameters in that list, not one. Here is how the query should look if you were to use parameters:

SELECT [field1], [field2], [field3] FROM table
WHERE [field2] in (@Param1, @Param2, @Param3)

So, you can't use a List object in .Net directly as a parameter for an SQL query. There is an additional step that you need to do to get the items in the list in the correct format in order for SQL to be able to process it.

There are a few different ways to do this. Since you already have similar code that will work, you can use that. You will just need to change it a bit. Thake this code that you wrote:

 List<ListItem> selectedItems = new List<ListItem>();  

           

        foreach (ListItem item in this.state.Items)  

         {  

            if (item.Selected) selectedItems.Add(item);  

         }  

         Session.Add("state", selectedItems);  

         Response.Redirect("search_result.aspx"); 

Instead of storing a List object of the selected items in the session, why not make it a string and put it in the right format to use as a paramter and store that in the session. So, just make these changes to the code:

        //Changed selected items to be of type string
         string selectedItems;  

           

        foreach (ListItem item in this.state.Items)  

         {  
            //generate a string in the form 'item', 'item', 'item',
            if (item.Selected) selectedItems += "'" + item + "', ";  

         }  

        //We need to peel off the last comma in the list. 
        //Use substring with the lenght of the list minus 1
         Session.Add("state", selectedItems.Substr(0, selectedItems.Length - 1));  

         Response.Redirect("search_result.aspx"); 

By doing this, we now have the session key "state" referring to a string of values that are formatted in a way that SQL knows how to processs them using the IN function... (Whew, that was a lot! I hope this helps out)

This post has been edited by keakTheGEEK: 23 September 2010 - 11:30 AM