Use a selected value of dropdown in SelectCommand for SqlDataSource

Question

I would like to use a selected value of a dropdown list in a SqlDataSource SelectCommand.

                <asp:DropDownList ID="ddlSelectRole" runat="server" ClientIDMode="Static">
                    <asp:ListItem></asp:ListItem>
                    <asp:ListItem>Client</asp:ListItem>
                    <asp:ListItem>Programming</asp:ListItem>
                    <asp:ListItem>Guest</asp:ListItem>
                </asp:DropDownList>

Currently:

<asp:SqlDataSource ID="dsourceProgEmails" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
    SelectCommand="SELECT  [Email] + ',' AS [text()] FROM [SiteUsers] WHERE [Role] = 'Programming' FOR XML PATH ('')">
</asp:SqlDataSource>

I'm thinking something like:

<asp:SqlDataSource ID="dsourceProgEmails" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
    SelectCommand="SELECT  [Email] + ',' AS [text()] FROM [SiteUsers] WHERE [Role] = '" + ddlSelectRole.SelectedValue + "' FOR XML PATH ('')">
</asp:SqlDataSource>

Code-behind is C#.

Write your query with passing parameter using @Role and then try this inside selectparameters tag: <asp:ControlParameter ControlID="ddlSelectRole" DefaultValue="0" Name="Role" PropertyName="SelectedValue" Type="Int32" /> — rach
– rach, Commented Jun 6, 2013 at 20:45

codingbiz · Accepted Answer · 2013-06-06 21:07:08Z

6

Your query should look like this in your SqlDataSource:

SelectCommand="SELECT [Email] + ',' AS [text] FROM [SiteUsers] WHERE [Role] = @RoleID FOR "

Then specify where to get the value for RoleID here

<SelectParameters>
   <asp:ControlParameter Name="RoleID" ControlID="ddlSelectRole" 
        PropertyName="SelectedValue" />
</SelectParameters>

edited Jun 6, 2013 at 21:07

answered Jun 6, 2013 at 20:59

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kyle_13 Over a year ago

Hi @codingbiz, I'm getting the error 'Could not find control 'ddlSelectRole' in ControlParameter 'RoleID'.' This is because the dropdown list is in an <InsertItemTemplate> of a FormView. How to fix this?

codingbiz Over a year ago

@kyle_13 the control you put in the InsertTemplate will only be visible at runtime and not in design mode. So, SqlDataSource would not be able to locate the control on the page. You should move the SqlDataSource also into the InsertTemplate.

kyle_13 Over a year ago

Hi @codingbiz, it's not letting me go further because I reference that datasource in another spot in the code-behind, in the onInserted event of a record, which sends a confirmation email out:

DataView dvProgEmails = (DataView)dsourceProgEmails.Select(DataSourceSelectArguments.Empty);     string emailsTo = (string)dvProgEmails.Table.Rows[0][0];     emailsTo = emailsTo.TrimEnd(',');

Gives me the error: Error - The name 'dsourceProgEmails' does not exist in the current context

kyle_13 Over a year ago

Hi @codingbiz, never mind, I figured it out, all I had to do was use FindControl after moving the datasource. SqlDataSource dsourceToEmails = (SqlDataSource)formViewNewItem.FindControl("dsourceToEmails"); Thanks so much, all is good now!

codingbiz Over a year ago

Cool you figured that out. I should have mentioned that in my comment.

Collectives™ on Stack Overflow

Use a selected value of dropdown in SelectCommand for SqlDataSource

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related