不允许更新记录-写冲突 [英] Updates to Records not allowed - Write Conflict

问题描述

这源于我之前问过的一个问题-关于与表单的写冲突，但是问题似乎源于以下事实：我可以更新一个System DSN提供的链接表中的现有记录，但不能更新另一个链接由另一个DSN（不同数据库）提供的表-允许我首先输入记录，但是随后阻止我对记录进行任何更改，就好像另一个用户更改了我之前的数据一样。在服务器上没有为默认值，没有触发器，没有视图，没有其他用户访问同一表的检查约束。

This stems from a previous question I asked - about a write conflict with a form, but the problem seems to be originating from the fact that I can update existing records in a linked table provided by one System DSN, but not another linked table provided by another DSN (different database) - allowing me to enter records at first, but then preventing me from making any changes to the records as if another user has changed the data before me. There are no check constraints defined on the server for default values, no triggers, no views, no other users accessing the same table.

两个DSN都设置为IDENTICAL，除了他们打开的默认数据库-第一个打开gtdata_test，而第二个打开instkeeper_test。 SQL Server实例上的两个数据库均归我所有，我是唯一的登录用户。

Both DSNs are set up IDENTICAL except for the default database they open - the first opening gtdata_test, while the second one opens instkeeper_test. Both databases on the SQL Server instance are owned by me, I am the only logged in user.

我已经对此进行了测试，并且此问题发生在表级别（否

I have tested this, and this problem occurs at the table level (no VBA in the program written, nothing) with manual updates.

如果有帮助，我将在good表及其下面的受影响表中包括CREATE语句。 。我希望有人能帮助我，因为我不怎么想。

In case it helps, I will include the CREATE statements for the good table, and for the affected tables below it. I hope someone can help me, as I am fresh out of ideas.

在gtdata_test中创建Supplier_Master表的代码：

USE [gtdata_test]
GO

