unit1.~pas

连接SQL数据库的一个程序

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Menus, ComCtrls, DB, ADODB, ImgList, ToolWin;

type
  TMain = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Edit1: TEdit;
    Label6: TLabel;
    DataSource1: TDataSource;
    ADOQuery1: TADOQuery;
    ADOConnection1: TADOConnection;
    ADOQuery2: TADOQuery;
    ADOQuery3: TADOQuery;
    ADOQuery4: TADOQuery;
    ADOQuery5: TADOQuery;
    ADOQuery6: TADOQuery;
    TreeView1: TTreeView;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Label7: TLabel;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    Edit5: TEdit;
    Edit6: TEdit;
    ImageList1: TImageList;
    MainMenu1: TMainMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    N5: TMenuItem;
    N6: TMenuItem;

      procedure TreeviewReFresh(Sender: TObject);
      Procedure BeComeEnable(Ena:Boolean);
    procedure FormCreate(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TreeView1Click(Sender: TObject);
    procedure TreeView1Expanding(Sender:TObject;Node:TTreeNode;
    var AllowExpansion:Boolean);
    procedure N3Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N6Click(Sender: TObject);

   
  private
    { Private declarations }
  public
     Node1,Node2,Node3,Node4:TTreeNode;
    { Public declarations }
  end;

var
  Main: TMain;

implementation

  procedure Tmain.TreeviewReFresh;
  begin

    Node1:=TreeView1.Items.Add(nil,'浙江教育用品');
    ADOQuery3.Close;
    ADOQuery3.SQL.Text:='Select Distinct name From info';
    ADOQuery3.Open;
    if ADOQuery3.RecordCount>0 then
       begin
          while not ADOQuery3.Eof do
             begin
                Node2:=TreeView1.Items.AddChild(Node1,ADOQuery3.Fields[0].asstring);
                TreeView1.Items.AddChild(Node2,'');
                ADOQuery3.Next;
             end;
       end;
   end;

    Procedure Tmain.BeComeEnable(Ena:Boolean);
     begin
        Edit1.Enabled:=Ena;
        Edit2.Enabled:=Ena;
        Edit3.Enabled:=Ena;
        Edit4.Enabled:=Ena;
        Edit5.Enabled:=Ena;
        Edit6.Enabled:=Ena;
        Combobox2.Enabled:=Ena;
     end;


procedure TMain.FormCreate(Sender: TObject);
  var
     i:integer;
begin
    with ADOQuery1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('Select * From info');
        Open;
        ADOQuery1.First;
        For i:=0 to ADOQuery1.FieldCount -1 do
          Combobox1.Items.Add(ADOQuery1.Fields[i].FieldName);
          ADOQuery1.Next;
       end;
end;

   procedure TMain.FormShow(Sender: TObject);
 begin
   with  ADOQuery4 do
       begin
          Close;
          SQL.Clear;
          SQL.Add('Select distinct dep From adddep');
          Open;
       end;
     While Not  ADOQuery4.Eof do
       begin
          Combobox2.Items.Add(Main.ADOQuery4.Fields[0].value);
          ADOQuery4.Next;
       end;

    TreeViewReFresh(self);
    TreeView1.AutoExpand:=True;
    BeComeEnable(false);
  end;



  procedure TMain.TreeView1Click(Sender: TObject);
    var
     str:string;
      begin
         if Treeview1.Selected=nil then
           begin
             Showmessage('选择不能为空');
             exit;
           end;
        ADOQuery5.SQL.Clear;
        str:='select * from info where name=:A';
        ADOQuery5.SQL.Add(str);
        ADOQuery5.Parameters.ParamByName('A').Value:=TreeView1.Selected.Text;
        ADOQuery5.Prepared;
        ADOQuery5.Open;
         if(ADOQuery5.RecordCount>0)and (Treeview1.Selected<>Nil)then
            with ADOQuery5 do
            begin
               Edit1.Text:=ADOQuery5.fieldbyname('code').AsString;
               Edit2.Text:=ADOQuery5.fieldbyname('name').AsString;
               Edit3.Text:=ADOQuery5.fieldbyname('master').AsString;
               Edit4.Text:=ADOQuery5.fieldbyname('tel').AsString;
               Edit5.Text:=ADOQuery5.fieldbyname('mancount').AsString;
               Edit6.Text:=ADOQuery5.fieldbyname('remark').AsString;
            end;
     end;
  procedure TMain.TreeView1Expanding(Sender:TObject;Node:TTreeNode;
    var AllowExpansion:Boolean);
    begin
      if Node=Node1 then
      exit;
      Node.DeleteChildren;
       ADOQuery3.Close;
       ADOQuery3.SQL.Text:='Select distinct name From info Where dep='+QuotedStr(Node.Text);
       ADOQuery3.Open;
       if ADOQuery3.RecordCount>0 then
       begin
       While not ADOQuery3.Eof do
       begin
         TreeView1.Items.AddChild(Node,ADOQuery3.Fields[0].asstring);
          ADOQuery3.Next;
          end;
        end;
  end;
         
procedure TMain.N3Click(Sender: TObject);
begin
  if (ADOQuery5.RecordCount>0)and(Treeview1.Selected<>Nil) then
  begin
    if messageDlg('确定要删除此条记录么?',mtconfirmation,[mbyes,mbno],0)=mryes then
   begin
   With ADOQuery1 do
     begin
      Close;
      SQL.Clear;
      SQL.Add('Delete From info Where name=:a');
      Parameters.ParamByName('a').Value:=TreeView1.Selected.Text;
      ExecSQL;
      TreeView1.Items.Delete(Treeview1.Selected);
      end;
    end;
    With ADOQuery1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('Select * From info');
        Open;
      end;
    end;
    TreeView1.Refresh;
     end;

   {$R *.dfm}
procedure TMain.N2Click(Sender: TObject);
begin
 if(Treeview1.Selected<>nil)and(Edit1.Text<>'')then
 begin
   BeComeEnable(True);
   end;
end;

procedure TMain.N4Click(Sender: TObject);
begin
 With ADOQuery1 do
   begin
     Close;
     SQL.Clear;
     SQL.Add('Update info Set code=:a,name=:b,dep=:c,master=:d,tel=:e,mancount=:f,remark=:g');
     SQL.Add('Where name='+quotedstr(Treeview1.Selected.Text));
     Parameters.ParamByName('a').Value:=Trim(Edit1.Text);
     Parameters.ParamByName('b').Value:=Trim(Edit2.Text);
     Parameters.ParamByName('c').Value:=Combobox1.Text;
     Parameters.ParamByName('d').Value:=Trim(Edit3.Text);
     Parameters.ParamByName('e').Value:=Trim(Edit4.Text);
     Parameters.ParamByName('f').Value:=Trim(Edit5.Text);
     Parameters.ParamByName('g').Value:=Trim(Edit6.Text);
     ExecSQL;
     MessageDlg('修改成功!',mtwarning,[mbyes],0);
    end;
   with Main.ADOQuery1 do
   begin
     ADOQuery1.Close;
     ADOQuery1.SQL.Clear;
     ADOQuery1.SQL.Add('Select * From info');
     ADOQuery1.Open;
     with ADOQuery3 do
     begin
     ADOQuery3.Close;
     ADOQuery3.SQL.Clear;
     ADOQuery3.SQL.Add('Select * From info');
     ADOQuery3.Open;
     TreeView1.Items.Clear;
     TreeView1.Refresh;
     TreeviewReFresh(self);
     end;
     BeComeEnable(False);
   end;
 end;



procedure TMain.N1Click(Sender: TObject);
var
SQLStr:string;
begin
  if(Edit1.Text='')and (Edit2.Text='')or(Combobox2.Text ='')then
  begin
  Application.MessageBox('请输入完整的信息!','添加出错',0);
  Edit1.SetFocus;
  exit;
  end;
  With Main.ADOQuery1 do
  begin
    Main.ADOQuery1.Close;
    Main.ADOQuery1.SQL.Clear;
    SQLStr:='insert into info(code,name,dep,master,tel,mancount,remark)'+
            'values(:a,:b,:c,:d,:e,:f,:g)';
    Main.ADOQuery1.SQL.Add(SQLStr);
    Main.ADOQuery1.Parameters.ParamByName('a').Value:=Trim(Edit1.Text);
    Main.ADOQuery1.Parameters.ParamByName('b').Value:=Trim(Edit2.Text);
    Main.ADOQuery1.Parameters.ParamByName('c').Value:=ComBobox1.Text;
    Main.ADOQuery1.Parameters.ParamByName('d').Value:=Trim(Edit3.Text);
    Main.ADOQuery1.Parameters.ParamByName('e').Value:=Trim(Edit4.Text);
    Main.ADOQuery1.Parameters.ParamByName('f').Value:=Trim(Edit5.Text);
    Main.ADOQuery1.Parameters.ParamByName('g').Value:=Trim(Edit6.Text);
   Main.ADOQuery1.ExecSQL;
   MessageDlg('添加成功!',mtwarning,[mbyes],0);
   end;
   With Main.ADOQuery1 do
   begin
   Main.ADOQuery1.Close;
   Main.ADOQuery1.SQL.Clear;
   Main.ADOQuery1.SQL.Add('Select * from info');
   Main.ADOQuery1.Open;
   with Main.ADOQuery3 do
      begin
        Main.ADOQuery3.Close;
        Main.ADOQuery3.SQL.Clear;
        Main.ADOQuery3.SQL.Add('Select * from info');
        Main.ADOQuery3.Open;
        Main.TreeView1.Items.Clear;
        Main.TreeView1.Refresh;
        Main.TreeviewReFresh(self);
      end;
   end;
  end;



procedure TMain.N6Click(Sender: TObject);
begin
 Close;
end;

end.