ADO方式连接数据库--添删查修

时间：2022-03-14 03:37

程序界面：

gxlsystem.com,布布扣

程序源码：

  1 unit mydb;
  2 
  3 interface
  4 
  5 uses
  6   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  7   Dialogs, DB, DBTables, StdCtrls, Grids, DBGrids, ADODB, ExtCtrls, DBCtrls,
  8   Mask;
  9 
 10 type
 11   TForm1 = class(TForm)
 12     DBGrid1: TDBGrid;
 13     ADOConnection1: TADOConnection;
 14     ADOQuery1: TADOQuery;
 15     DataSource1: TDataSource;
 16     DBNavigator1: TDBNavigator;
 17     ADOQuery1name: TStringField;
 18     ADOQuery1age: TIntegerField;
 19     ADOQuery1address: TStringField;
 20     Button1: TButton;
 21     Edit1: TEdit;
 22     Button2: TButton;
 23     Label1: TLabel;
 24     ADOQuery1stucode: TWideStringField;
 25     ADOQuery1sex: TStringField;
 26     ADOQuery1province: TStringField;
 27     ADOQuery1phonenumber: TStringField;
 28     ComboBox1: TComboBox;
 29     Label2: TLabel;
 30     Label3: TLabel;
 31     ComboBox2: TComboBox;
 32     Panel1: TPanel;
 33     ADOQuery2: TADOQuery;
 34     Panel2: TPanel;
 35     Edit2: TEdit;
 36     Edit3: TEdit;
 37     Edit4: TEdit;
 38     Edit5: TEdit;
 39     Edit6: TEdit;
 40     Label4: TLabel;
 41     Label5: TLabel;
 42     Label6: TLabel;
 43     Label7: TLabel;
 44     Label8: TLabel;
 45     Label9: TLabel;
 46     Edit7: TEdit;
 47     Label10: TLabel;
 48     Button3: TButton;
 49     RadioGroup1: TRadioGroup;
 50     Button4: TButton;
 51     Button5: TButton;
 52     Button6: TButton;
 53     procedure Button1Click(Sender: TObject);
 54     procedure Button2Click(Sender: TObject);
 55     procedure ComboBox1DropDown(Sender: TObject);
 56     procedure ComboBox2DropDown(Sender: TObject);
 57     procedure Button3Click(Sender: TObject);
 58     procedure displayAll;
 59     procedure DBGrid1CellClick(Column: TColumn);
 60     procedure Button4Click(Sender: TObject);
 61     procedure Button5Click(Sender: TObject);
 62     procedure Button6Click(Sender: TObject);
 63 
 64   private
 65     { Private declarations }
 66   public
 67     { Public declarations }
 68   end;
 69 
 70 var
 71   Form1: TForm1;
 72 
 73 implementation
 74 
 75 {$R *.dfm}
 76 
 77 procedure TForm1.Button1Click(Sender: TObject);
 78 var
 79    sqlstr:string;
 80 begin
 81   ADOQuery1.Close;
 82   ADOQuery1.SQL.Clear;
 83   sqlstr:=‘select * from student where 1=1‘;
 84   //ADOQuery1.SQL.Add(‘select * from student where  name like :name  order by stucode‘);
 85   //ADOQuery1.Parameters.ParamByName(‘name‘).Value:=‘%‘+Edit1.Text+‘%‘;
 86 
 87 
 88  if  Edit1.Text<>‘‘ then
 89   begin
 90     sqlstr:=sqlstr+‘ and name like ‘‘%‘+trim(Edit1.Text)+‘%‘‘ ‘;
 91   end;
 92 
 93   if  ComboBox1.Text<>‘‘then
 94   begin
 95      sqlstr:=sqlstr+‘ and province=‘‘‘+trim(ComboBox1.Text)+‘‘‘‘;
 96   end;
 97 
 98    if  ComboBox2.Text<>‘‘then
 99   begin
