关于Delphi的一个小问题:就是如何用ADOQuery编写一个模糊查询,似乎有通配符%不太管用。谢谢了先。
请附上源码,谢谢了先
请附上源码,谢谢了先
解决方案 »
- 如何使一个exe程序(既相同程序),在同一时间内只能运行一个。
- 能判断某个菜单下有没有子菜单吗?
- rave怎样支持jpg格式的字段?
- 赶在周末休息前得到了一个星星,高兴散分!广交朋友!
- 请教关于动态控件的访问.
- 一个pascal的问题。不要笑
- 发送接收邮件系统问题!!!急救!!
- 哪位仁兄帮帮忙吧, 要有事情发生了!!!!!!
- 高分相送--------------------------高人帮看这个idSMTP 发送是否有问题?
- 如何使声卡发某一频率的声音!
- 求救:请问这个错误怎么解决:[Error]:Invalid compiler directive: 'EXTERNAL'
- 求救!!!!!!!!!!!!!!!
sqlstr :string;
begin
sqlstr :='select * from table1 where field1 like '+'''%' +trim(edit1.text) +'%'''';
adoquery.sql :=sqlstr;
adoquery.open();
end;
SQLString : string;
begin
SQLString := Format('Select * From Table1 Where Field1 Like ''%%%s%%''');
ADOQuery.SQL.Add(SQLString);
ADOQuery.Open;
end;
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ExtCtrls, ComCtrls, Grids, DBClient, DBGridEh, TFlatPanelUnit,
DBGrids, XPMenu;type
TFr_Query = class(TFrame)
FPan_Query: TPanel;
FPanel_T: TFlatPanel;
P_Grid: TPanel;
Panel57: TPanel;
Panel63: TPanel;
Edt_ZJ: TEdit;
Panel32: TPanel;
P_Czz: TPanel;
CB_CZZD: TComboBox;
CB_CZTJ: TComboBox;
EDT_CXZ: TEdit;
P_Tjlb: TPanel;
Panel60: TPanel;
Btn_Add: TButton;
Btn_Del: TButton;
Btn_Find: TButton;
Btn_Clear: TButton;
Btn_EXCEL: TButton;
Panel61: TPanel;
LV_TJ: TListView;
P_Tj: TPanel;
RG_COND: TRadioGroup;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
DBGrid1: TDBGrid;
XPMenu1: TXPMenu;
procedure Btn_AddClick(Sender: TObject);
procedure Btn_DelClick(Sender: TObject);
procedure Btn_ClearClick(Sender: TObject);
procedure Btn_FindClick(Sender: TObject);
procedure Btn_EXCELClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
procedure SetLv;
end;
// procedure Add(const Czzd,Cztj:TComboBox;Cxz:TEdit;Cond:TRadioGroup;LV:TListView);stdcall;External'D_Query.dll';
// Procedure Delete(Const Lv:TListView);stdcall;External'D_Query.dll';
// procedure Find(Const Lv:TListView;Q:TClientDataSet);stdcall;External'D_Query.dll';
// Procedure Clear(Const Lv:TListView);stdcall;External'D_Query.dll';implementation
uses U_DM_Query,U_general_print;
{$R *.dfm}procedure TFr_Query.Btn_AddClick(Sender: TObject);
var
SQL:TListItem;
begin
if (CB_CZZD.Text='')or(CB_CZTJ.Text='')or(EDT_CXZ.Text='') then
begin
Application.MessageBox('查找字段、查找条件或查找值不能为空','桂林工学院社科部资料管理系统',mb_Ok);
EDT_CXZ.SetFocus; //焦点在查找值输入框
exit; //退出此过程
end;
SQL := LV_TJ.Items.Add; //增加一条查找字段、查找条件和查找值到ListView内
if (LV_TJ.Items.Count>1) then
begin
if (RG_COND.ItemIndex=1) then
SQL.Caption := '或者' //增加查找逻辑关系值
else
SQL.Caption := '并且';
end;
SQL.SubItems.Add(CB_CZZD.Text); //追加查找字段
SQL.SubItems.Add(CB_CZTJ.Text); //追加查找条件
if CB_CZTJ.ItemIndex<>1 then
SQL.SubItems.Add(trim(EDT_CXZ.Text)) //追加查找值
else
SQL.SubItems.Add('%'+ trim(EDT_CXZ.Text)+'%'); //追加查找值
end;procedure TFr_Query.Btn_DelClick(Sender: TObject);
begin
try
lv_TJ.DeleteSelected ;
except
Application.MessageBox('请确认选中要删除的目标!',nil,mb_Ok);
end;
end;procedure TFr_Query.Btn_ClearClick(Sender: TObject);
begin
try
lv_TJ.Items.Clear;
except
end;
end;procedure TFr_Query.Btn_FindClick(Sender: TObject);
function JudgeSQL(const i:integer):string; //判断SQL语句
begin
case i of //
0: result :='select * from SKB_Book_Info where ';
1: result :='select * from SKB_Magazine_Info where ';
2: result :='select * from SKB_Archives_Info where ';//档案查询
3: result :='select * from SKB_Artick_Info where ';//文章查询
4: result :='select * from SKB_Borrower where '; //读者查询
5: result :='select distinct 书名,位置号,入库日期,书籍编号 as 登记号,出版社,count(书名) as 册数,单价,count(书名)* 单价 as 总价 from SKB_Book_Info group by 书名,单价, 入库日期,书名,书籍编号,出版社,位置号 having ';
end; // case
end;
var
No,i:integer;
str,buf1,buf2:AnsiString;
sqlstring:String;
begin
sqlstring:=JudgeSQL((Sender as TButton).Tag);
if (LV_TJ.Items.Count=0) then
begin
ShowMessage('你还没有选择数据记录查找条件,请选择');
Edt_Cxz.SetFocus; //焦点在查找值输入框
exit;
end;
dm_query.CDS_Query.Close ; //关闭数据集
dm_query.CDS_Query.CommandText :=''; //清除SQL语句 No := LV_TJ.Items.Count; //获得当前ListView对象中的记录条数
for i:=0 to No-1 do //以下所有语句都用来进行转换用户选择查找条件为SQL语句
begin
if (i>=1)then //第二条记录开始需要添加查找逻辑
begin
buf1 := LV_TJ.Items.Item[i].Caption; //通过判断添加对应的逻辑条件
if (buf1 = '并且') then
str := str+' AND '
else
str := str+' OR ';
end; buf1 := LV_TJ.Items.Item[i].SubItems.Strings[0]; //通过查找字段判断添加对应SQL语句
str:=str+buf1; buf2 := LV_TJ.Items.Item[i].SubItems.Strings[1]; //通过查找条件判断添加对应SQL语句
if (buf2 = '等于') then
str := str+'='
else if (buf2 = '不等于') then
str := str+'<>'
else if (buf2 = '大于等于') then
str := str+'>='
else if (buf2 = '小于等于') then
str := str+'<='
else if (buf2 = '模糊') then
str := str+' Like '; buf2 := LV_TJ.Items.Item[i].SubItems.Strings[2]; //通过查找值判断添加对应SQL语句
if (buf1 = '版次') or (buf1 = '单价') or (buf1 = '正文页数') or (buf1 = '附件件数') or (buf1 = '附图张数') then //整数类型字段
str := str+buf2
else str := str+''''+buf2+'''' ; end;
dm_query.CDS_Query.CommandText:=sqlstring+Str;
try
screen.Cursor :=crHourGlass;
dm_query.CDS_Query.Active :=true;
// showmessagefmt('sql :%s',[ dm_query.CDS_Query.CommandText]);
except
Application.MessageBox('无法打开数据库',nil,mb_OK);
end;
Edt_ZJ.Text :=inttostr(dm_query.CDS_Query.RecordCount );
screen.Cursor :=crDefault;
end;procedure TFr_Query.Btn_EXCELClick(Sender: TObject);
begin
Application.CreateForm(TF_Print,F_Print);
F_Print.create((Sender as TButton).Tag);
F_Print.ShowModal ;
end;
procedure TFr_Query.SetLv;
begin
self.LV_TJ.Items.Clear ;
CB_CZZD.ItemIndex :=0;
CB_CZTJ.ItemIndex :=0;
EDT_CXZ.Text :='';
Edt_ZJ.Text:='';
end;end.
adoquery.sql.clear;
adoquery.sql.add('select * from table1 where field1 like '+'''%' +trim (edit1.text) +'%''');
adoquery.open;
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ExtCtrls, ComCtrls, Grids, DBClient, DBGridEh, TFlatPanelUnit,
DBGrids, XPMenu;type
TFr_Query = class(TFrame)
FPan_Query: TPanel;
FPanel_T: TFlatPanel;
P_Grid: TPanel;
Panel57: TPanel;
Panel63: TPanel;
Edt_ZJ: TEdit;
Panel32: TPanel;
P_Czz: TPanel;
CB_CZZD: TComboBox;
CB_CZTJ: TComboBox;
EDT_CXZ: TEdit;
P_Tjlb: TPanel;
Panel60: TPanel;
Btn_Add: TButton;
Btn_Del: TButton;
Btn_Find: TButton;
Btn_Clear: TButton;
Btn_EXCEL: TButton;
Panel61: TPanel;
LV_TJ: TListView;
P_Tj: TPanel;
RG_COND: TRadioGroup;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
DBGrid1: TDBGrid;
XPMenu1: TXPMenu;
procedure Btn_AddClick(Sender: TObject);
procedure Btn_DelClick(Sender: TObject);
procedure Btn_ClearClick(Sender: TObject);
procedure Btn_FindClick(Sender: TObject);
procedure Btn_EXCELClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
procedure SetLv;
end;
// procedure Add(const Czzd,Cztj:TComboBox;Cxz:TEdit;Cond:TRadioGroup;LV:TListView);stdcall;External'D_Query.dll';
// Procedure Delete(Const Lv:TListView);stdcall;External'D_Query.dll';
// procedure Find(Const Lv:TListView;Q:TClientDataSet);stdcall;External'D_Query.dll';
// Procedure Clear(Const Lv:TListView);stdcall;External'D_Query.dll';implementation
uses U_DM_Query,U_general_print;
{$R *.dfm}procedure TFr_Query.Btn_AddClick(Sender: TObject);
var
SQL:TListItem;
begin
if (CB_CZZD.Text='')or(CB_CZTJ.Text='')or(EDT_CXZ.Text='') then
begin
Application.MessageBox('查找字段、查找条件或查找值不能为空','桂林工学院社科部资料管理系统',mb_Ok);
EDT_CXZ.SetFocus; //焦点在查找值输入框
exit; //退出此过程
end;
SQL := LV_TJ.Items.Add; //增加一条查找字段、查找条件和查找值到ListView内
if (LV_TJ.Items.Count>1) then
begin
if (RG_COND.ItemIndex=1) then
SQL.Caption := '或者' //增加查找逻辑关系值
else
SQL.Caption := '并且';
end;
SQL.SubItems.Add(CB_CZZD.Text); //追加查找字段
SQL.SubItems.Add(CB_CZTJ.Text); //追加查找条件
if CB_CZTJ.ItemIndex<>1 then
SQL.SubItems.Add(trim(EDT_CXZ.Text)) //追加查找值
else
SQL.SubItems.Add('%'+ trim(EDT_CXZ.Text)+'%'); //追加查找值
end;procedure TFr_Query.Btn_DelClick(Sender: TObject);
begin
try
lv_TJ.DeleteSelected ;
except
Application.MessageBox('请确认选中要删除的目标!',nil,mb_Ok);
end;
end;procedure TFr_Query.Btn_ClearClick(Sender: TObject);
begin
try
lv_TJ.Items.Clear;
except
end;
end;procedure TFr_Query.Btn_FindClick(Sender: TObject);
function JudgeSQL(const i:integer):string; //判断SQL语句
begin
case i of //
0: result :='select * from SKB_Book_Info where ';
1: result :='select * from SKB_Magazine_Info where ';
2: result :='select * from SKB_Archives_Info where ';//档案查询
3: result :='select * from SKB_Artick_Info where ';//文章查询
4: result :='select * from SKB_Borrower where '; //读者查询
5: result :='select distinct 书名,位置号,入库日期,书籍编号 as 登记号,出版社,count(书名) as 册数,单价,count(书名)* 单价 as 总价 from SKB_Book_Info group by 书名,单价, 入库日期,书名,书籍编号,出版社,位置号 having ';
end; // case
end;
var
No,i:integer;
str,buf1,buf2:AnsiString;
sqlstring:String;
begin
sqlstring:=JudgeSQL((Sender as TButton).Tag);
if (LV_TJ.Items.Count=0) then
begin
ShowMessage('你还没有选择数据记录查找条件,请选择');
Edt_Cxz.SetFocus; //焦点在查找值输入框
exit;
end;
dm_query.CDS_Query.Close ; //关闭数据集
dm_query.CDS_Query.CommandText :=''; //清除SQL语句 No := LV_TJ.Items.Count; //获得当前ListView对象中的记录条数
for i:=0 to No-1 do //以下所有语句都用来进行转换用户选择查找条件为SQL语句
begin
if (i>=1)then //第二条记录开始需要添加查找逻辑
begin
buf1 := LV_TJ.Items.Item[i].Caption; //通过判断添加对应的逻辑条件
if (buf1 = '并且') then
str := str+' AND '
else
str := str+' OR ';
end; buf1 := LV_TJ.Items.Item[i].SubItems.Strings[0]; //通过查找字段判断添加对应SQL语句
str:=str+buf1; buf2 := LV_TJ.Items.Item[i].SubItems.Strings[1]; //通过查找条件判断添加对应SQL语句
if (buf2 = '等于') then
str := str+'='
else if (buf2 = '不等于') then
str := str+'<>'
else if (buf2 = '大于等于') then
str := str+'>='
else if (buf2 = '小于等于') then
str := str+'<='
else if (buf2 = '模糊') then
str := str+' Like '; buf2 := LV_TJ.Items.Item[i].SubItems.Strings[2]; //通过查找值判断添加对应SQL语句
if (buf1 = '版次') or (buf1 = '单价') or (buf1 = '正文页数') or (buf1 = '附件件数') or (buf1 = '附图张数') then //整数类型字段
str := str+buf2
else str := str+''''+buf2+'''' ; end;
dm_query.CDS_Query.CommandText:=sqlstring+Str;
try
screen.Cursor :=crHourGlass;
dm_query.CDS_Query.Active :=true;
// showmessagefmt('sql :%s',[ dm_query.CDS_Query.CommandText]);
except
Application.MessageBox('无法打开数据库',nil,mb_OK);
end;
Edt_ZJ.Text :=inttostr(dm_query.CDS_Query.RecordCount );
screen.Cursor :=crDefault;
end;procedure TFr_Query.Btn_EXCELClick(Sender: TObject);
begin
Application.CreateForm(TF_Print,F_Print);
F_Print.create((Sender as TButton).Tag);
F_Print.ShowModal ;
end;
procedure TFr_Query.SetLv;
begin
self.LV_TJ.Items.Clear ;
CB_CZZD.ItemIndex :=0;
CB_CZTJ.ItemIndex :=0;
EDT_CXZ.Text :='';
Edt_ZJ.Text:='';
end;end.