asp.net和sql查询中的查询问题 [英] Problem in a query in asp.net and sql query

问题描述

我写了这个代码

我需要从下拉列表中选择一个值

或

写入文本框应该获取数据在文本框中;

i Wrote this code
where I need to choose a value from dropdownlist
or
write in a text box data should be fetched in a textbox;

<asp:GridView ID="GridView1" runat="server" 
            AutoGenerateColumns="False" DataKeyNames="ProductID" 
            DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="ProductCategory" HeaderText="ProductCategory" 
                SortExpression="ProductCategory" />
            <asp:BoundField DataField="ProductID" HeaderText="ProductID" ReadOnly="True" 
                SortExpression="ProductID" />
            <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
                SortExpression="ProductName" />
            <asp:BoundField DataField="MRP" HeaderText="MRP" SortExpression="MRP" />
            <asp:BoundField DataField="CP" HeaderText="CP" SortExpression="CP" />
            <asp:BoundField DataField="BV" HeaderText="BV" SortExpression="BV" />
        </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 
            SelectCommand="SELECT [ProductCategory], [ProductID], [ProductName], [MRP], [CP], [BV] FROM [ProductDetails] WHERE  (ProductName LIKE '%@ProductName%') or (ProductCategory = '@ProductCategory') ORDER BY [ProductName]">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="ProductCategory" 
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="TextBox2" Name="ProductName" 
                    PropertyName="Text" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>

但查询我没有给出确切的输出。

只有一个条件是一次工作。

控制回发是真的。



请建议。

but the query i not giving exact output.
only one condition is working at a time.
control postback is true.

please suggest.

推荐答案

ConnectionStrings：ConnectionString1％>
SelectCommand = SELECT [ProductCategory] ，[ProductID]，[ProductName]，[MRP]，[CP]，[BV] FROM [ProductDetails] WHERE（ProductName LIKE'％@ ProductName％'）或（ProductCategory ='@ ProductCategory'）ORDER BY [ProductName] >
< SelectParameters>
< asp：ControlParameter ControlID = DropDownList1 Name = ProductCategory
PropertyName = SelectedValue Type = String />
< asp：ControlParameter ControlID = TextBox2 Name = ProductName
PropertyName = Text Type = String />
< / SelectParameters >
< / asp：SqlDataSource >

ConnectionStrings:ConnectionString1 %>" SelectCommand="SELECT [ProductCategory], [ProductID], [ProductName], [MRP], [CP], [BV] FROM [ProductDetails] WHERE (ProductName LIKE '%@ProductName%') or (ProductCategory = '@ProductCategory') ORDER BY [ProductName]"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ProductCategory" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="TextBox2" Name="ProductName" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>

但查询我没有给出确切的输出。

只有一个条件是一次工作。

控制回发是真的。



请建议。

but the query i not giving exact output.
only one condition is working at a time.
control postback is true.

please suggest.

应该在SELECT语句或@ProductName或@ProductCategory SQLParameters中否撇号。对于@ProductName，将％放在@ProductName SQLParameter中。



现在读取：

There should be no apostrophes in the SELECT statement or in the @ProductName or @ProductCategory SQLParameters. For @ProductName, put the % within the @ProductName SQLParameter.

Now reads:

SelectCommand="SELECT [ProductCategory], [ProductID], [ProductName], [MRP], [CP], [BV] FROM [ProductDetails] WHERE  (ProductName LIKE ''%@ProductName%'') or (ProductCategory = ''@ProductCategory'') ORDER BY [ProductName]">

应阅读：

Should read:

SelectCommand="SELECT [ProductCategory], [ProductID], [ProductName], [MRP], [CP], [BV] FROM [ProductDetails] WHERE  (ProductName LIKE @ProductName) or (ProductCategory = @ProductCategory) ORDER BY [ProductName]">

ASP.NET应用程序应该使用存储过程 [ ^ ]因为性能和许多其他事情!!!



内存储过程您可以构建查询字符串：

ASP.NET application should use stored procedeures[^] because of performance and many other things!!!

Inside stored procedure you can build your query-string:

CREATE PROCEDURE GetProductsByNameOrCategory
    @ProductName NVARCHAR(100),
    @ProductCategory NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(1000)

    IF NOT @ProductName IS NULL THEN
        SET @sql = 'SELECT [ProductCategory], [ProductID], [ProductName], [MRP], [CP], [BV] FROM [ProductDetails] WHERE  (ProductName LIKE %' + @ProductName + '%)'
    ELSE
        SET @sql = 'SELECT [ProductCategory], [ProductID], [ProductName], [MRP], [CP], [BV] FROM [ProductDetails] WHERE (ProductCategory = ' + @ProductCategory + ')'

    SET @sql = @sql + ' ORDER BY [ProductName]'
    
    --if you want to see your query-string  in Messages window, uncheck below line:
    --PRINT @sql

    EXEC(@sql)

END

< br $> b $ b

如何在ASP.NET应用程序中调用存储过程 [ ^ ]

从ASP.NET和VB.NET调用存储过程 [ ^ ]

这篇关于asp.net和sql查询中的查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！