100      sqlstr:=sqlstr+‘ and address=‘‘‘+trim(ComboBox2.Text)+‘‘‘‘;
101   end;
102   ADOQuery1.SQL.Add(sqlstr+‘ order by stucode‘);
103   ADOQuery1.open;
104 
105   Panel1.Caption:=‘共‘+ IntToStr(ADOQuery1.RecordCount)+‘条数据‘;
106 
107 
108 end;
109 
110 procedure TForm1.Button2Click(Sender: TObject);
111 
112 begin
113   displayAll;
114   Panel1.Caption:=‘共‘+ IntToStr(ADOQuery1.RecordCount)+‘条数据‘;
115 end;
116 
117 
118 
119 procedure TForm1.ComboBox1DropDown(Sender: TObject);
120 
121 begin
122   ComboBox1.Items.Clear;
123   ADOQuery2.Close;
124   ADOQuery2.SQL.Clear;
125   ADOQuery2.SQL.Add(‘select distinct province from student‘);
126   ADOQuery2.open;
127   while not ADOQuery2.Eof do
128   begin
129     ComboBox1.Items.Add(ADOQuery2.FieldByName(‘province‘).AsString);
130     ADOQuery2.Next;
131   end;
132 end;
133 
134 
135 procedure TForm1.ComboBox2DropDown(Sender: TObject);
136 
137 begin
138   ComboBox2.Items.Clear;
139   ADOQuery2.Close;
140   ADOQuery2.SQL.Clear;
141   ADOQuery2.SQL.Add(‘select distinct address from student‘);
142   ADOQuery2.open;
143   while not ADOQuery2.Eof do
144   begin
145     ComboBox2.Items.Add(ADOQuery2.FieldByName(‘address‘).AsString);
146     ADOQuery2.Next;
147   end;
148 end;
149 
150 procedure TForm1.Button3Click(Sender: TObject);
151 
152 begin
153 //ShowMessage(IntToStr(RadioGroup1.ItemIndex));
154 //ShowMessage(RadioGroup1.Items[RadioGroup1.ItemIndex]);
155 
156   ADOQuery1.Close;
157   ADOQuery1.SQL.Clear;
158   ADOQuery1.SQL.Add(‘insert into student (stucode,name,sex,age,province,address,phonenumber) values(:stucode,:name,:sex,:age,:province,:address,:phonenumber)‘);
159   ADOQuery1.Parameters.ParamByName(‘stucode‘).Value:=Trim(Edit2.Text);
160   ADOQuery1.Parameters.ParamByName(‘name‘).Value:=Trim(Edit3.Text);
161   ADOQuery1.Parameters.ParamByName(‘sex‘).Value:=Trim(RadioGroup1.Items[RadioGroup1.ItemIndex]);
162   ADOQuery1.Parameters.ParamByName(‘age‘).Value:=Trim(Edit4.Text);
163   ADOQuery1.Parameters.ParamByName(‘province‘).Value:=Trim(Edit5.Text);
164   ADOQuery1.Parameters.ParamByName(‘address‘).Value:=Trim(Edit6.Text);
165   ADOQuery1.Parameters.ParamByName(‘phonenumber‘).Value:=Trim(Edit7.Text);
166   ADOQuery1.ExecSQL;
167   //ShowMessage(‘保存成功！‘);
168   displayAll;     //显示所有记录
169   Button6.Click;
170 end;
171 
172 procedure TForm1.displayAll;
173 begin
174   ADOQuery1.Close;
175   ADOQuery1.SQL.Clear;
176   ADOQuery1.SQL.Add(‘select * from student order by stucode‘);
177   ADOQuery1.open;
178 end;
179 
180 procedure TForm1.DBGrid1CellClick(Column: TColumn);
181 begin
182   // s:=query1.fieldbyname(‘name‘).asstring;
183  //ShowMessage(ADOQuery1.fieldbyname(‘stucode‘).asstring);
184    
185   //ShowMessage(adoquery1.fieldbyname(‘name‘).AsString);
186   Edit2.Text:=ADOQuery1.fieldbyname(‘stucode‘).AsString;
187   Edit3.Text:=ADOQuery1.fieldbyname(‘name‘).AsString;
188   if Trim(ADOQuery1.fieldbyname(‘sex‘).AsString)=‘男‘ then
189       RadioGroup1.ItemIndex:=0
190    else
191       RadioGroup1.ItemIndex:=1;
192 
193   Edit4.Text:=ADOQuery1.fieldbyname(‘age‘).AsString;
194   Edit5.Text:=ADOQuery1.fieldbyname(‘province‘).AsString;
195   Edit6.Text:=ADOQuery1.fieldbyname(‘address‘).AsString;
196   Edit7.Text:=ADOQuery1.fieldbyname(‘phonenumber‘).AsString;
197   Edit2.Enabled:=False;
198 end;
199 
200 procedure TForm1.Button4Click(Sender: TObject);
201 begin
202   ADOQuery1.Close;
203   ADOQuery1.SQL.Clear;
204   ADOQuery1.SQL.Add(‘update student set name=:name ,sex=:sex,age=:age,province=:province,address=:address,phonenumber=:phonenumber  where stucode=:stucode‘);
205   ADOQuery1.Parameters.ParamByName(‘name‘).Value:=Trim(Edit3.Text);
206   ADOQuery1.Parameters.ParamByName(‘sex‘).Value:=Trim(RadioGroup1.Items[RadioGroup1.ItemIndex]);
207   ADOQuery1.Parameters.ParamByName(‘age‘).Value:=Trim(Edit4.Text);
208   ADOQuery1.Parameters.ParamByName(‘province‘).Value:=Trim(Edit5.Text);
209   ADOQuery1.Parameters.ParamByName(‘address‘).Value:=Trim(Edit6.Text);
210   ADOQuery1.Parameters.ParamByName(‘phonenumber‘).Value:=Trim(Edit7.Text);
211   ADOQuery1.Parameters.ParamByName(‘stucode‘).Value:=Trim(Edit2.Text);
212   ADOQuery1.ExecSQL;
213   //ShowMessage(‘修改成功！‘);
214   displayAll;     //显示所有记录
215 
216 end;
217 
218 procedure TForm1.Button5Click(Sender: TObject);
219 begin
220   DBGrid1.SelectedRows.Delete;
221 end;
222 
223 procedure TForm1.Button6Click(Sender: TObject);
224 begin
225  Edit2.Text:=‘‘;
226  Edit3.Text:=‘‘;
227  Edit4.Text:=‘‘;
228  Edit5.Text:=‘‘;
229  Edit6.Text:=‘‘;
230  Edit7.Text:=‘‘;
231  
232 end;
233 
234 end.

