首页 > 代码库 > 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.
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<>‘‘ 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方式连接数据库--添删查修