Delphi中ADO查询多表更新单表解决方法

最新推荐文章于 2020-10-20 11:29:07 发布

原创最新推荐文章于 2020-10-20 11:29:07 发布 · 7k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#Delphi #数据库 #SQL #Windows

Delphi 专栏收录该内容

29 篇文章

订阅专栏

本文介绍了在Delphi中使用ADO操作多表时如何更新单表的方法，通过设置数据集字段的providerflags，避免提交多表相关语句，解决了因未设置导致的错误问题，实现了数据库操作的精细化控制。

学Delphi也好久了，但一直没怎么总结，这不好久没弄ADO了，趁这个星期一直看公司的代码，自己也来实践一下。以前一直用ADO操作单表，一直没怎么弄过ADO操作多表的程序，不自己实践一下还真不知道操作多表时有要注意的地方。下面是一个例子。

第一步，新建一个测试库Test，表结构如下：

第二步，新建一个Delphi应用程序，在界面上放置adoconnection和adoquery以及 datasource和DBGridEh等控件，截图如下：

dfm文件如下：

object Form1: TForm1
Left = 328
Top = 78
Width = 601
Height = 403
Caption = '数据库操作'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
OnDestroy = FormDestroy
OnShow = FormShow
PixelsPerInch = 96
TextHeight = 13
object tlb1: TToolBar
Left = 0
Top = 0
Width = 585
Height = 25
AutoSize = True
ButtonHeight = 21
ButtonWidth = 31
Caption = 'tlb1'
ShowCaptions = True
TabOrder = 0
Transparent = False
object btnAdd: TToolButton
Left = 0
Top = 2
Caption = '添加'
ImageIndex = 0
OnClick = btnAddClick
end
object btnDel: TToolButton
Left = 31
Top = 2
Caption = '删除'
ImageIndex = 1
OnClick = btnDelClick
end
object btnEdit: TToolButton
Left = 62
Top = 2
Caption = '修改'
ImageIndex = 2
OnClick = btnEditClick
end
object btnSave: TToolButton
Left = 93
Top = 2
Caption = '保存'
ImageIndex = 3
OnClick = btnSaveClick
end
end
object dbgrdhUser: TDBGridEh
Left = 0
Top = 25
Width = 585
Height = 340
Align = alClient
DataGrouping.GroupLevels = <>
DataSource = ds1
Flat = False
FooterColor = clWindow
FooterFont.Charset = DEFAULT_CHARSET
FooterFont.Color = clWindowText
FooterFont.Height = -11
FooterFont.Name = 'MS Sans Serif'
FooterFont.Style = []
RowDetailPanel.Color = clBtnFace
TabOrder = 1
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
OnCellClick = dbgrdhUserCellClick
Columns = <
item
EditButtons = <>
FieldName = 'id'
Footers = <>
Visible = False
end
item
EditButtons = <>
FieldName = 'deptname'
Footers = <>
Width = 94
end
item
EditButtons = <>
FieldName = 'uname'
Footers = <>
end
item
EditButtons = <>
FieldName = 'deptid'
Footers = <>
Visible = False
end
item
EditButtons = <>
FieldName = 'sex'
Footers = <>
KeyList.Strings = (
'男'
'女')
PickList.Strings = (
'男'
'女')
Width = 45
end
item
EditButtons = <>
FieldName = 'age'
Footers = <>
end
item
EditButtons = <>
FieldName = 'job'
Footers = <>
end>
object RowDetailData: TRowDetailPanelControlEh
end
end
object ds1: TDataSource
DataSet = ds2
Left = 208
Top = 64
end
object ds2: TClientDataSet
Aggregates = <>
Params = <>
ProviderName = 'DataSetProvider1'
Left = 280
Top = 200
object intgrfldds2id: TIntegerField
FieldName = 'id'
Visible = False
end
object strngfldds2deptname: TStringField
DisplayLabel = '部门名称'
FieldName = 'deptname'
LookupDataSet = qryTemp
KeyFields = 'deptid'
ProviderFlags = []
end
object strngfldds2uname: TStringField
DisplayLabel = '用户名'
FieldName = 'uname'
end
object intgrfldds2deptid: TIntegerField
FieldName = 'deptid'
end
object strngfldds2set: TStringField
DisplayLabel = '性别'
FieldName = 'sex'
Size = 5
end
object intgrfldds2age: TIntegerField
DisplayLabel = '年龄'
FieldName = 'age'
end
object strngfldds2job: TStringField
DisplayLabel = '职业'
FieldName = 'job'
end
end
object DataSetProvider1: TDataSetProvider
DataSet = qry1
Left = 208
Top = 200
end
object con: TADOConnection
LoginPrompt = False
Left = 176
Top = 304
end
object qry1: TADOQuery
Connection = con
Parameters = <>
Left = 224
Top = 304
object intgrfldqry1id: TIntegerField
FieldName = 'id'
end
object strngfldqry1deptname: TStringField
DisplayLabel = '部门'
FieldName = 'deptname'
ProviderFlags = []
end
object strngfldqry1uname: TStringField
DisplayLabel = '姓名'
FieldName = 'uname'
end
object intgrfldqry1deptid: TIntegerField
DisplayLabel = '部门id'
FieldName = 'deptid'
end
object strngfldqry1sex: TStringField
DisplayLabel = '性别'
FieldName = 'sex'
end
object intgrfldqry1age: TIntegerField
DisplayLabel = '年龄'
FieldName = 'age'
end
object strngfldqry1job: TStringField
DisplayLabel = '职业'
FieldName = 'job'
end
end
object qryTemp: TADOQuery
Connection = con
DataSource = ds1
Parameters = <>
Left = 424
Top = 312
end
end