View Code

代码分析：

（1）、组合查询，拼接SQL 语句

 1 procedure TForm1.Button1Click(Sender: TObject);
 2 var
 3    sqlstr:string;
 4 begin
 5   ADOQuery1.Close;
 6   ADOQuery1.SQL.Clear;
 7   sqlstr:=‘select * from student where 1=1‘;
 8   //ADOQuery1.SQL.Add(‘select * from student where  name like :name  order by stucode‘);
 9   //ADOQuery1.Parameters.ParamByName(‘name‘).Value:=‘%‘+Edit1.Text+‘%‘;
10 
11 
12  if  Edit1.Text<>‘‘ then
13   begin
14     sqlstr:=sqlstr+‘ and name like ‘‘%‘+trim(Edit1.Text)+‘%‘‘ ‘;
15   end;
16 
17   if  ComboBox1.Text<>‘‘then
18   begin
19      sqlstr:=sqlstr+‘ and province=‘‘‘+trim(ComboBox1.Text)+‘‘‘‘;
20   end;
21 
22    if  ComboBox2.Text<>‘‘then
23   begin
24      sqlstr:=sqlstr+‘ and address=‘‘‘+trim(ComboBox2.Text)+‘‘‘‘;
25   end;
26   ADOQuery1.SQL.Add(sqlstr+‘ order by stucode‘);
27   ADOQuery1.open;
28 
29   Panel1.Caption:=‘共‘+ IntToStr(ADOQuery1.RecordCount)+‘条数据‘;
30 
31 
32 end;

（2）、当 ComboBox 发生 DropDown 事件时，把数据库中数据显示在 ComboBox 列表中

procedure TForm1.ComboBox1DropDown(Sender: TObject);

begin
  ComboBox1.Items.Clear;
  ADOQuery2.Close;
  ADOQuery2.SQL.Clear;
  ADOQuery2.SQL.Add(‘select distinct province from student‘);
  ADOQuery2.open;
  while not ADOQuery2.Eof do
  begin
    ComboBox1.Items.Add(ADOQuery2.FieldByName(‘province‘).AsString);
    ADOQuery2.Next;
  end;
end;

