当StaffId和Password参数值为空字串时报“不能比较和排序text,ntext和Image类型,除非使用IS NULL或Like运算符”错误!但是当参数值不为空字串时一切正常!数据库为Sql Server2000。错误的语句如下:with qry do
begin
Connection := Fdbac.DbConnection ;
Close;
Sql.Clear;
Sql.Add('Select FunctionTag as Fun from sysPositionFunction where PositionID in ');
Sql.Add('( Select SP.PositionID from hrStaffPosition SP inner join hrStaff S ') ;
Sql.Add(' On Sp.StaffId = S.StaffId where S.StaffId = :StaffId and ');
Sql.Add(' S.Password = :Password and S.Isuser = 1 )') ;
parameters.ParamByName('StaffId').Value := '';// FuserInfo^.StaffId ;
parameters.ParamByName('Password').Value := '';// FuserInfo^.Password ;
Prepared;
Open;
end; 但是使用下面的语句却正常:
Sql.Text := 'Select FunctionTag as Fun from sysPositionFunction where PositionID in( Select SP.PositionID from hrStaffPosition SP inner join hrStaff S On Sp.StaffId = S.StaffId where S.StaffId = '' and S.Password = '' and S.Isuser = 1 )' ; 请那位说明原因!
begin
Connection := Fdbac.DbConnection ;
Close;
Sql.Clear;
Sql.Add('Select FunctionTag as Fun from sysPositionFunction where PositionID in ');
Sql.Add('( Select SP.PositionID from hrStaffPosition SP inner join hrStaff S ') ;
Sql.Add(' On Sp.StaffId = S.StaffId where S.StaffId = :StaffId and ');
Sql.Add(' S.Password = :Password and S.Isuser = 1 )') ;
parameters.ParamByName('StaffId').Value := '';// FuserInfo^.StaffId ;
parameters.ParamByName('Password').Value := '';// FuserInfo^.Password ;
Prepared;
Open;
end; 但是使用下面的语句却正常:
Sql.Text := 'Select FunctionTag as Fun from sysPositionFunction where PositionID in( Select SP.PositionID from hrStaffPosition SP inner join hrStaff S On Sp.StaffId = S.StaffId where S.StaffId = '' and S.Password = '' and S.Isuser = 1 )' ; 请那位说明原因!
解决方案 »
- 请问delphi用UniDac操作mysql如何返回关键字在指定表的指定字段的第一个出现的位置
- 年终总结(二),软件皮肤(SKIN)技术
- 使用GIFImage单元,如何设置gif颜色数,急!
- DBgrid中的数据和edit中同步显示?
- 請問怎樣用ado連上同一工作組里其它電腦上的sql server
- 一个输出文字的问题!!急!!
- 怎么提高 ApplyUpdates 速度?
- 问题标签用户 【求助】c#SerialPort与Delphi的TApdComPort机制问题
- 请问如何从一个字符串中提取特定的一段文字,如:s:='hello, http://abc.com.cn ok'中提取http://abc.com.cn出来?
- Delphi VS Powerbuilder
- 如果回答Delphi串口操作的实现
- 小问题要帮忙,在新窗口的DBGRID中显示选定的字段?
你可以在错误的语句Open处打上中断,看看这时的Sql.Text和
'Select FunctionTag as Fun from sysPositionFunction where PositionID in( Select SP.PositionID from hrStaffPosition SP inner join hrStaff S On Sp.StaffId = S.StaffId where S.StaffId = '' and S.Password = '' and S.Isuser = 1 )'
是否一样
Sql.Add('Select FunctionTag as Fun');
Sql.Add(' from sysPositionFunction');
Sql.Add(' where PositionID in ');
Sql.Add('(Select SP.PositionID');
Sql.Add(' from hrStaffPosition SP inner join hrStaff S On Sp.StaffId = S.StaffId');
Sql.Add(' where S.StaffId = :StaffId');
Sql.Add(' and S.Password = :Password');
Sql.Add(' and S.Isuser = 1)') ;
使用
ParamByName('StaffId').Asstring := '';
ParamByName('Password').Asstring := '';
试试
你的parambyname会出错的。
最好是用format函数。
如:
sql.add(format('Select FunctionTag as Fun from sysPositionFunction where PositionID in( Select SP.PositionID from hrStaffPosition SP inner join hrStaff S On Sp.StaffId = S.StaffId where S.StaffId = %d and S.Password = ''%s'' and S.Isuser = 1 ) ',[FuserInfo^.StaffId ,FuserInfo^.Password]));
parameters.ParamByName('Password').Value := QuotedStr('');// FuserInfo^.Password ; 即给字符串加引号!
begin
Connection := Fdbac.DbConnection ;
Close;
Sql.Clear;
//去掉该行
// Sql.Add('Select FunctionTag as Fun from sysPositionFunction where PositionID in ');
Sql.Add(' Select SP.PositionID from hrStaffPosition SP inner join hrStaff S ') ;
Sql.Add(' On Sp.StaffId = S.StaffId where S.StaffId = :StaffId and ');
Sql.Add(' S.Password = :Password and S.Isuser = 1 ') ;
parameters.ParamByName('StaffId').Value := '';// FuserInfo^.StaffId ;
parameters.ParamByName('Password').Value := '';// FuserInfo^.Password ;
Prepared;
Open;
end;
改为以下试一下
with qry do
begin
Connection := Fdbac.DbConnection ;
Close;
Sql.Clear;
Sql.Add('Select FunctionTag as Fun from sysPositionFunction where PositionID in ( ');
Sql.Add(' Select SP.PositionID from hrStaffPosition SP inner join hrStaff S ') ;
Sql.Add(' On Sp.StaffId = S.StaffId where S.StaffId = :StaffId and ');
Sql.Add(' S.Password = :Password and S.Isuser = 1 ) ') ;
parameters.ParamByName('StaffId').Value := '';// FuserInfo^.StaffId ;
parameters.ParamByName('Password').Value := '';// FuserInfo^.Password ;
Prepared;
Open;
end;
exec sp_executesql N'select patientname from patient where patientid in
(Select Patientid From labexam where labexamsetid= @P1)
', N'@P1 text', '' //为空时,报错exec sp_executesql N'select patientname from patient where patientid in
(Select Patientid From labexam where labexamsetid= @P1)
', N'@P1 varchar(9)', '000000009' //不为空时从上可以看出再为空时,默认类型为Text,因此会报错,将参数加上类型转换函数正确
with qry do
begin
Connection := Fdbac.DbConnection ;
Close;
Sql.Clear;
Sql.Add('Select FunctionTag as Fun from sysPositionFunction where PositionID in ');
Sql.Add('( Select SP.PositionID from hrStaffPosition SP inner join hrStaff S ') ;
Sql.Add(' On Sp.StaffId = S.StaffId where S.StaffId = Convert(nvarchar(10),:StaffId) and ');
Sql.Add(' S.Password =Convert(nvarchar(10) :Password) and S.Isuser = 1 )') ;
parameters.ParamByName('StaffId').Value := userInfo^.StaffId ;
parameters.ParamByName('Password').Value := FuserInfo^.Password ;
Prepared;
Open;
end;