这是一个根据图书的书名、作者、译者、以及出版商查询图书的相关信息。代码可以运行,但是达不到查询的目的,无论书名、作者、译者、以及出版商的文本框输入任意值或者都为空时,点击查询按钮时都会显示全部的图书信息。找来找去都找不出什么问题来,麻烦大家了~~
存储过程:
alter procedure SearchBook
@Name VarChar(100),
@Author VarChar(30),
@Translator VarChar(30),
@Publisher VarChar(100)
as
select * from Book where Name like '%'+@Name+'%' or Author like '%'+@Author+'%' or Translator like '%'+@Translator+'%' or Publisher like '%'+@Publisher+'%'
GO
后台代码:
//获取连接数据库字符串
string strconn = ConfigurationManager.ConnectionStrings["connectionStr"].ToString();
//连接数据库
SqlConnection conn = new SqlConnection(strconn);
conn.Open();
SqlCommand cmd = new SqlCommand("SearchBook", conn);
//设置sql命令变量类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;
//清掉公用类中上次的使用的参数
cmd.Parameters.Clear();
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@Author",text_author.Text as object),
new SqlParameter("@Name",text_name.Text as object),
new SqlParameter ("@Translator",text_translator.Text as object),
new SqlParameter ("@Publisher",text_publisher.Text as object)
};
cmd.Parameters.AddRange(paras);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
Repeater1.DataSource = dt;
Repeater1.DataBind();
存储过程:
alter procedure SearchBook
@Name VarChar(100),
@Author VarChar(30),
@Translator VarChar(30),
@Publisher VarChar(100)
as
select * from Book where Name like '%'+@Name+'%' or Author like '%'+@Author+'%' or Translator like '%'+@Translator+'%' or Publisher like '%'+@Publisher+'%'
GO
后台代码:
//获取连接数据库字符串
string strconn = ConfigurationManager.ConnectionStrings["connectionStr"].ToString();
//连接数据库
SqlConnection conn = new SqlConnection(strconn);
conn.Open();
SqlCommand cmd = new SqlCommand("SearchBook", conn);
//设置sql命令变量类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;
//清掉公用类中上次的使用的参数
cmd.Parameters.Clear();
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@Author",text_author.Text as object),
new SqlParameter("@Name",text_name.Text as object),
new SqlParameter ("@Translator",text_translator.Text as object),
new SqlParameter ("@Publisher",text_publisher.Text as object)
};
cmd.Parameters.AddRange(paras);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
Repeater1.DataSource = dt;
Repeater1.DataBind();
解决方案 »
- SqlParameter[]parameters与IDataParameter[] parameters区别
- SSO单点登录的问题
- 超时时间已到。在操作完成之前超时时间已过或服务器未响应。
- 页面重定向之前,显示提示窗口的问题? 急!
- asp.net2.0+c#上传图片的时候,如果图片的宽度或者高度超过特定的值,如何压缩图片啊?
- VS2005用的超熟的进来看看!!!关于gridview自带的分页的样式问题????
- web自定义控件动态设置
- 请教关于自定义空间的问题
- 探讨三层结构的具体实现
- javascript里有没有能检验某一控件value的类型的方法?
- 急!!!用WebbUpload上传文件在 iis7 没有反应
- WebClient.UploadStringAsync(Uri, String, String, Object) 中的object是怎么用的?救解……
set @sql = 'select * from Book where Name like'+'''%'+rtrim(@Name)+'%'''
set @sql = 'select * from Book where Name like '+'''%'+rtrim(@Name)+'%'''
EXEC (@sql)
@Name VarChar(100),
@Author VarChar(30),
@Translator VarChar(30),
@Publisher VarChar(100)
as
declare @sql varchar(500)
set @sql = 'select * from Book where Name like ''%'+@Name+'%'' or Author like ''%'+
@Author+'%'' or Translator like ''%'+@Translator+'%'' or Publisher like ''%'+@Publisher+'%'''exec(@sql)
GO
---------------------------
原因还是挺简单的,因你的SQL语句:
select * from Book where Name like '%'+@Name+'%' or Author like '%'+@Author+'%' or Translator like '%'+@Translator+'%' or Publisher like '%'+@Publisher+'%'当有值为空时(如:@Name),结果会是:
select * from Book where Name like '%%' or Author like '%'+@Author+'%' or Translator like '%'+@Translator+'%' or Publisher like '%'+@Publisher+'%'“Name like '%%'”是永远都为True的,这样就返回所有的记录了。---------------------------
如果当条件为空时,不想返回所有记录,可以这样做:
DECLARE @sql NVARCHAR(1000)
SET @sql = 'select * from Book WHERE 1=1 '
IF( LEN(@Name) > 0 ) SET @sql = sql + ' AND Name LIKE ''%'+ @name +'%'''
--其它参数类似
exec( @sql )
应该是
alter procedure SearchBook
@Name VarChar(100),
@Author VarChar(30),
@Translator VarChar(30),
@Publisher VarChar(100)
as
select * from Book where Name like '%'+@Name+'%' and Author like '%'+@Author+'%'
and Translator like '%'+@Translator+'%' and Publisher like '%'+@Publisher+'%'
GO你把 or 都改成 and 应该就可以了