（3）、把表单中的数据保存在数据库中

 1 procedure TForm1.Button3Click(Sender: TObject);
 2 
 3 begin
 4 //ShowMessage(IntToStr(RadioGroup1.ItemIndex));
 5 //ShowMessage(RadioGroup1.Items[RadioGroup1.ItemIndex]);
 6 
 7   ADOQuery1.Close;
 8   ADOQuery1.SQL.Clear;
 9   ADOQuery1.SQL.Add(‘insert into student (stucode,name,sex,age,province,address,phonenumber)
       values(:stucode,:name,:sex,:age,:province,:address,:phonenumber)‘);
10   ADOQuery1.Parameters.ParamByName(‘stucode‘).Value:=Trim(Edit2.Text);
11   ADOQuery1.Parameters.ParamByName(‘name‘).Value:=Trim(Edit3.Text);
12   ADOQuery1.Parameters.ParamByName(‘sex‘).Value:=Trim(RadioGroup1.Items[RadioGroup1.ItemIndex]);
13   ADOQuery1.Parameters.ParamByName(‘age‘).Value:=Trim(Edit4.Text);
14   ADOQuery1.Parameters.ParamByName(‘province‘).Value:=Trim(Edit5.Text);
15   ADOQuery1.Parameters.ParamByName(‘address‘).Value:=Trim(Edit6.Text);
16   ADOQuery1.Parameters.ParamByName(‘phonenumber‘).Value:=Trim(Edit7.Text);
17   ADOQuery1.ExecSQL;
18   
19   displayAll;     //显示所有记录
20   Button6.Click;
21 end;

(4)、当单击 DBGrid1 中的某条数据时，在修改表项中显示数据

 1 procedure TForm1.DBGrid1CellClick(Column: TColumn);
 2 begin
 3   // s:=query1.fieldbyname(‘name‘).asstring;
 4  //ShowMessage(ADOQuery1.fieldbyname(‘stucode‘).asstring);
 5    
 6   //ShowMessage(adoquery1.fieldbyname(‘name‘).AsString);
 7   Edit2.Text:=ADOQuery1.fieldbyname(‘stucode‘).AsString;
 8   Edit3.Text:=ADOQuery1.fieldbyname(‘name‘).AsString;
 9   if Trim(ADOQuery1.fieldbyname(‘sex‘).AsString)=‘男‘ then
10       RadioGroup1.ItemIndex:=0
11    else
12       RadioGroup1.ItemIndex:=1;
13 
14   Edit4.Text:=ADOQuery1.fieldbyname(‘age‘).AsString;
15   Edit5.Text:=ADOQuery1.fieldbyname(‘province‘).AsString;
16   Edit6.Text:=ADOQuery1.fieldbyname(‘address‘).AsString;
17   Edit7.Text:=ADOQuery1.fieldbyname(‘phonenumber‘).AsString;
18   Edit2.Enabled:=False;
19 end;

（5）、修改数据并保存

 1 procedure TForm1.Button4Click(Sender: TObject);
 2 begin
 3   ADOQuery1.Close;
 4   ADOQuery1.SQL.Clear;
 5   ADOQuery1.SQL.Add(‘update student set name=:name ,sex=:sex,age=:age,province=:province,address=:address,phonenumber=:phonenumber  where stucode=:stucode‘);
 6   ADOQuery1.Parameters.ParamByName(‘name‘).Value:=Trim(Edit3.Text);
 7   ADOQuery1.Parameters.ParamByName(‘sex‘).Value:=Trim(RadioGroup1.Items[RadioGroup1.ItemIndex]);
 8   ADOQuery1.Parameters.ParamByName(‘age‘).Value:=Trim(Edit4.Text);
 9   ADOQuery1.Parameters.ParamByName(‘province‘).Value:=Trim(Edit5.Text);
10   ADOQuery1.Parameters.ParamByName(‘address‘).Value:=Trim(Edit6.Text);
11   ADOQuery1.Parameters.ParamByName(‘phonenumber‘).Value:=Trim(Edit7.Text);
12   ADOQuery1.Parameters.ParamByName(‘stucode‘).Value:=Trim(Edit2.Text);
13   ADOQuery1.ExecSQL;
14   //ShowMessage(‘修改成功！‘);
15   displayAll;     //显示所有记录
16 
17 end;