locked
Formatting DB2 WHERE Clause for DataSource RRS feed

  • Question

  • User-2085293900 posted

    Using Studio 2010 C# with DB2 UDB 9.7.5

    I have a DataSource with a WHERE clausing using an IN operator as follows:

    select <columns> WHERE GRP_NAME IN ('GROUP1','GROUP2','GROUP3')

    The DataSource SelectCommand works fine with the list of groups hard-coded. 

    But - I am building a session string in the code-behind as follows:  Session["grpList"] = "('GROUP1','GROUP2','GROUP3')"

    When I display the Session["grpList"].ToString() in the code-behind I do in fact have ('GROUP1','GROUP2','GROUP3').

    But I do not get any data displayed when I change the WHERE clause to be

    select <columns> WHERE GRP_NAME IN

    and use SelectParameters to bring in the group list for the WHERE Clause as follows:

    <SelectParameters>

    <asp:SessionParameter Name="grpList" Type="String" SessionField="grpList" />

    </SelectParameters>

     Please advise...Thanks

    Friday, March 8, 2013 10:20 AM

Answers

  • User-2085293900 posted

    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_DataBound() as follows:

    SqlDataSource1.SelectCommand = String.Format("SELECT mycolumns FROM mytables WHERE GROUP_NAME IN ({0}),Get_Groups(logged_in_user));

    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','groupb','groupc'".

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 20, 2013 11:47 AM

All replies

  • User-2085293900 posted

    No solution yet.  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

    Thursday, March 14, 2013 9:47 AM
  • User-2085293900 posted

    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_DataBound() as follows:

    SqlDataSource1.SelectCommand = String.Format("SELECT mycolumns FROM mytables WHERE GROUP_NAME IN ({0}),Get_Groups(logged_in_user));

    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','groupb','groupc'".

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 20, 2013 11:47 AM