Using Field name as select parameter in SQLDataSource

Question

Is it possible to use a field name as a select parameter in an SQLDataSource?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DbConnection %>" 
    ProviderName="<%$ ConnectionStrings:DbConnection.ProviderName %>"      
SelectCommand="select * from sometable where @FieldKey = @FieldValue">
<SelectParameters>
    <asp:Parameter Name="FieldKey" Type="String" />
    <asp:Parameter Name="FieldValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

To be able to change the where condition based on a supplied field name — jax
– jax, Commented May 31, 2012 at 2:56

codingbiz · Accepted Answer · 2012-05-31 03:07:29Z

1

Here is my hack(workaround) most time

protected void page_load(...)
{
   SqlDataSource1.SelectParameters["FieldKey"] = field;
   SqlDataSource1.SelectParameters["FieldName"] = name;
}

OR create two hidden fields, assign the values to those fields and then use

<asp:ControlParameter />

That is another hack

answered May 31, 2012 at 3:07

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

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

Comments

Damith · Accepted Answer · 2012-06-01 03:17:04Z

1

OPTION 1

If FieldKey and FieldName are taking from code behind I think you can easily set command as below.

SqlDataSource1.SelectCommand = string.Format("select * from sometable where {0} = {1}",fieldKey ,fieldValue)

please validate input fields for SQL injection attacks.

OPTION 2

Create stored procedure to accept two parameters key and value, you need to build SQL query from those parameters and execute inside stored procedure.

Then you can call it from datasource.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DbConnection %>" 
    ProviderName="<%$ ConnectionStrings:DbConnection.ProviderName %>"      
    SelectCommand="StoredProcedureName"
    SelectCommandType="StoredProcedure">
<SelectParameters>
    <asp:Parameter Name="FieldKey" Type="String" />
    <asp:Parameter Name="FieldValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

edited Jun 1, 2012 at 3:17

answered May 31, 2012 at 6:38

Damith

63.2k13 gold badges105 silver badges154 bronze badges

2 Comments

jax Over a year ago

That is the way I ended up going, however, I would rather a solution straight from the .aspx page.

Damith Over a year ago

@jax add another alternative way

codingbiz · Accepted Answer · 2012-05-31 02:41:18Z

0

If what you mean is using values from a control [like textbox, dropdown] then here is something

<SelectParameters>
    <asp:ControlParameter ControlID="FieldKey" PropertyName="Text"
         Type="String" />
    <asp:ControlParameter ControlID="FieldName" PropertyName="Text"
         Type="String" />
</SelectParameters>

Assuming the values are stored in textboxes

answered May 31, 2012 at 2:41

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

1 Comment

jax Over a year ago

No, the SQL is inside a class that extends UserControl, the property is in the code behind of this class

Collectives™ on Stack Overflow

Using Field name as select parameter in SQLDataSource

3 Answers 3

Comments

OPTION 1

OPTION 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

OPTION 1

OPTION 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related