/****** Object:  Table [dbo].[Supplier_Master]    Script Date: 05/27/2009 15:58:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Supplier_Master](
    [Supplier_Code] [nvarchar](50) NOT NULL,
    [Supplier_Master_Name] [nvarchar](50) NULL,
    [Salutation] [nvarchar](50) NULL,
    [Contact] [nvarchar](50) NULL,
    [Phone] [nvarchar](50) NULL,
    [Fax] [nvarchar](50) NULL,
    [EMail] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [State] [nvarchar](50) NULL,
    [Zip] [nvarchar](50) NULL,
    [Country] [nvarchar](50) NULL,
    [Last_Review] [datetime] NULL,
    [Last_Rating] [datetime] NULL,
    [Last_Received] [datetime] NULL,
    [Last_Reject] [datetime] NULL,
    [Enabled] [int] NULL,
    [User1] [nvarchar](50) NULL,
    [User2] [nvarchar](50) NULL,
    [SupType] [nvarchar](50) NULL,
 CONSTRAINT [Supplier_Master$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [Supplier_Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Address$disallow_zero_length] CHECK  ((len([Address])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Address$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$City$disallow_zero_length] CHECK  ((len([City])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$City$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Contact$disallow_zero_length] CHECK  ((len([Contact])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Contact$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Country$disallow_zero_length] CHECK  ((len([Country])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Country$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$EMail$disallow_zero_length] CHECK  ((len([EMail])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$EMail$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Fax$disallow_zero_length] CHECK  ((len([Fax])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Fax$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Phone$disallow_zero_length] CHECK  ((len([Phone])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Phone$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Salutation$disallow_zero_length] CHECK  ((len([Salutation])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Salutation$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$State$disallow_zero_length] CHECK  ((len([State])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$State$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Code$disallow_zero_length] CHECK  ((len([Supplier_Code])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Code$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Master_Name$disallow_zero_length] CHECK  ((len([Supplier_Master_Name])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Master_Name$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$SupType$disallow_zero_length] CHECK  ((len([SupType])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$SupType$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$User1$disallow_zero_length] CHECK  ((len([User1])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$User1$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$User2$disallow_zero_length] CHECK  ((len([User2])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$User2$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master]  WITH NOCHECK ADD  CONSTRAINT [SSMA_CC$Supplier_Master$Zip$disallow_zero_length] CHECK  ((len([Zip])>(0)))
GO

ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Zip$disallow_zero_length]
GO

ALTER TABLE [dbo].[Supplier_Master] ADD  DEFAULT ((0)) FOR [Enabled]
GO

在instkeeper_test中创建tblSupplierInfo的代码：

USE [instkeeper_test]
GO

/****** Object:  Table [dbo].[tblSupplierInfo]    Script Date: 05/27/2009 15:57:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSupplierInfo](
    [strSupplierID] [nvarchar](50) NOT NULL,
    [bolSupAltShipAddyRep] [bit] NULL,
    [bolSupAltShipAddyCal] [bit] NULL,
    [bolSupInsistNet30] [bit] NULL,
    [bolRMARequireRepair] [bit] NULL,
    [bolRMARequireCalibration] [bit] NULL,
    [bolSupShipOrCourier] [bit] NULL,
    [bolSupRequireMSDS] [bit] NULL,
    [bolSupBlanketPO] [bit] NULL,
    [bolSupRequirePricing] [bit] NULL,
    [bolSupBlankPricing] [bit] NULL,
    [bolSupFaxPOSend] [bit] NULL,
    [bolAdditionalPaperworkRepair] [bit] NULL,
    [bolAdditionalPaperworkCalibration] [bit] NULL,
    [strRMARepairWordage] [nvarchar](100) NULL,
    [strRMACalibrationWordage] [nvarchar](100) NULL,
    [intBlanketPO] [int] NULL,
    [bolUseFedExNumber] [bit] NULL,
    [strFedExNumber] [nvarchar](150) NULL,
    [bolUseUPSNumber] [bit] NULL,
    [strUPSNumber] [nvarchar](150) NULL,
    [bolSupA2LAAccredited] [bit] NULL,
    [bolSupFreightAllow] [bit] NULL,
    [bolSupFreightOnly] [bit] NULL,
    [bolSupUseMiscNum] [bit] NULL,
    [strSupMiscFreightNum] [nvarchar](150) NULL,
 CONSTRAINT [tblSupplierInfo$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [strSupplierID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

在instkeeper_tes中创建tblSupplierNote的代码t ：

USE [instkeeper_test]
GO

/****** Object:  Table [dbo].[tblSupplierNote]    Script Date: 06/01/2009 12:34:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSupplierNote](
    [intSupNoteID] [int] IDENTITY(1,1) NOT NULL,
    [strSupplierID] [nvarchar](50) NULL,
    [datDateNoteEntered] [datetime] NULL,
    [datTimeNoteEntered] [datetime] NULL,
    [strNoteBy] [nvarchar](255) NULL,
    [memSupNote] [nvarchar](max) NULL,
 CONSTRAINT [tblSupplierNote$PrimaryKey] PRIMARY KEY CLUSTERED 
(
    [intSupNoteID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblSupplierNote]  WITH CHECK ADD  CONSTRAINT [tblSupplierNote$tblSupplierInfotblSupplierNote] FOREIGN KEY([strSupplierID])
REFERENCES [dbo].[tblSupplierInfo] ([strSupplierID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblSupplierNote] CHECK CONSTRAINT [tblSupplierNote$tblSupplierInfotblSupplierNote]
GO

仅概述-我使用第一个DSN开发的应用程序中提取的任何表都可以读取/写入，可以创建，删除和更新记录，而不会出现任何问题。使用第二个DSN，我放入应用程序中的任何表都可以写入ONCE。任何进一步的更改都将丢失，并且只要在数据表视图中的表中更新数据，就会出现写冲突对话框。

Just to overview - any table I pull into the application I am developing using the first DSN are read/write-able, records can be created, removed, and updated without any problem. With the second DSN, any table I pull into the application can be written to ONCE. Any further changes are lost, and a Write Conflict dialog box appears whenever data is updated in the table in datasheet view.

我正在使用的软件：

• Microsoft Access 2003


• SQL Server Express 2008


• Windows XP Professional SP3

-编辑06/03/2009 @ 1307小时-

-- Edited 06/03/2009 @ 1307 hours --

我发现使用是/否复选框和带有Access的SQL Server时出现了一个奇怪的问题。显然，Access会将NULL解释为No-更改值，但是SQL Server不会在Bit字段中将NULL解释为No（在转换中将Yes / No转化为是），因此当未提供值时会引发Write Conflict错误必填，为NULL。解决方案是重新设计表，以便需要一个值，并且为每个以前的是/否复选框分配了默认值。这解决了神秘的Write Conflict消息，并允许在创建记录后对其进行更改。

I found out that a strange problem that crops up when using Yes/No checkboxes and SQL Server with Access. Apparently, Access will interpret NULL as No - changing the value, but SQL Server will not interpret NULL as a No in a Bit field (what Yes/No gets turned into in conversion) so it throws a Write Conflict error when a value is not required, and is NULL. The solution was to redesign the table so that a value was required, and that there was a default value assigned for EVERY former Yes/No checkbox. This solved the mysterious Write Conflict messages, and allowed changes to records once they were created.

推荐答案

Access的方式有所不同处理是/否复选框值和SQL Server。从Access到SQL Server转换是/否布尔值时，必须记住定义默认状态并将其标记为需要答案。否则，每次都会发生写冲突，并且一旦设置了初始值，就将阻止记录随更改保存。

There is a difference between how Access handles Yes/No checkbox values and SQL Server. When translating Yes/No booleans from Access to SQL Server, you must remember to define a default state as well as mark it as requiring an answer. Otherwise, you will get write conflicts every time, and it will prevent the record from being saved with your changes once the initial values have been set.

这篇关于不允许更新记录-写冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！