procedure Tf_info.cxButton1Click(Sender: TObject);
begin
if (trim(cxtextedit1.Text )='') and (trim(cxtextedit2.Text )='') and
(trim(cxtextedit3.Text )='') and (trim(cxtextedit4.Text )='')
then
begin
application.MessageBox('请输入查询条件','提示',64);
cxtextedit2.SelectAll ;
cxtextedit2.SetFocus ;
exit;
end
else
begin
with DM.ADOQuery1 do
begin
close;
sql.Clear ;
sql.Add('select itemid,itemname,itemspecs,itemunit,category,memo,date from itemaccoun where itemid like');
sql.Add(':a ');
sql.Add('and itemname like ');
sql.Add(':b ');
sql.Add('and itemspecs like');
sql.Add(':c');
sql.Add('and memo like');
sql.Add(':e');
parameters.ParamByName('a').Value :='%'+trim(cxtextedit1.Text )+'%';
parameters.ParamByName('b').Value :='%'+trim(cxtextedit2.Text )+'%';
parameters.ParamByName('c').Value :='%'+trim(cxtextedit3.Text )+'%';
parameters.ParamByName('e').Value :='%'+trim(cxtextedit4.Text )+'%'; open;
fields[0].DisplayLabel :='物料代码';//设置中文标题
fields[1].DisplayLabel :='名称';
fields[2].DisplayLabel :='规格描述';
fields[3].DisplayLabel :='单位';
fields[4].DisplayLabel :='单位';
fields[5].displaylabel :='备注';
fields[6].DisplayLabel :='创建日期';
end;
dbgrid1.Columns[0].Width :=70;//设置列宽
dbgrid1.Columns[1].Width :=110;
dbgrid1.Columns[2].Width :=200;
dbgrid1.Columns[3].Width :=40;
dbgrid1.Columns[4].Width :=60;
dbgrid1.Columns[5].Width :=90;
dbgrid1.Columns[6].Width :=100; end;end;
begin
if (trim(cxtextedit1.Text )='') and (trim(cxtextedit2.Text )='') and
(trim(cxtextedit3.Text )='') and (trim(cxtextedit4.Text )='')
then
begin
application.MessageBox('请输入查询条件','提示',64);
cxtextedit2.SelectAll ;
cxtextedit2.SetFocus ;
exit;
end
else
begin
with DM.ADOQuery1 do
begin
close;
sql.Clear ;
sql.Add('select itemid,itemname,itemspecs,itemunit,category,memo,date from itemaccoun where itemid like');
sql.Add(':a ');
sql.Add('and itemname like ');
sql.Add(':b ');
sql.Add('and itemspecs like');
sql.Add(':c');
sql.Add('and memo like');
sql.Add(':e');
parameters.ParamByName('a').Value :='%'+trim(cxtextedit1.Text )+'%';
parameters.ParamByName('b').Value :='%'+trim(cxtextedit2.Text )+'%';
parameters.ParamByName('c').Value :='%'+trim(cxtextedit3.Text )+'%';
parameters.ParamByName('e').Value :='%'+trim(cxtextedit4.Text )+'%'; open;
fields[0].DisplayLabel :='物料代码';//设置中文标题
fields[1].DisplayLabel :='名称';
fields[2].DisplayLabel :='规格描述';
fields[3].DisplayLabel :='单位';
fields[4].DisplayLabel :='单位';
fields[5].displaylabel :='备注';
fields[6].DisplayLabel :='创建日期';
end;
dbgrid1.Columns[0].Width :=70;//设置列宽
dbgrid1.Columns[1].Width :=110;
dbgrid1.Columns[2].Width :=200;
dbgrid1.Columns[3].Width :=40;
dbgrid1.Columns[4].Width :=60;
dbgrid1.Columns[5].Width :=90;
dbgrid1.Columns[6].Width :=100; end;end;
begin
if (trim(cxtextedit1.Text )='') and (trim(cxtextedit2.Text )='') and
(trim(cxtextedit3.Text )='') and (trim(cxtextedit4.Text )='') then
begin
application.MessageBox('请输入查询条件','提示',64);
cxtextedit2.SelectAll ;
cxtextedit2.SetFocus ;
exit;
end
else begin
with DM.ADOQuery1 do begin
close;
sql.Clear;
sql.Add('select itemid,itemname,itemspecs,itemunit,category,memo,date from itemaccoun where );
sql.Add(Format('itemid like ''%%%s%%''', [trim(cxtextedit1.Text)]));
sql.Add(Format('itemid like ''%%%s%%''', [trim(cxtextedit2.Text)]));
sql.Add(Format('itemid like ''%%%s%%''', [trim(cxtextedit3.Text)]));
sql.Add(Format('itemid like ''%%%s%%''', [trim(cxtextedit4.Text)]));
end;
.......
用ShowMessage把SQL显示出来看看呢
if (trim(cxtextedit1.Text )='') and (trim(cxtextedit2.Text )='') and
(trim(cxtextedit3.Text )='') and (trim(cxtextedit4.Text )='')
then
begin
application.MessageBox('请输入查询条件','提示',64);
cxtextedit2.SelectAll ;
cxtextedit2.SetFocus ;
exit;
end
else
begin
with DM.ADOQuery1 do
begin
close;
sql.Clear ;
sql.Add('select itemid,itemname,itemspecs,itemunit,category,memo,'+
'date from itemaccoun where itemid like'+
QuotedStr('%'+Trim(cxtextedit1.Text)'%')+'and itemname like'+
QuotedStr('%'+Trim(cxtextedit2.Text)'%')+'and itemspecs like'+
QuotedStr('%'+Trim(cxtextedit3.Text)'%')+'and memo like'+
QuotedStr('%'+Trim(cxtextedit4.Text)'%'));
open;
fields[0].DisplayLabel :='物料代码';//设置中文标题
fields[1].DisplayLabel :='名称';
fields[2].DisplayLabel :='规格描述';
fields[3].DisplayLabel :='单位';
fields[4].DisplayLabel :='单位';
fields[5].displaylabel :='备注';
fields[6].DisplayLabel :='创建日期';
end;
dbgrid1.Columns[0].Width :=70;//设置列宽
dbgrid1.Columns[1].Width :=110;
dbgrid1.Columns[2].Width :=200;
dbgrid1.Columns[3].Width :=40;
dbgrid1.Columns[4].Width :=60;
dbgrid1.Columns[5].Width :=90;
dbgrid1.Columns[6].Width :=100; end;
(trim(cxtextedit3.Text )='') and (trim(cxtextedit4.Text )='')
then
begin
application.MessageBox('请输入查询条件','提示',64);
cxtextedit2.SelectAll ;
cxtextedit2.SetFocus ;
exit;
end
else
begin
with DM.ADOQuery1 do
begin
close;
sql.Clear ;
sql.Add('select itemid,itemname,itemspecs,itemunit,category,memo,'+
'date from itemaccoun where itemid like'+
QuotedStr('%'+Trim(cxtextedit1.Text)+'%')+'and itemname like'+
QuotedStr('%'+Trim(cxtextedit2.Text)+'%')+'and itemspecs like'+
QuotedStr('%'+Trim(cxtextedit3.Text)+'%')+'and memo like'+
QuotedStr('%'+Trim(cxtextedit4.Text)+'%'));
open;
fields[0].DisplayLabel :='物料代码';//设置中文标题
fields[1].DisplayLabel :='名称';
fields[2].DisplayLabel :='规格描述';
fields[3].DisplayLabel :='单位';
fields[4].DisplayLabel :='单位';
fields[5].displaylabel :='备注';
fields[6].DisplayLabel :='创建日期';
end;
dbgrid1.Columns[0].Width :=70;//设置列宽
dbgrid1.Columns[1].Width :=110;
dbgrid1.Columns[2].Width :=200;
dbgrid1.Columns[3].Width :=40;
dbgrid1.Columns[4].Width :=60;
dbgrid1.Columns[5].Width :=90;
dbgrid1.Columns[6].Width :=100; end;
我觉得还是写法有问题 ,比如楼上的只用一个条件能查到某条记录
sql.Add('select itemid,itemname,itemspecs,itemunit,category,memo,date from itemaccoun where itemid like'+QuotedStr('%'+Trim(cxtextedit1.Text)+'%')) ;
如果再加别的条件就查不到了,显示空白
问题出在了字段是NULL的用组合查询差不到
比如:
select * from screencode where [itemid] like '%1601%' and [memo] like '%%'
如果[Memo]字段没有值是NULL,那么用这条语句就差不出来东西 SQL语句我已经写出来了如下
select [itemid],[itemname],[itemspecs],[itemunit],[category],[memo],[date] from itemaccoun where itemid like '%%'
and [itemname] like'%%'
and ([itemspecs] like '%%' or itemspecs is null)
and ([memo] like '%%' or memo is null)上面的sql语句如果用在DELPHI里该怎么写?
Where ......
这样写在WHERE子句中就不用判断是否为NULL了。