关于一个复合查询问题！！！help me！！ 急……………(200分)

B

badboyzhang5205

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #1

procedure TForm1.Button9Click(Sender: TObject);
var
FilterText:string;
begin
Adotable4.Close;
Adotable4.Filtered:=false;
filterText:='Sex='+ QuotedStr(combobox2.Text)
+'and'+'politics='+ QuotedStr(combobox3.Text)
+'and'+'class='+ QuotedStr(Edit3.Text)
+'and'+'major='+ QuotedStr(combobox1.Text);
AdoTable4.Filter:=FilterText;
Adotable4.Filtered:=true;
Adotable4.Open;

end;
说明： 我想用一个按钮实现单表复合查询，条件区为combobox1,combobox2,combobox3
和Edit3.数据库组件adoTable,datasourse。如果通过button9 把这几个条件并(and)起来
查询？？？上面那段是自已想的，可以通过编译，不过真正查询时出错
谁能尽快给我答复[?]

 

S

smallbs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #2

想让上面的代码能运行，请把'and' 前后的空格留出来 ' and ' []

复合查询首选的就是adoquery了
select * from tablename
where Sex=:sex
and politics=olitics
and class=:class
and major=:major
//设计期间就把上面的写进去，动态调用赋值即可
procedure TForm1.Button1Click(Sender: TObject);
begin
adoquery1.Active:=false;
adoquery1.Parameters.ParamByName('sex').Value:=combobox2.Text;
adoquery1.Parameters.ParamByName('class').Value:=Edit3.Text;
adoquery1.Parameters.ParamByName('politics').Value:=combobox3.Text;
adoquery1.Parameters.ParamByName('major').Value:=combobox1.Text;
adoquery1.Active:=true;
end;

 

X

xzh2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #3

同意smallbs
用参数出错会少些

 

来

来如风

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #4

是不是因为
filterText:='Sex='+ QuotedStr(combobox2.Text)
+'and'+'politics='+ QuotedStr(combobox3.Text)
+'and'+'class='+ QuotedStr(Edit3.Text)
+'and'+'major='+ QuotedStr(combobox1.Text);
错了？改为:='sex='''+quotedstr(combobox2.text)+''''
+'and'…………
看到有什么区别了吧？

 

B

badboyzhang5205

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #5

谢谢楼上几位！！你们的回答都非常有效。
不过那样的话那四个条件必须同时满足
如果允许其中的条件可选空值，比如combobox1里面的值可为空
而选出满足其他三个条件的查询
应该怎样做 thx!!!!!

 

S

smallbs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #6

var
str:string;
begin
str:='select * from tablename where ?=?'
// ?=? 一个固定的条件,如果没有它要判断第一个条件用where还是and,少许麻烦点
if combobox2.Text<>'' then str:=str+' and Sex='+ QuotedStr(combobox2.Text);
if combobox3.Text<>'' then str:=str+' and politics='+ QuotedStr(combobox3.Text);
if combobox1.Text<>'' then str:=str+' and major='+ QuotedStr(combobox1.Text);
if Edit3.Text<>'' then str:=str+' and class='+ QuotedStr(Edit3.Text);
adoquery1.Active:=false;
adoquery1.SQL.Clear;
adoquery1.SQL.Add(str);
adoquery1.Active:=true;
end;

 

S

shuaixin0311

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #7

combobox1里面的值可为空时，
就不用取它的值呗！

 

B

badboyzhang5205

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #8

谢谢smallbs！！！！能否提供一个用adotable的方法？

 

S

smallbs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #9

警告： 用adotable搞出来的复合查询会害死你的，至少在效率上让你难受 []
如一定要这么做
AdoTable4.Filter:=str ; //代码同上

 

B

badboyzhang5205

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #10

procedure TForm1.Button9Click(Sender: TObject);
var
str:string;
begin
Adotable4.Close;
adotable4.Filtered:=false;
str:='select * from student where Sex=:sex and politics=olitics and class=:class and major=:major';
if combobox2.Text<>'' then str:=str+' and Sex='+ QuotedStr(combobox2.Text);
if combobox3.Text<>'' then str:=str+' and politics='+ QuotedStr(combobox3.Text);
if combobox1.Text<>'' then str:=str+' and major='+ QuotedStr(combobox1.Text);
if combobox4.Text<>'' then str:=str+' and class='+ QuotedStr(combobox4.Text);
AdoTable4.Filter:=str;
Adotable4.Filtered:=true;
Adotable4.Open;
end;
是不是这样呢？运行还是有问题哦

 

S

smallbs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #11

....
var:str:
...
str:='Sex='+ QuotedStr(combobox2.Text); //combobox2.Text不为空
if combobox3.Text<>'' then str:=str+' and politics='+ QuotedStr(combobox3.Text);
if combobox1.Text<>'' then str:=str+' and major='+ QuotedStr(combobox1.Text);
if combobox4.Text<>'' then str:=str+' and class='+ QuotedStr(combobox4.Text);
........
.....

 

B

badboyzhang5205

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #12

str:='select * from student where 1=1';
是什么意思 把这句改了也不行咯。。
谢谢smallbs

 

S

smallbs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #13

上面的已经修改
强烈建议：
放个Adoquery上去，连接一下就行啦，相信不会有人会在adotable上研究复合查询的

 

B

badboyzhang5205

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-26

• #14

谢谢各位指点。。