ADO方式连接数据库--添删查修
程序界面：
程序源码：
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 Button7: TButton; 54 procedure Button1Click(Sender: TObject); 55 procedure Button2Click(Sender: TObject); 56 procedure ComboBox1DropDown(Sender: TObject); 57 procedure ComboBox2DropDown(Sender: TObject); 58 procedure Button3Click(Sender: TObject); 59 procedure displayAll; 60 procedure DBGrid1CellClick(Column: TColumn); 61 procedure Button4Click(Sender: TObject); 62 procedure Button5Click(Sender: TObject); 63 procedure Button6Click(Sender: TObject); 64 procedure Button7Click(Sender: TObject); 65 66 67 private 68 { Private declarations } 69 public 70 { Public declarations } 71 end; 72 73 var 74 Form1: TForm1; 75 76 implementation 77 78 {$R *.dfm} 79 80 procedure TForm1.Button1Click(Sender: TObject); 81 var 82 sqlstr:string; 83 begin 84 ADOQuery1.Close; 85 ADOQuery1.SQL.Clear; 86 sqlstr:='select * from student where 1=1'; 87 //ADOQuery1.SQL.Add('select * from student where name like :name order by stucode'); 88 //ADOQuery1.Parameters.ParamByName('name').Value:='%'+Edit1.Text+'%'; 89 90 91 if Edit1.Text<>'' then 92 begin 93 sqlstr:=sqlstr+' and name like ''%'+trim(Edit1.Text)+'%'' '; 94 end; 95 96 if ComboBox1.Text<>''then 97 begin 98 sqlstr:=sqlstr+' and province='''+trim(ComboBox1.Text)+''''; 99 end; 100 101 if ComboBox2.Text<>''then 102 begin 103 sqlstr:=sqlstr+' and address='''+trim(ComboBox2.Text)+''''; 104 end; 105 ADOQuery1.SQL.Add(sqlstr+' order by stucode'); 106 ADOQuery1.open; 107 108 Panel1.Caption:='共'+ IntToStr(ADOQuery1.RecordCount)+'条数据'; 109 110 111 end; 112 113 procedure TForm1.Button2Click(Sender: TObject); 114 115 begin 116 displayAll; 117 Panel1.Caption:='共'+ IntToStr(ADOQuery1.RecordCount)+'条数据'; 118 end; 119 120 121 122 procedure TForm1.ComboBox1DropDown(Sender: TObject); 123 124 begin 125 ComboBox1.Items.Clear; 126 ADOQuery2.Close; 127 ADOQuery2.SQL.Clear; 128 ADOQuery2.SQL.Add('select distinct province from student'); 129 ADOQuery2.open; 130 while not ADOQuery2.Eof do 131 begin 132 ComboBox1.Items.Add(ADOQuery2.FieldByName('province').AsString); 133 ADOQuery2.Next; 134 end; 135 end; 136 137 138 procedure TForm1.ComboBox2DropDown(Sender: TObject); 139 140 begin 141 ComboBox2.Items.Clear; 142 ADOQuery2.Close; 143 ADOQuery2.SQL.Clear; 144 ADOQuery2.SQL.Add('select distinct address from student'); 145 ADOQuery2.open; 146 while not ADOQuery2.Eof do 147 begin 148 ComboBox2.Items.Add(ADOQuery2.FieldByName('address').AsString); 149 ADOQuery2.Next; 150 end; 151 end; 152 153 procedure TForm1.Button3Click(Sender: TObject); 154 155 begin 156 //ShowMessage(IntToStr(RadioGroup1.ItemIndex)); 157 //ShowMessage(RadioGroup1.Items[RadioGroup1.ItemIndex]); 158 if Edit2.Text='' then 159 ShowMessage('学号不能为空！') 160 else 161 begin 162 ADOQuery1.Close; 163 ADOQuery1.SQL.Clear; 164 ADOQuery1.SQL.Add('insert into student (stucode,name,sex,age,province,address,phonenumber) values(:stucode,:name,:sex,:age,:province,:address,:phonenumber)'); 165 ADOQuery1.Parameters.ParamByName('stucode').Value:=Trim(Edit2.Text); 166 ADOQuery1.Parameters.ParamByName('name').Value:=Trim(Edit3.Text); 167 ADOQuery1.Parameters.ParamByName('sex').Value:=Trim(RadioGroup1.Items[RadioGroup1.ItemIndex]); 168 if Trim(Edit4.Text)='' then 169 ADOQuery1.Parameters.ParamByName('age').Value:=0 170 else 171 ADOQuery1.Parameters.ParamByName('age').Value:=StrToInt(Trim(Edit4.Text)); 172 173 ADOQuery1.Parameters.ParamByName('province').Value:=Trim(Edit5.Text); 174 ADOQuery1.Parameters.ParamByName('address').Value:=Trim(Edit6.Text); 175 ADOQuery1.Parameters.ParamByName('phonenumber').Value:=Trim(Edit7.Text); 176 try 177 ADOQuery1.ExecSQL; 178 except 179 on e:Exception do 180 ShowMessage('学号已存在！'); 181 end; 182 //ShowMessage('保存成功！'); 183 displayAll; //显示所有记录 184 Button6.Click; 185 186 187 end; 188 end; 189 190 procedure TForm1.displayAll; 191 begin 192 ADOQuery1.Close; 193 ADOQuery1.SQL.Clear; 194 ADOQuery1.SQL.Add('select * from student order by stucode'); 195 ADOQuery1.open; 196 end; 197 198 procedure TForm1.DBGrid1CellClick(Column: TColumn); 199 begin 200 // s:=query1.fieldbyname('name').asstring; 201 //ShowMessage(ADOQuery1.fieldbyname('stucode').asstring); 202 203 //ShowMessage(adoquery1.fieldbyname('name').AsString); 204 Edit2.Text:=ADOQuery1.fieldbyname('stucode').AsString; 205 Edit3.Text:=ADOQuery1.fieldbyname('name').AsString; 206 if Trim(ADOQuery1.fieldbyname('sex').AsString)='男' then 207 RadioGroup1.ItemIndex:=0 208 else 209 RadioGroup1.ItemIndex:=1; 210 211 Edit4.Text:=ADOQuery1.fieldbyname('age').AsString; 212 Edit5.Text:=ADOQuery1.fieldbyname('province').AsString; 213 Edit6.Text:=ADOQuery1.fieldbyname('address').AsString; 214 Edit7.Text:=ADOQuery1.fieldbyname('phonenumber').AsString; 215 Edit2.Enabled:=False; 216 217 end; 218 219 procedure TForm1.Button4Click(Sender: TObject); 220 begin 221 if Edit2.Text='' then 222 ShowMessage('学号不能为空！') 223 else 224 begin 225 226 227 ADOQuery1.Close; 228 ADOQuery1.SQL.Clear; 229 ADOQuery1.SQL.Add('update student set name=:name ,sex=:sex,age=:age,province=:province,address=:address,phonenumber=:phonenumber where stucode=:stucode'); 230 ADOQuery1.Parameters.ParamByName('name').Value:=Trim(Edit3.Text); 231 ADOQuery1.Parameters.ParamByName('sex').Value:=Trim(RadioGroup1.Items[RadioGroup1.ItemIndex]); 232 if Trim(Edit4.Text)='' then 233 ADOQuery1.Parameters.ParamByName('age').Value:=0 234 else 235 ADOQuery1.Parameters.ParamByName('age').Value:=StrToInt(Trim(Edit4.Text)); 236 237 ADOQuery1.Parameters.ParamByName('province').Value:=Trim(Edit5.Text); 238 ADOQuery1.Parameters.ParamByName('address').Value:=Trim(Edit6.Text); 239 ADOQuery1.Parameters.ParamByName('phonenumber').Value:=Trim(Edit7.Text); 240 ADOQuery1.Parameters.ParamByName('stucode').Value:=Trim(Edit2.Text); 241 ADOQuery1.ExecSQL; 242 //ShowMessage('修改成功！'); 243 displayAll; //显示所有记录 244 end; 245 end; 246 247 procedure TForm1.Button5Click(Sender: TObject); 248 begin 249 DBGrid1.SelectedRows.Delete; 250 displayAll; //显示所有记录 251 end; 252 253 procedure TForm1.Button6Click(Sender: TObject); 254 begin 255 Edit2.Text:=''; 256 Edit3.Text:=''; 257 Edit4.Text:=''; 258 Edit5.Text:=''; 259 Edit6.Text:=''; 260 Edit7.Text:=''; 261 Edit2.Enabled:=True; 262 end; 263 264 265 266 procedure TForm1.Button7Click(Sender: TObject); 267 268 var 269 i: Integer; 270 begin 271 if dbgrid1.SelectedRows.Count>0 then 272 begin 273 for i:=0 to dbgrid1.SelectedRows.Count-1 do 274 begin 275 ADOQuery1.GotoBookmark(pointer(dbgrid1.SelectedRows.Items[i])); 276 //ShowMessage(ADOQuery1.FieldByName('stucode').AsString); 277 self.ADOQuery1.Delete;//删除记录 278 end; 279 end; 280 281 end; 282 283 end.
代码分析：
（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;