第三步，编写简单的增删查改代码代码如下：

unit UnitTblOp;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComCtrls, ToolWin, DBGridEhGrouping, DB, GridsEh, DBGridEh,
Provider, DBClient, ADODB;

type
TForm1 = class(TForm)
tlb1: TToolBar;
btnAdd: TToolButton;
btnDel: TToolButton;
btnEdit: TToolButton;
btnSave: TToolButton;
dbgrdhUser: TDBGridEh;
ds1: TDataSource;
ds2: TClientDataSet;
intgrfldds2id: TIntegerField;
strngfldds2deptname: TStringField;
strngfldds2uname: TStringField;
intgrfldds2deptid: TIntegerField;
strngfldds2set: TStringField;
intgrfldds2age: TIntegerField;
strngfldds2job: TStringField;
DataSetProvider1: TDataSetProvider;
con: TADOConnection;
qry1: TADOQuery;
intgrfldqry1id: TIntegerField;
strngfldqry1deptname: TStringField;
strngfldqry1uname: TStringField;
intgrfldqry1deptid: TIntegerField;
strngfldqry1sex: TStringField;
intgrfldqry1age: TIntegerField;
strngfldqry1job: TStringField;
qryTemp: TADOQuery;
procedure FormShow(Sender: TObject);
procedure btnAddClick(Sender: TObject);
procedure btnDelClick(Sender: TObject);
procedure btnEditClick(Sender: TObject);
procedure btnSaveClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure dbgrdhUserCellClick(Column: TColumnEh);
private
procedure ShowData;
procedure FillDept;
public

end;

var
Form1: TForm1;

implementation

{$R *.dfm}

const
strsql='Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s';

procedure TForm1.ShowData;
begin
with qry1 do
begin
Close;
sql.Clear;
SQL.Text:='select b.deptname,a.* from tbl_user a left join tbl_dept b on a.deptid=b.deptid';
Open;
end;
ds2.Close;
ds2.Open;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
ShowData;
end;

procedure TForm1.btnAddClick(Sender: TObject);
begin
ds2.Append;
end;

procedure TForm1.btnDelClick(Sender: TObject);
begin
if ds2.IsEmpty then exit;
ds2.Delete;
end;

procedure TForm1.btnEditClick(Sender: TObject);
begin
ds2.Edit;
end;

procedure TForm1.btnSaveClick(Sender: TObject);
begin
if ds2.State in [dsInsert,dsEdit] then
begin
ds2.Post;
end;
ds2.ApplyUpdates(-1);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
if not con.Connected then
begin
con.ConnectionString:=Format(strsql,['123456','sa','Test','.']);
con.Open;
// FillDept;
end;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
if con.Connected then
con.Close;
end;

procedure TForm1.FillDept;
begin
with qryTemp do
begin
Close;
SQL.Clear;
SQL.Add('select * from tbl_dept');
Open;
First;
while not eof do
begin
dbgrdhUser.FieldColumns['deptname'].PickList.Add(FieldByName('deptname').AsString);
dbgrdhUser.FieldColumns['deptname'].KeyList.Add(IntToStr(FieldByName('deptid').AsInteger));
Next;
end;
end;
end;

procedure TForm1.dbgrdhUserCellClick(Column: TColumnEh);
var
index:integer;
begin
ds2.Edit;
ds2.FieldByName('deptid').AsInteger:=strtointdef(ds2.FieldByName('deptname').AsString,0);
ds2.ApplyUpdates(-1);
end;

end.

end.

运行结果如图：