up vote 3 down vote favorite 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT ProductName, ProductPrice FROM Product WHERE (@type LIKE '%' + @seach + '%')">
<SelectParameters>
    <asp:QueryStringParameter Name="type" QueryStringField="type" />
    <asp:QueryStringParameter Name="seach" QueryStringField="search" />
</SelectParameters>
</asp:SqlDataSource>

The problem is cannot get any return results which all because @type, if I change the @type to ProductName, its works fine, because I want the @type value become dynamic value that can choose by user, and I'll pass it by using queryString. How can I solve this kind of problem?

share|improve this question
up vote 2 down vote

You can you dynamic SQL in this case.

Change the select query to stored procedure say -

CREATE PROCEDURE usp_GetData
    @type VARCHAR(100),
    @search NVARCHAR(max)
AS 
    BEGIN

        DECLARE @SQLQuery AS NVARCHAR(max)

        SET @SQLQuery = 'SELECT ProductName, ProductPrice FROM Product WHERE ( ['
            + @type + '] LIKE ''%' + @search + '%'')'

        PRINT @SQLQuery
        EXECUTE sp_executesql @sqlquery

    END

Then use above procedure to get the data

you can take a look at: http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

share|improve this answer
    
Is there any other ways that just edit in sqldatasource? – Alvis Chen Apr 7 '12 at 19:22
1  
i dont think so – Thakur Apr 7 '12 at 19:33
up vote 0 down vote

If you have few fields to check, you might use something like

"SELECT ProductName, ProductPrice FROM Product 
        WHERE (@type = 'ProductName' and ProductName LIKE '%' + @search + '%')
           OR (@type = 'ProductDescription' and ProductDescription LIKE '%' + @search + '%')
           OR (@type = 'Metadata' and Metadata LIKE '%' + @search + '%')"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.