有主从结构的两个表
master, detail
还有一个关联的产品表
定义语句为:
CREATE TABLE [dbo].[detail] (
[theID] [char] (10) NOT NULL ,
[goodID] [char] (2) NOT NULL ,
[amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[good] (
[goodID] [char] (2) NOT NULL ,
[names] [char] (10) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[master] (
[theID] [char] (10) NOT NULL ,
[re] [char] (10) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[detail] WITH NOCHECK ADD
CONSTRAINT [PK_detail] PRIMARY KEY CLUSTERED
(
[theID],
[goodID]
) ON [PRIMARY]
GOALTER TABLE [dbo].[good] WITH NOCHECK ADD
CONSTRAINT [PK_good] PRIMARY KEY CLUSTERED
(
[goodID]
) ON [PRIMARY]
GOALTER TABLE [dbo].[master] WITH NOCHECK ADD
CONSTRAINT [PK_master] PRIMARY KEY CLUSTERED
(
[theID]
) ON [PRIMARY]
GOALTER TABLE [dbo].[detail] ADD
CONSTRAINT [FK_detail_good] FOREIGN KEY
(
[goodID]
) REFERENCES [dbo].[good] (
[goodID]
),
CONSTRAINT [FK_detail_master] FOREIGN KEY
(
[theID]
) REFERENCES [dbo].[master] (
[theID]
)
GO这是我的窗体frm文件的内容:
-------------------------------------------------------------
object Form1: TForm1
Left = 214
Top = 149
Width = 696
Height = 480
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object Panel1: TPanel
Left = 0
Top = 0
Width = 688
Height = 446
Align = alClient
ParentShowHint = False
ShowHint = False
TabOrder = 0
object Splitter1: TSplitter
Left = 1
Top = 161
Width = 686
Height = 2
Cursor = crVSplit
Align = alTop
end
object Panel3: TPanel
Left = 1
Top = 163
Width = 686
Height = 282
Align = alClient
BevelInner = bvLowered
TabOrder = 0
object gdDetail: TDBGrid
Left = 2
Top = 2
Width = 682
Height = 278
Hint = '使用右键菜单操作'
Align = alClient
DataSource = dsDetail
ParentShowHint = False
ShowHint = True
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
end
object DBNavigator1: TDBNavigator
Left = 168
Top = 80
Width = 240
Height = 25
DataSource = dsDetail
TabOrder = 1
end
end
object gdBrowse: TDBGrid
Left = 1
Top = 1
Width = 686
Height = 160
Align = alTop
DataSource = dsMaster
Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit]
TabOrder = 1
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
end
object DBNavigator2: TDBNavigator
Left = 160
Top = 88
Width = 240
Height = 25
DataSource = dsMaster
TabOrder = 2
end
end
object dsMaster: TDataSource
DataSet = qryMaster
Left = 585
Top = 11
end
object qryMaster: TADOQuery
CacheSize = 1000
Connection = conn
CursorType = ctStatic
LockType = ltBatchOptimistic
AfterOpen = qryMasterAfterOpen
BeforePost = qryMasterBeforePost
AfterPost = qryMasterAfterPost
BeforeCancel = qryMasterBeforeCancel
BeforeDelete = qryMasterBeforeDelete
AfterDelete = qryMasterAfterDelete
AfterScroll = qryMasterAfterScroll
Parameters = <>
SQL.Strings = (
'select *'
'from Master')
Left = 515
Top = 11
object qryMastertheID: TStringField
FieldName = 'theID'
FixedChar = True
Size = 10
end
object qryMasterre: TStringField
FieldName = 're'
FixedChar = True
Size = 10
end
end
object dsDetail: TDataSource
DataSet = qryDetail
Left = 553
Top = 259
end
object qryDetail: TADOQuery
CacheSize = 1000
Connection = conn
CursorType = ctStatic
LockType = ltBatchOptimistic
BeforeOpen = qryDetailBeforeOpen
AfterOpen = qryDetailAfterOpen
BeforeInsert = qryDetailBeforeInsert
BeforeEdit = qryDetailBeforeEdit
BeforePost = qryDetailBeforePost
AfterPost = qryDetailAfterPost
BeforeDelete = qryDetailBeforeDelete
AfterDelete = qryDetailAfterDelete
Parameters = <
item
Name = 'theID'
DataType = ftString
NumericScale = 255
Precision = 255
Size = 10
Value = Null
end>
SQL.Strings = (
'select *'
'from Detail'
'where theID=:theID')
Left = 476
Top = 266
object qryDetailtheID: TStringField
FieldName = 'theID'
FixedChar = True
Size = 10
end
object qryDetailgoodID: TStringField
FieldName = 'goodID'
FixedChar = True
Size = 2
end
object qryDetailamount: TBCDField
FieldName = 'amount'
Precision = 18
Size = 0
end
end
object conn: TADOConnection
Connected = True
ConnectionString =
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
'fo=False;Initial Catalog=work;Use Procedure for Prepare=1;Auto T' +
'ranslate=True;Packet Size=4096;Workstation ID=C420004;Use Encryp' +
'tion for Data=False;Tag with column collation when possible=Fals' +
'e'
LoginPrompt = False
Provider = 'SQLOLEDB.1'
Left = 382
Top = 35
end
end
————————————————————————————————————
master, detail
还有一个关联的产品表
定义语句为:
CREATE TABLE [dbo].[detail] (
[theID] [char] (10) NOT NULL ,
[goodID] [char] (2) NOT NULL ,
[amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[good] (
[goodID] [char] (2) NOT NULL ,
[names] [char] (10) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[master] (
[theID] [char] (10) NOT NULL ,
[re] [char] (10) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[detail] WITH NOCHECK ADD
CONSTRAINT [PK_detail] PRIMARY KEY CLUSTERED
(
[theID],
[goodID]
) ON [PRIMARY]
GOALTER TABLE [dbo].[good] WITH NOCHECK ADD
CONSTRAINT [PK_good] PRIMARY KEY CLUSTERED
(
[goodID]
) ON [PRIMARY]
GOALTER TABLE [dbo].[master] WITH NOCHECK ADD
CONSTRAINT [PK_master] PRIMARY KEY CLUSTERED
(
[theID]
) ON [PRIMARY]
GOALTER TABLE [dbo].[detail] ADD
CONSTRAINT [FK_detail_good] FOREIGN KEY
(
[goodID]
) REFERENCES [dbo].[good] (
[goodID]
),
CONSTRAINT [FK_detail_master] FOREIGN KEY
(
[theID]
) REFERENCES [dbo].[master] (
[theID]
)
GO这是我的窗体frm文件的内容:
-------------------------------------------------------------
object Form1: TForm1
Left = 214
Top = 149
Width = 696
Height = 480
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object Panel1: TPanel
Left = 0
Top = 0
Width = 688
Height = 446
Align = alClient
ParentShowHint = False
ShowHint = False
TabOrder = 0
object Splitter1: TSplitter
Left = 1
Top = 161
Width = 686
Height = 2
Cursor = crVSplit
Align = alTop
end
object Panel3: TPanel
Left = 1
Top = 163
Width = 686
Height = 282
Align = alClient
BevelInner = bvLowered
TabOrder = 0
object gdDetail: TDBGrid
Left = 2
Top = 2
Width = 682
Height = 278
Hint = '使用右键菜单操作'
Align = alClient
DataSource = dsDetail
ParentShowHint = False
ShowHint = True
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
end
object DBNavigator1: TDBNavigator
Left = 168
Top = 80
Width = 240
Height = 25
DataSource = dsDetail
TabOrder = 1
end
end
object gdBrowse: TDBGrid
Left = 1
Top = 1
Width = 686
Height = 160
Align = alTop
DataSource = dsMaster
Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit]
TabOrder = 1
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
end
object DBNavigator2: TDBNavigator
Left = 160
Top = 88
Width = 240
Height = 25
DataSource = dsMaster
TabOrder = 2
end
end
object dsMaster: TDataSource
DataSet = qryMaster
Left = 585
Top = 11
end
object qryMaster: TADOQuery
CacheSize = 1000
Connection = conn
CursorType = ctStatic
LockType = ltBatchOptimistic
AfterOpen = qryMasterAfterOpen
BeforePost = qryMasterBeforePost
AfterPost = qryMasterAfterPost
BeforeCancel = qryMasterBeforeCancel
BeforeDelete = qryMasterBeforeDelete
AfterDelete = qryMasterAfterDelete
AfterScroll = qryMasterAfterScroll
Parameters = <>
SQL.Strings = (
'select *'
'from Master')
Left = 515
Top = 11
object qryMastertheID: TStringField
FieldName = 'theID'
FixedChar = True
Size = 10
end
object qryMasterre: TStringField
FieldName = 're'
FixedChar = True
Size = 10
end
end
object dsDetail: TDataSource
DataSet = qryDetail
Left = 553
Top = 259
end
object qryDetail: TADOQuery
CacheSize = 1000
Connection = conn
CursorType = ctStatic
LockType = ltBatchOptimistic
BeforeOpen = qryDetailBeforeOpen
AfterOpen = qryDetailAfterOpen
BeforeInsert = qryDetailBeforeInsert
BeforeEdit = qryDetailBeforeEdit
BeforePost = qryDetailBeforePost
AfterPost = qryDetailAfterPost
BeforeDelete = qryDetailBeforeDelete
AfterDelete = qryDetailAfterDelete
Parameters = <
item
Name = 'theID'
DataType = ftString
NumericScale = 255
Precision = 255
Size = 10
Value = Null
end>
SQL.Strings = (
'select *'
'from Detail'
'where theID=:theID')
Left = 476
Top = 266
object qryDetailtheID: TStringField
FieldName = 'theID'
FixedChar = True
Size = 10
end
object qryDetailgoodID: TStringField
FieldName = 'goodID'
FixedChar = True
Size = 2
end
object qryDetailamount: TBCDField
FieldName = 'amount'
Precision = 18
Size = 0
end
end
object conn: TADOConnection
Connected = True
ConnectionString =
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
'fo=False;Initial Catalog=work;Use Procedure for Prepare=1;Auto T' +
'ranslate=True;Packet Size=4096;Workstation ID=C420004;Use Encryp' +
'tion for Data=False;Tag with column collation when possible=Fals' +
'e'
LoginPrompt = False
Provider = 'SQLOLEDB.1'
Left = 382
Top = 35
end
end
————————————————————————————————————
————————————————————————————————------
unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, DBCtrls, Grids, DBGrids, ExtCtrls;type
TForm1 = class(TForm)
Panel1: TPanel;
Splitter1: TSplitter;
Panel3: TPanel;
gdDetail: TDBGrid;
DBNavigator1: TDBNavigator;
gdBrowse: TDBGrid;
DBNavigator2: TDBNavigator;
dsMaster: TDataSource;
qryMaster: TADOQuery;
qryMastertheID: TStringField;
qryMasterre: TStringField;
dsDetail: TDataSource;
qryDetail: TADOQuery;
qryDetailtheID: TStringField;
qryDetailgoodID: TStringField;
qryDetailamount: TBCDField;
conn: TADOConnection;
procedure qryMasterAfterDelete(DataSet: TDataSet);
procedure qryMasterAfterOpen(DataSet: TDataSet);
procedure qryMasterAfterPost(DataSet: TDataSet);
procedure qryMasterAfterScroll(DataSet: TDataSet);
procedure qryMasterBeforeCancel(DataSet: TDataSet);
procedure qryMasterBeforeDelete(DataSet: TDataSet);
procedure qryMasterBeforePost(DataSet: TDataSet);
procedure qryDetailAfterOpen(DataSet: TDataSet);
procedure qryDetailAfterDelete(DataSet: TDataSet);
procedure qryDetailAfterPost(DataSet: TDataSet);
procedure qryDetailBeforeDelete(DataSet: TDataSet);
procedure qryDetailBeforeEdit(DataSet: TDataSet);
procedure qryDetailBeforeInsert(DataSet: TDataSet);
procedure qryDetailBeforeOpen(DataSet: TDataSet);
procedure qryDetailBeforePost(DataSet: TDataSet);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
bDeleteAll: boolean;
procedure UpdateMasterData;
procedure UpdateDetaiData;
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}{ TForm1 }procedure TForm1.UpdateDetaiData;
var
strMsg :String;
i, errorCount :Integer;
begin
try
Screen.Cursor:=crHourglass;
conn.BeginTrans;
try
qryDetail.UpdateBatch;;
conn.CommitTrans;
except
errorCount := conn.Errors.Count;
strMsg := '';
for i := 0 to errorCount-1 do
begin
case conn.Errors.Item[i].NativeError of
2627:
strMsg := strMsg + '主键不能重复!请重新输入.' + #13;
3621:
strMsg := strMsg + '语句被中止!' + #13;
547:
strMsg := strMsg + '被别的数据库参考,违反引用完整性.不可删除!' + #13;
else
strMsg := strMsg + conn.Errors.Item[i].Description + #13;
end;
end; //End For
showmessage(strMsg);
qryDetail.CancelUpdates;
conn.RollbackTrans;
end; // End Except
finally
Screen.Cursor:=crDefault;
end; //End Finally
end;procedure TForm1.UpdateMasterData;
var
strMsg :String;
i, errorCount :Integer;
oldcursor:Tcursor;
begin
oldcursor:=screen.Cursor ; //保存当前鼠标
Screen.Cursor:=crHourglass;
try
conn.BeginTrans;
try
qryMaster.UpdateBatch;;
conn.CommitTrans;
except
errorCount := conn.Errors.Count;
strMsg := '';
for i := 0 to errorCount-1 do
begin
case conn.Errors.Item[i].NativeError of
2627:
strMsg := strMsg + ' 主键不能重复!请重新输入.' + #13;
3621:
strMsg := strMsg + '语句被中止!' + #13;
547:
strMsg := strMsg + ' 被别的数据库参考,违反引用完整性.不可删除!' + #13;
else
strMsg := strMsg + conn.Errors.Item[i].Description + #13;
end;
end;
showmessage(strMsg);
qryMaster.CancelUpdates;
conn.RollbackTrans;
end;
finally
//还原光标
screen.Cursor :=oldcursor;
end;
end;
var
strMsg :String;
i, errorCount :Integer;
begin
// 因为主从更新需要一定的更新次序,取消基类更新逻辑
// inherited;
// 更新数据到数据库
try
Screen.Cursor:=crHourglass;
//开始交易
conn.BeginTrans;
try
//先更新明细文件,后主文件
qryDetail.UpdateBatch();
qryMaster.UpdateBatch();
conn.CommitTrans;
except
errorCount := conn.Errors.Count;
strMsg := '';
for i := 0 to errorCount-1 do
begin
case conn.Errors.Item[i].NativeError of
2627:
strMsg := strMsg + '主键不能重复!请重新输入.' + #13;
3621:
strMsg := strMsg + '语句被中止!' + #13;
547:
strMsg := strMsg + '被别的数据库参考,违反引用完整性.不可删除!' + #13;
else
strMsg := strMsg + conn.Errors.Item[i].Description + #13;
end;
end; // End For
showmessage(strMsg);
qryDetail.CancelUpdates;
qryMaster.CancelUpdates;
conn.RollbackTrans;
end; // End Except
finally
Screen.Cursor:=crDefault;
end; // End Finallyend;procedure TForm1.qryMasterAfterOpen(DataSet: TDataSet);
begin
qryMaster.Last;
end;procedure TForm1.qryMasterAfterPost(DataSet: TDataSet);
begin
UpdateMasterData;
end;procedure TForm1.qryMasterAfterScroll(DataSet: TDataSet);
begin
// 主文件指针移到后,重新调取对应的明细文件
qryDetail.DisableControls;
try
qryDetail.Close;
qryDetail.Open;
finally
qryDetail.EnableControls;
end;
end;procedure TForm1.qryMasterBeforeCancel(DataSet: TDataSet);
begin
// 主文件取消前,明细文件必须先取消
if qryDetail.State in [dsInsert,dsEdit] then
qryDetail.Cancel;
end;procedure TForm1.qryMasterBeforeDelete(DataSet: TDataSet);
begin
// 删除所对应的所有明细文件
bDeleteAll:= true;
{
删除所有明细数据时也会出错,但是去掉 qryDetail.DisableControls; 和
qryDetail.EnableControls; 这两条语句便没有异常了
异常内容:
---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EOleException with message 'Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record'. Process stopped. Use Step or Run to continue.
---------------------------
OK Help
---------------------------
}
qryDetail.DisableControls;
try
qryDetail.First;
while not qryDetail.Eof do //发生异常时, 运行到这条语句
qryDetail.Delete;
finally
qryDetail.EnableControls;
bDeleteAll:=false;
end;
end;procedure TForm1.qryMasterBeforePost(DataSet: TDataSet);
begin
// 主文件保存前,必须先保存明细文件
if qryDetail.State in [dsInsert,dsEdit] then
qryDetail.Post;
end;procedure TForm1.qryDetailAfterOpen(DataSet: TDataSet);
begin
// 打开后,移至末
qryDetail.Last;
end;procedure TForm1.qryDetailAfterDelete(DataSet: TDataSet);
begin
if bDeleteAll then exit;
UpdateDetaiData;
//删除完成,将主文件保存,变为浏览模式
if qryMaster.State in [dsInsert,dsEdit] then
qryMaster.Post;
end;procedure TForm1.qryDetailAfterPost(DataSet: TDataSet);
begin
//更新数据库
UpdateDetaiData;
end;procedure TForm1.qryDetailBeforeDelete(DataSet: TDataSet);
begin
// 如果是删除全部,退出
if bDeleteAll then exit;
// 主文件无数据,则明细文件不可删除
if (qryMaster.Eof) and (qryMaster.Bof) then
Abort;
// 明细文件无数据,则明细文件不可删除
if (qryDetail.Eof) and (qryDetail.Bof) then
Abort;
//主文件变为编辑模式
if qryMaster.State=dsBrowse then
qryMaster.Edit;
end;procedure TForm1.qryDetailBeforeEdit(DataSet: TDataSet);
begin
// 主文件无数据,则明细文件不可编辑
if (qryMaster.Eof) and (qryMaster.Bof) then
Abort;
// 明细文件无数据,则明细文件不可编辑
if (qryDetail.Eof) and (qryDetail.Bof) then
Abort;
// 主文件是新增的,必须先保存
if qryMaster.State = dsInsert then
qryMaster.Post;
// 把主文件变为编辑模式
if qryMaster.State=dsBrowse then
qryMaster.Edit;
end;procedure TForm1.qryDetailBeforeInsert(DataSet: TDataSet);
begin
// 主文件无数据,则明细文件不可新增
if (qryMaster.Eof) and (qryMaster.Bof) then
Abort;
// 主文件是新增的,必须先保存
if qryMaster.State = dsInsert then
qryMaster.Post;
// 把主文件变为编辑模式
if qryMaster.State=dsBrowse then
qryMaster.Edit;
end;procedure TForm1.qryDetailBeforeOpen(DataSet: TDataSet);
begin
qryDetail.Parameters.ParamByName('theID').Value
:= qryMaster.fieldByName('theID').AsString;
end;procedure TForm1.qryDetailBeforePost(DataSet: TDataSet);
begin
if qryDetail.UpdateStatus = usInserted then
qryDetail.FieldByName('theID').AsString :=
qryMaster.FieldByName('theID').AsString;
end;procedure TForm1.FormCreate(Sender: TObject);
begin
qryMaster.Open;
qryDetail.Open;
bDeleteAll:=false;
end;end.
————————————————————————————————————现在问题是: 如果在数据库中对应主表项, 从表只有一条数据。 则在打开程序点击删除这条唯一的数据时 ,就会出现找不到当前行的异常:
---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EOleException with message 'Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record'. Process stopped. Use Step or Run to continue.
---------------------------
OK Help
---------------------------
而如果从表有两条数据的话, 就不会出错。
如:
现在数据库有这些数据
master : theID re good: goodID name
No1 第一条 a 物品a
b 物品b
Detail: theID goodID amount
No1 a 12
运行程序, 在从数据区,点击右键菜单删除数据 另外,在删除主数据同时删除所有的从表数据时也存在,我在代码中有说明。我找了几天了都没有弄清问题出在哪,郁闷!! 请大家帮我调试一下。 分不够再加。 谢谢
我是用 delphi 7 ado 2.8
太多
懒得看楼主以后问问题,拣主要的说,真是ctrl+v用得太熟了
不是主从表么,怎么还要自己控制?
初步觉得是不是做了很多重复的动作导致了错误。
procedure TForm1.qryMasterAfterScroll(DataSet: TDataSet);
begin
// 主文件指针移到后,重新调取对应的明细文件
qryDetail.DisableControls;
try
qryDetail.Close;
qryDetail.Open;
finally
qryDetail.EnableControls;
end;
end;