麻烦各位高手,关于用SqlDataSource向存储过程传递参数的问题(一解决立即结帐)!

我用GridView(以SqlDataSource作数据源)
这是我的存储过程
CREATE PROCEDURE dbo.Searchresult
@bookname nvarchar(50),
@author nvarchar(50),
@press nvarchar(50),
@ISBN nvarchar(50)
AS
select * from Product where Product_name like @bookname and Author like @author and Press like @press and ISBN like @ISBN
GO
我想把"%"+Trim(Textbox1.text)+ "%" 这样的string来作为@bookname的参数,请问代码该怎样写.好像在SqlDataSource中只能设置参数=Textbox1.text这样的形式,想了很久也没能解决.
还有就是若Textbox1.text的值为nothing时,在Sql中是否有通用字符可以代替存储过程中Product_name的值,使查询可以仅依照其它三个参数来进行.
请各位帮忙!

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

1)直接設置@bookname="'%"+Trim(Textbox1.text)+"%'.
2)若Textbox1.text為nothing時直接使用上述方法即可。這時按上述方法得出的SQL為:
Product_name like '%%'。Product_name的值將不受限制
Textbox1.text 用临时变量替换
这是我的SqlDataSource部分的代码,能否把详细的改法写给我看一下,我刚才改了还是不行.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:boyabookshopConnectionString %>" SelectCommand="Searchresult" SelectCommandType="StoredProcedure" DataSourceMode="DataReader">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="bookname" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox1"
                    Name="author" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox3" Name="press" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox4" Name="ISBN" PropertyName="Text" Type="String" />
            </SelectParameters>        </asp:SqlDataSource>
