Asp.net SqlDataSource SelectCommand using LIKE with QueryString
<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 can't get any return results which is all because @type, if I change @type to ProductName then it works great because I want the @type value to become a dynamic value that the user can select and I will pass it using queryString. How can I solve this problem?
+3
source to share
2 answers
In this case, you can use dynamic SQL.
Change your select query to a 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 the 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
+2
source to share
If you have few fields to validate, you can 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 + '%')"
0
source to share