首页 > 代码库 > ADO方式连接数据库--添删查修
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 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 begin100 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 begin113 displayAll;114 Panel1.Caption:=‘共‘+ IntToStr(ADOQuery1.RecordCount)+‘条数据‘;115 end;116 117 118 119 procedure TForm1.ComboBox1DropDown(Sender: TObject);120 121 begin122 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 do128 begin129 ComboBox1.Items.Add(ADOQuery2.FieldByName(‘province‘).AsString);130 ADOQuery2.Next;131 end;132 end;133 134 135 procedure TForm1.ComboBox2DropDown(Sender: TObject);136 137 begin138 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 do144 begin145 ComboBox2.Items.Add(ADOQuery2.FieldByName(‘address‘).AsString);146 ADOQuery2.Next;147 end;148 end;149 150 procedure TForm1.Button3Click(Sender: TObject);151 152 begin153 //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 begin174 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 begin182 // 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)=‘男‘ then189 RadioGroup1.ItemIndex:=0190 else191 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 begin202 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 begin220 DBGrid1.SelectedRows.Delete;221 end;222 223 procedure TForm1.Button6Click(Sender: TObject);224 begin225 Edit2.Text:=‘‘;226 Edit3.Text:=‘‘;227 Edit4.Text:=‘‘;228 Edit5.Text:=‘‘;229 Edit6.Text:=‘‘;230 Edit7.Text:=‘‘;231 232 end;233 234 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<>‘‘ then13 begin14 sqlstr:=sqlstr+‘ and name like ‘‘%‘+trim(Edit1.Text)+‘%‘‘ ‘;15 end;16 17 if ComboBox1.Text<>‘‘then18 begin19 sqlstr:=sqlstr+‘ and province=‘‘‘+trim(ComboBox1.Text)+‘‘‘‘;20 end;21 22 if ComboBox2.Text<>‘‘then23 begin24 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)=‘男‘ then10 RadioGroup1.ItemIndex:=011 else12 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;
ADO方式连接数据库--添删查修
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。