各位好,我用 C# WinForm+Access 做单据式的界面,绑定主从表时子表操作都没问题,但一修改主表,再保存就出错,提示[由于表 'BillDetail' 中了包含相关记录,不能删除或改变该记录。]。其实我并没修改主表的主键或其他有关联的数据,只是修改了一些备注的文字信息也是如此,不知何故?
我尝试换成SqlServer数据库,结构都一样,却没有此问题,不知何故?
千万别告诉我,将关系删除,我相信是有关系的问题,但不是关系的错。
图一:Access数据库结构:
图二:Sqlserver数据库结构
图三:WinForm界面与控件
以下是SqlSever的建库脚本,Access的结构也一样,WinForm中基本没什么自己写的代码,都是生成的。
- if exists (select * from sysobjects where id = OBJECT_ID('[Products]') and OBJECTPROPERTY(id,
- 'IsUserTable') = 1)
- DROP TABLE [Products]
- GO
- CREATE TABLE [Products] (
- [Pkid] [int] IDENTITY (1, 1) NOT NULL ,
- [ProductCode] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
- [ProductName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
- CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
- (
- [Pkid]
- ) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET IDENTITY_INSERT [Products] ON
- INSERT [Products] ([Pkid],[ProductCode],[ProductName]) VALUES ( 2,'F0501200','绿茶洗发水')
- INSERT [Products] ([Pkid],[ProductCode],[ProductName]) VALUES ( 3,'M0602600','香草沐浴露')
- INSERT [Products] ([Pkid],[ProductCode],[ProductName]) VALUES ( 4,'C0518500','芦荟洗手液')
- SET IDENTITY_INSERT [Products] OFF
- GO
- --------------------
- if exists (select * from sysobjects where id = OBJECT_ID('[Bill]') and OBJECTPROPERTY(id,
- 'IsUserTable') = 1)
- DROP TABLE [Bill]
- GO
- CREATE TABLE [Bill] (
- [Guid] [uniqueidentifier] NOT NULL ,
- [BillDateTime] [datetime] NOT NULL ,
- [Person] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
- CONSTRAINT [PK_Bill] PRIMARY KEY CLUSTERED
- (
- [Guid]
- ) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- INSERT [Bill] ([Guid],[BillDateTime],[Person]) VALUES ( 'b0caaa95-339c-4de7-b1ad-
- 1b0fadf78103','2008-12-02 23:55:00','唐古拉山')
- INSERT [Bill] ([Guid],[BillDateTime],[Person]) VALUES ( '23704e6a-eca6-4622-9fe1-
- caa7a3aa8eca','2007-09-24 8:23:00','李飞')
- GO
- --------------------
- if exists (select * from sysobjects where id = OBJECT_ID('[BillDetail]') and OBJECTPROPERTY(id,
- 'IsUserTable') = 1)
- DROP TABLE [BillDetail]
- GO
- CREATE TABLE [BillDetail] (
- [Guid] [uniqueidentifier] NOT NULL ,
- [LineNum] [int] NOT NULL ,
- [ProductId] [int] NOT NULL ,
- [Quantity] [decimal](18, 4) NOT NULL ,
- [Price] [decimal](18, 4) NOT NULL ,
- [Remark] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- CONSTRAINT [PK_BillDetail] PRIMARY KEY CLUSTERED
- (
- [Guid],
- [LineNum]
- ) ON [PRIMARY] ,
- CONSTRAINT [FK_BillDetail_Bill] FOREIGN KEY
- (
- [Guid]
- ) REFERENCES [Bill] (
- [Guid]
- ),
- CONSTRAINT [FK_BillDetail_Products] FOREIGN KEY
- (
- [ProductId]
- ) REFERENCES [Products] (
- [Pkid]
- )
- ) ON [PRIMARY]
- GO
- INSERT [BillDetail] ([Guid],[LineNum],[ProductId],[Quantity],[Price],[Remark]) VALUES (
- 'b0caaa95-339c-4de7-b1ad-1b0fadf78103',1,2,23.5000,66.5800,'单独发货')
- INSERT [BillDetail] ([Guid],[LineNum],[ProductId],[Quantity],[Price]) VALUES ( 'b0caaa95-339c-
- 4de7-b1ad-1b0fadf78103',2,3,33.5000,45.5800)
- INSERT [BillDetail] ([Guid],[LineNum],[ProductId],[Quantity],[Price]) VALUES ( 'b0caaa95-339c-
- 4de7-b1ad-1b0fadf78103',3,4,24.0000,50.0000)
- INSERT [BillDetail] ([Guid],[LineNum],[ProductId],[Quantity],[Price]) VALUES ( '23704e6a-eca6-
- 4622-9fe1-caa7a3aa8eca',1,4,44.0000,345.0000)
- INSERT [BillDetail] ([Guid],[LineNum],[ProductId],[Quantity],[Price],[Remark]) VALUES (
- '23704e6a-eca6-4622-9fe1-caa7a3aa8eca',2,3,52.0000,56.7800,'补货')
- INSERT [BillDetail] ([Guid],[LineNum],[ProductId],[Quantity],[Price]) VALUES ( '23704e6a-eca6-
- 4622-9fe1-caa7a3aa8eca',3,2,23.5000,66.8000)
- GO
呵呵,自己解决了。
观察了“TableAdapter”的“UpdateCommand”的SQL语句,哇,一大把:
- UPDATE Bill
- SET [Guid] = ?, BillDateTime = ?, Person = ?
- WHERE ([Guid] = ?) AND (? = 1) AND (BillDateTime IS NULL) AND (? = 1) AND
- (Person IS NULL) OR
- ([Guid] = ?) AND (BillDateTime = ?) AND (? = 1) AND (Person IS NULL) OR
- ([Guid] = ?) AND (? = 1) AND (BillDateTime IS NULL) AND (Person = ?) OR
- ([Guid] = ?) AND (BillDateTime = ?) AND (Person = ?)
去除它的“使用开放式并发”后:
- UPDATE Bill
- SET [Guid] = ?, BillDateTime = ?, Person = ?
- WHERE ([Guid] = ?)
现在好看了……哇!为何要Update Guid字段,它是主键,不会变的!好,改掉它的!
- UPDATE Bill
- SET BillDateTime = ?, Person = ?
- WHERE ([Guid] = ?)
现在没问题了罗。
心得:
(1)、微软的IDE是方便快捷,但在没明白它都给你干了些啥之前,最好别用它。
(2)、搞不懂MS ACCESS,Update 自己=自己这样的语句也是导致了修改事件?而SQL Server则不会。