CODEWITHSUNDEEP
asp.net.netsqldatasource
jax
jax

Reputation: 38653

Using Field name as select parameter in SQLDataSource

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>

Upvotes: 2

Views: 5065

Answers (3)

Damith
Damith

Reputation: 63105

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>

Upvotes: 1

codingbiz
codingbiz

Reputation: 26396

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

Upvotes: 1

codingbiz
codingbiz

Reputation: 26396

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

Upvotes: 0

Related Questions