sqldatasource using WHERE IN problem

[dev99]

Using Studio 2010 C# - with sqldatasource querying DB2 UDB database.

I have a gridview sqldatasource that uses a WHERE condition as follows:
Select values WHERE groupname IN ('groupa','grou pb',etc.)

Works fine hardcoded in the sqldatasource but I want to create the group list in the code-behind and plug it into the SelectParameter s.

So far - I have changed my sqldatasource select statement to: Select values WHERE groupname IN ?

Next, I am building a string in the code behind as follows:
string gList = "('groupa', 'groupb')";
This is a variable list of groups from another data source.

Next, I moved the string to a Session string so that it's available throughout my session as follows:
Session["aGrps"] = gList;

Then I use the session string in my selectparameter s of the sqldatasource as follows:
<SelectParamete rs> <asp:SessionPar ameter Name="aGrps" Type="String" <SessionField=" aGrps" /> </SelectParameter s>

The sql statment does not work.
I get data if gList is only one group with no quote or paren characters as follows:
string gList = "groupa";

But when I try to add more groups in the list with or without any special characters like quotes, parens, or commas - I get no data. Not sure that's causing the problem - but then again...?

Is there a problem with using WHERE IN ? Or am I going about this all wrong? Any assistance in building this WHERE claue would be appreciated.

Thanks

[r035198x]

Determine how many parameters you are going to add, say N, before hand and add N amount of ? to the query parameters programatically . Then add the values and you should be good.

[dev99]

Thanks for the reply. I was not quite able to get your suggestion to work. I was however trying to set the Select paramters in the code behind as follows and got it partially to work:

I added onSelecting="Gr id01_Selecting" to the SqlDataSource and my WHERE clause is WHERE GROUP_NAM = @Grps

and my select parms are as follows:
<SelectParamete rs><asp:Paramet er Name="Grps" Type="String" /></SelectParameter s>

In the code-behind I have:

protected void Grid01_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
 e.Command.Parameters[0].Value = "groupA";
}

This works and the sql will return data for groupA. But I can't figure out how to have 1-n groups in the Command.Paramet er list. Is there a way to do this?

[dev99]

Sorry for not using the code tags. I will do better! Does anyone know how I can pass a string of parms from my code behind to the WHERE IN clause of my sqldatasource Select statement? thanks

[dev99]

Solution found elsewhere. thanks everyone

[Rabbit]

Can you post the solution you found for the benefit of the next person that reads this thread?

[dev99]

A solution I found for this issue is overriding the SqlDataSource SELECT statement so that the WHERE IN clause works.

In my case, I used a String.Format to build a Select statement with a formatted string of group names in my gridview_DataBo und() as follows:

SqlDataSource1. SelectCommand = String.Format(" SELECT mycolumns FROM mytables WHERE GROUP_NAME IN ({0}),Get_Group s(logged_in_use r));

Get_Groups receives the logged in user id and returns a formatted string of groups. In my case I retrieved group names from Active Directory and the returned string looked like this: "'groupa','grou pb','groupc'".

As the gridview bind takes place - the new Select statement is executed.