Dynamic column in ASP.Net SQL query Where clause

Question

I need to select the column in a where clause dynamically from the value of a control, Can someone help with that.

I need help with this bit:

SelectCommand="SELECT * FROM [Skills] WHERE (@YearCol = @YearValue)">

YearCol is a dropdown list, I want column in the where clause to be the selection in the dropdown list.

Here is the full code I am using.

<form id="form1" runat="server">
    <div>
    </div>
    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
        <asp:ListItem>Year1</asp:ListItem>
        <asp:ListItem>Year2</asp:ListItem>
        <asp:ListItem>Year3</asp:ListItem>
        <asp:ListItem>Year4</asp:ListItem>
        <asp:ListItem>Year5</asp:ListItem>
    </asp:DropDownList>
    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Skills] WHERE (@YearCol = @YearValue)">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" DefaultValue="Year1" Name="YearCol" PropertyName="SelectedValue" Type="String" />
            <asp:Parameter DefaultValue="1" Name="YearValue" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
</form>

You cannot use a Parameter to represent a column name. You should do it with code manually building your query based on the value selected in the DropDownList. — Steve
– Steve, Commented Jan 9, 2018 at 11:40

Aria · Accepted Answer · 2018-01-09 12:04:34Z

2

You should change it manually because parameters just use for col values not col name, so do something like:

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    string selectCommand = "SELECT * FROM [Skills] WHERE ({0} = @YearValue)";
    string yearParam = Convert.ToString(e.Command.Parameters["@YearCol"].Value);
    string yearColName = string.Empty;
    switch (yearParam)
    {
        case "Year1":
            yearColName = "Year1";//What you want
            break;
        case "Year2":
            yearColName = "Year2,Year3";
            break;
          .....
    }
    e.Command.Parameters["@YearValue"].Value = yearParam;
    SqlDataSource1.SelectCommand = string.Format(selectCommand, yearColName); 
}

Can be done in Selecting event or ddl change event or where you want .

edited Jan 9, 2018 at 12:04

answered Jan 9, 2018 at 11:57

Aria

3,8541 gold badge27 silver badges55 bronze badges

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

Comments

SANM2009 · Accepted Answer · 2018-01-09 15:15:20Z

0

I am accepting Aria's answer, but also adding my final code in case it helps someone. I just needed dropdownbox values as the Column Name in the where clause. Still surprised it is not supported through ASP.

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
{
con.Open();
string selectCommand = "SELECT * FROM [Skills] WHERE ({0} = '1')";
String yearColName = Convert.ToString(DropDownList1.SelectedItem);
SqlDataSource1.SelectCommand = string.Format(selectCommand, yearColName);
}
}

answered Jan 9, 2018 at 15:15

SANM2009

2,0082 gold badges16 silver badges31 bronze badges

Collectives™ on Stack Overflow

Dynamic column in ASP.Net SQL query Where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related