访问链接到 SQL:为新创建的记录显示了错误的数据 [英] Access Linked to SQL: Wrong data shown for a newly created record

问题描述

我在 Access (2013) 中使用链接到 SQL 数据库的表.主键是一列RowId"，包含一个 GUID.

I'm using tables in Access (2013), that are linked to an SQL database. The primary key is a column 'RowId', containing a GUID.

当我向表中添加新记录时，我将 RowId 留空.这条记录被插入到 SQL 数据库中，但随后添加的记录被另一条记录的数据填充.

When I'm adding a new record to the table, and I leave the RowId blank. The record is inserted in the SQL database, but then the added record is filled up with data of another record.

在调查了 SQL Server Profiler 的问题后，我发现会发生这种情况:

After investigating the problem with SQL Server Profiler, I discovered that this happens:

首先处理一条 INSERT 语句:

First, an INSERT statement is processed:

exec sp_executesql
    N'INSERT INTO  "{table}"  ("{column1}","{column2}", {column4}") 
      VALUES (@P1,@P2,@P3)',
    N'@P1 int,@P2 nvarchar(50),@P3 nvarchar(50)',
    3438,
    N'Smith',
    N'Sofia'

但是，第二个语句被传递，以获取保存记录的值(我猜):

But then, a second statement is passed, in order to get the values of the saved record (I guess):

declare @p1 int
set @p1=5
exec sp_prepexec
   @p1 output,
   NULL,
   N'SELECT "{column1}","{column2}", {column3}","{column4}","{column5}", {column6}",
     "RowID","RV"  FROM "{table}"  WHERE "RowID" IS NULL'
select @p1

最初在创建记录时未给出 RowID，因此现在 Access 要求提供 RowId 为 NULL 的行.这发生了两次，失败了两次.然后，通过插入语句中给出的参数询问 RowId:

The RowID is not initially given when creating the record, so now Access asks for an row with RowId IS NULL. This happens twice, and fails twice. Then, the RowId is asked by the parameters that are given in the insert statement:

exec sp_executesql
   N'SELECT "{table}"."RowID"
     FROM "{table}"
     WHERE "{column1}" = @P1 AND "{column2}" = @P2 AND "{column4}" = @P3',
   N'@P1 int,@P2 nvarchar(50),@P3 nvarchar(50)',
   3438,
   N'Smith',
   N'Sofia'

如果存在另一条参数相同的记录(例如3438、Smith、Sofia)，则返回这条记录的RowID，Access中的新记录填充这条记录的数据(包括错误的RowID)).现在对该记录的每个操作都是在错误的记录上进行的，因为语句中给出了错误的 RowID.刷新表后，会显示新插入的记录，以及正确的数据和新生成的 RowID.

If there is another record with the same parameters (eg 3438, Smith, Sofia) exists, the RowID of this record given back, and the new record in Access is filled in with data from this other record (including the wrong RowID). Every action on this record is made on the wrong record now, as the wrong RowID is given with the statements. After I refresh the table, the newly inserted record is shown, with the right data and new generated RowID.

我怎样才能防止这种情况发生?如何在插入行后立即显示正确的数据?

How can I prevent this from happening? How can I make it so the right data is shown immediately after I insert the row?

推荐答案

我怀疑 Access 不知道 RowId 是主键，或者它(或 ODBC 驱动程序)无法处理GUID 数据类型正确.

I suspect that either Access doesn't know that RowId is the primary key, or it (or the ODBC driver) cannot handle the GUID datatype properly.

如果在设计视图中打开链接表，Access 会为 RowId 显示什么数据类型?它是否显示主键的键"图标?

If you open the linked table in design view, what datatype does Access show for RowId? And does it show the "key" icon for the primary key?

在对 关于使用 SQL Server GUID 的建议的评论中MS Access ，David-W-Fenton(对这些事情了解很多)写道:

In a comment to Recommendations on using SQL Server GUID from MS Access , David-W-Fenton (who knows a lot about these things) writes:

如果您的前端在 Access 中，那么处理 GUID 就会出现重大问题:http://www.trigeminal.com/usenet/usenet011.asp?1033

If your front end is in Access, then there are major problems with handling GUIDs: http://www.trigeminal.com/usenet/usenet011.asp?1033

很遗憾，此页面已关闭.
编辑 archive.org 来救援:信息:复制和 GUID、好的、坏的和丑的
虽然这似乎是关于 Jet GUID，而不是 SQL Server，所以可能不适用.

Unfortunately this page is down.
Edit archive.org to the rescue: INFO: Replication and GUIDs, the Good, the Bad, and the Ugly
Although this seems to be about Jet GUIDs, not SQL Server, so probably not applicable.

关于 GUID 的更多信息:使用 GUID 作为主键的最佳做法是什么，特别是在性能方面?

Some more things about GUIDs: What are the best practices for using a GUID as a primary key, specifically regarding performance?

这篇关于访问链接到 SQL:为新创建的记录显示了错误的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！