我在做一个模糊查询，但是无论输入什么都，查询的结果都是空！怎么回事呀，希望大家能帮助我一下（小弟刚学）
protected void btn_select_Click(object sender, EventArgs e)
{
SqlConnection conn = new
SqlConnection("Server=.;database=StuManage;uid=sa;pwd=123");//创建链接数据库
conn.Open();//打开数据库
//显示
SqlCommand cmd5 = new SqlCommand("Select*from StuInfo where stuXueyuan like'%" + txt_select.Text + "%' or stuName like'%"
+ txt_select.Text + "%' or stuID like'%" + txt_select.Text + "%' or stuMajor like'%" + txt_select.Text + "%' or stuSex like'%" + txt_select.Text + "%' or stuPhone like'%" + txt_select.Text + "%'", conn);
SqlDataReader sdr1 = cmd5.ExecuteReader();
lbl.Text = "<table border='1'><tr bgcolor='#ABCDEF'><td>姓名</td><td>学号</td> <td>密码</td><td>性别</td> <td>学院</td><td>专业</td><td>班级</td><td>电话</td><td>照片</td></tr>";
while (sdr1.Read())
{
lbl.Text += "<tr>";
lbl.Text += "<td>" + sdr1["stuName"] + "</td>";
lbl.Text += "<td>" + sdr1["stuPwd"] + "</td>";
lbl.Text += "<td>" + sdr1["stuID"] + "</td>";
lbl.Text += "<td>" + sdr1["stuSex"] + "</td>";
lbl.Text += "<td>" + sdr1["stuXueyuan"] + "</td>";
lbl.Text += "<td>" + sdr1["stuMajor"] + "</td>";
lbl.Text += "<td>" + sdr1["stuClass"] + "</td>";
lbl.Text += "<td>" + sdr1["stuPhone"] + "</td>";
lbl.Text += "</tr>";
}
lbl.Text += "</Table>";
sdr1.Close();
//统计人数
string sql = "Select count(*)from StuInfo where stuXueyuan like '%" + txt_select.Text + "%' or stuName like '%"
+ txt_select.Text + "%'or stuID like '%" + txt_select.Text + "%'or stuSex like '%" + txt_select.Text + "%' or stuMajor like '%" + txt_select.Text + "%' or stuPhone like '%" + txt_select.Text + "%' ";//统计出stuID所含个数
SqlCommand cmd6 = new SqlCommand(sql, conn);
lbl_renshu.Text = cmd6.ExecuteScalar().ToString();//在这里给Label的text属性赋值
cmd6.Connection.Close();//关
}
[解决办法]
你这查询也太闹心了吧
给你个存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--[gjss] 10,20,'a','','','','','',''
ALTER procedure [dbo].[gjss]
(
@startIndex int=1,
@endIndex int=3286,
@ProductName varchar(100)=NULL,
@Inputer varchar(50)=NULL,
@ProductStandard varchar(100)=NULL,
@PriceOriginal varchar(20)=NULL,
@ISBN varchar(20)=NULL,
@CreateTimeStart varchar(20)=NULL,
@CreateTimeEnd varchar(20)=NULL,
@PrintNO varchar(20)=NULL,
@VersionNO varchar(20)=NULL
)
as
SET NOCOUNT ON
begin
declare @start int
declare @end int
declare @SqlWhere varchar(500)
declare @strSql varchar(1000)
declare @strSql1 varchar(1000)
set @start= @startIndex * @endIndex
set @end =@start+@endIndex
set @SqlWhere=' where 1=1 '
if @PrintNO<>''
begin
set @SqlWhere=@SqlWhere+' and PrintNO = '''+@PrintNO+''''
end
if @VersionNO<>''
begin
set @SqlWhere=@SqlWhere+' and VersionNO = '''+@VersionNO+''''
end
if @ProductName<>''
begin
set @SqlWhere=@SqlWhere+' and ProductName like ''%'+@ProductName+'%'''
end
if @Inputer<>''
begin
set @SqlWhere=@SqlWhere+' and Inputer like ''%'+@Inputer+'%'''
end
if @ProductStandard<>''
begin
set @SqlWhere=@SqlWhere+' and ProductStandard like ''%'+@ProductStandard+'%'''
end
if @PriceOriginal<>''
begin
set @SqlWhere=@SqlWhere+' and PriceOriginal like ''%'+@PriceOriginal+'%'''
end
if @ISBN<>''
begin
set @SqlWhere=@SqlWhere+' and ISBN = '''+@ISBN+''''
end
if @CreateTimeStart <>''
begin
set @SqlWhere=@SqlWhere+' and CreateTime >= '''+@CreateTimeStart+''''
end
if @CreateTimeEnd <>''
begin
set @SqlWhere=@SqlWhere+' and CreateTime <= '''+@CreateTimeStart+''''
end
set @strSql='select temptbl.* from (SELECT ROW_NUMBER() OVER (ORDER BY productId asc)AS Row,*
from My_Products
'
set @strSql1=')as temptbl where temptbl.Row > '+cast(@start as varchar(10)) +' and temptbl.Row<='+cast(@end as varchar(10))
set @strSql=@strSql+@SqlWhere+@strSql1
exec(@strSql)
print @strSql
set @strSql='select count(productId) from My_Products '+@SqlWhere
exec(@strSql)
print @strSql
end
[解决办法]
给你看看我做了一个简单的模糊查询。你的SQL语句很容易出错，他们说先放到数据库查询找错是个办法，你可以优化你SQL语句。
public DataTable GetShuji(string text)
{
string sql = "select * from dbo.View_1 where Title like @title";
List<SqlParameter> list = new List<SqlParameter>();
SqlParameter sp = new SqlParameter("@title",SqlDbType.VarChar);
sp.Value = "%" + text + "%";
list.Add(sp);
DataSet ds = server.GetResult(sql, list);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}