1)如2樓，用臨時變量替換
2)一般直接修改存储过程CREATE PROCEDURE dbo.Searchresult
@bookname nvarchar(50),
@author nvarchar(50),
@press nvarchar(50),
@ISBN nvarchar(50)
AS
select * from Product where Product_name like '%'+@bookname+'%' ...GO
CREATE PROCEDURE dbo.Searchresult
@bookname nvarchar(50)=null,
@author nvarchar(50)=null,
@press nvarchar(50)=null,
@ISBN nvarchar(50)=null
AS
if @bookname==null
select * from Product where  ……
else
select * from Product where Product_name like '%'+@bookname+'%' ...GO
还是显示不出结果.
这是我改之后的存储过程和.aspx的代码,高手们请帮忙看看.
CREATE PROCEDURE dbo.Searchresult
@bookname nvarchar(50)='%',
@author  nvarchar(50)='%',
@press nvarchar(50)='%',
@ISBN nvarchar(50)='%'
AS
select * from Product where Product_name like '%'+@bookname+'%' and Author like '%'+@author+'%' and Press like '%'+@press+'%' and ISBN like '%'+@ISBN+'%'
GO<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>搜索结果</title>
</head>
<body>
    <form id="form1" runat="server">
        <table border="0" cellpadding="0" cellspacing="0" style="width: 587px" align=center>
            <tr>
                <td colspan="3">
                    <uc1:Top ID="Top1" runat="server" />
                </td>
            </tr>
            <tr>
                <td colspan="3">
                    <img height="5" src="img/spacer.gif" /></td>
            </tr>
            <tr>
                <td colspan="3">
                    <table border="0" cellpadding="0" cellspacing="0" style="font-size: 11pt; width: 833px">
                        <tr>
                            <td colspan="11">
                                <hr style="border-right: #437ac5 1px solid; border-top: #437ac5 1px solid; border-left: #437ac5 1px solid;
                                    border-bottom: #437ac5 1px solid" />
                                <img src="UserControls/img/spacer.gif" /></td>
                        </tr>
                        <tr>
                            <td style="width: 3px">
                                <img height="1" src="img/spacer.gif" width="20" /></td>
                            <td style="width: 51px">
                                按书名:</td>
                            <td>
                                <asp:TextBox ID="TextBox1" runat="server" Height="13px" Width="108px"></asp:TextBox></td>
                            <td>
                                按作者:</td>
                            <td>
                                <asp:TextBox ID="TextBox2" runat="server" Height="13px" Width="108px"></asp:TextBox></td>
                            <td>
                                按出版社:</td>
                            <td>
                                <asp:TextBox ID="TextBox3" runat="server" Height="13px" Width="108px"></asp:TextBox></td>
                            <td>
                                按ISBN:</td>
                            <td style="width: 129px">
                                <asp:TextBox ID="TextBox4" runat="server" Height="13px" Width="108px"></asp:TextBox></td>
                            <td><asp:ImageButton ID="search" runat="server" ImageUrl="~/img/ss.gif" />
                            </td>
                            <td style="width: 2px">
                                <img height="1" src="UserControls/img/spacer.gif" width="20" /></td>
                        </tr>
                        <tr>
                            <td colspan="11">
                                <hr style="border-right: #437ac5 1px solid; border-top: #437ac5 1px solid; border-left: #437ac5 1px solid;
                                    border-bottom: #437ac5 1px solid" />
                                <img src="UserControls/img/spacer.gif" /></td>
                        </tr>
                    </table>
                    <img src="img/spacer.gif" /></td>
            </tr>
            <tr>
                <td colspan="3">
                    <img height="5" src="img/spacer.gif" /></td>
            </tr>
            <tr>
                <td colspan="3">
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                        Width="833px" DataSourceID="SqlDataSource1">
                        <Columns>
                            <asp:BoundField DataField="Sort_id" HeaderText="Sort_id" SortExpression="Sort_id" />
                            <asp:BoundField DataField="Hw_id" HeaderText="Hw_id" InsertVisible="False" ReadOnly="True"
                                SortExpression="Hw_id" />
                            <asp:BoundField DataField="Product_name" HeaderText="Product_name" SortExpression="Product_name" />
                            <asp:BoundField DataField="Press" HeaderText="Press" SortExpression="Press" />
                            <asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" />
                            <asp:BoundField DataField="ISBN" HeaderText="ISBN" SortExpression="ISBN" />
                            <asp:BoundField DataField="Number_sold" HeaderText="Number_sold" SortExpression="Number_sold" />
                            <asp:BoundField DataField="Original_price" HeaderText="Original_price" SortExpression="Original_price" />
                            <asp:BoundField DataField="Discountrate" HeaderText="Discountrate" SortExpression="Discountrate" />
                            <asp:BoundField DataField="Final_price" HeaderText="Final_price" ReadOnly="True"
                                SortExpression="Final_price" />
                            <asp:BoundField DataField="Introduction" HeaderText="Introduction" SortExpression="Introduction" />
                            <asp:BoundField DataField="Image" HeaderText="Image" SortExpression="Image" />
                            <asp:BoundField DataField="Record_date" HeaderText="Record_date" SortExpression="Record_date" />
                        </Columns>
                    </asp:GridView>
                    <img src="img/spacer.gif" /></td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                </td>
                <td>
                </td>
            </tr>
        </table>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:boyabookshopConnectionString %>" SelectCommand="Searchresult" SelectCommandType="StoredProcedure" DataSourceMode="DataReader">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="bookname"
                    PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox2" Name="author" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="TextBox3" Name="press" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox4" Name="ISBN" PropertyName="Text" Type="String" />
            </SelectParameters>        </asp:SqlDataSource>
    </form>
</body>
</html>
tryCREATE PROCEDURE dbo.Searchresulta
@bookname nvarchar(50),
@author  nvarchar(50),
@press nvarchar(50),
@ISBN nvarchar(50)
AS
select *
from Product
where Product_name like '%'+isnull(@bookname,'')+'%'
      and Author like '%'+isnull(@author,'')+'%'
      and Press like '%'+isnull(@press,'')+'%'
      and ISBN like '%'+isnull(@ISBN,'')+'%'
GO
CREATE PROCEDURE dbo.Searchresulta
@bookname nvarchar(50),
@author  nvarchar(50),
@press nvarchar(50),
@ISBN nvarchar(50)
AS
select *
from Product
where Product_name like '%'+isnull(@bookname,'')+'%'
      and Author like '%'+isnull(@author,'')+'%'
      and Press like '%'+isnull(@press,'')+'%'
      and ISBN like '%'+isnull(@ISBN,'')+'%'