为什么 AccessDataSource 会返回不同的结果在 Access 中查询? [英] Why would AccessDataSource return different results to query in Access?

问题描述

我有一个从 Access 数据库返回随机不同行的查询.这是查询:

I have a query to return random distinct rows from an Access database. Here is the query:

SELECT * FROM 
(SELECT DISTINCT m.MemberID, m.Title, m.FullName, m.Address, 
        m.Phone, m.EmailAddress, m.WebsiteAddress FROM Members AS m INNER JOIN MembersForType AS t ON m.MemberID = t.MemberID WHERE 
(Category = 'MemberType1' OR Category = 'MemberType2')) as Members 
ORDER BY RND(members.MemberID) DESC

当我在 Access 中运行它时，它每次都会根据随机排序顺序以不同的顺序返回行.当我通过我的网络应用程序运行它时，行每次都以相同的顺序返回.这是我在我的代码隐藏中调用它的方式:

When I run this in Access it returns the rows in different order every time, as per the random sort order. When I run it through my web app however the rows return in the same order every time. Here is how I call it in my code-behind:

private void BindData()
{
    using (AccessDataSource ds = new AccessDataSource("~/App_Data/mydb.mdb", GetSQLStatement()))
    {
        ds.DataSourceMode = SqlDataSourceMode.DataReader;
        ds.CacheDuration = 0;
        ds.CacheExpirationPolicy = DataSourceCacheExpiry.Absolute;
        ds.EnableCaching = false;            
        listing.DataSource = ds.Select(new DataSourceSelectArguments());
        listing.DataBind();
        if (listing.Items.Count == 0)
            noResults.Visible = true;
        else
            noResults.Visible = false;
    }
}

我添加了有关缓存的所有内容，因为我认为查询可能已被缓存但结果是相同的.我在代码中放置了一个断点以确保查询与上面的相同.

I added in all that stuff about caching because I thought maybe the query was being cached but the result was the same. I put a breakpoint in the code to make sure the query was the same as above and it was.

有什么想法吗?这让我发疯.

Any ideas? This is driving me nuts.

推荐答案

当对新连接执行 ACE/Jet RND 函数时，每次都使用相同的种子值.使用 MS Access 时，您每次都使用相同的连接，这解释了为什么每次都获得不同的值.

When executing the ACE/Jet RND function against a new connection the same seed value is used each time. When using MS Access you are using the same connection each time, which explains why you get a different value each time.

考虑这些 VBA 示例:第一个在每次迭代时使用一个新连接:

Consider these VBA examples: the first uses a new connection on each iteration:

Sub TestDiff()

  Dim con As Object
  Set con = CreateObject("ADODB.Connection")
  With con
    .ConnectionString = _
        "Provider=MSDataShape;Data " & _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Tempo\Test_Access2007.accdb"
    .CursorLocation = 3

    Dim i As Long
    For i = 0 To 2

      .Open
      Debug.Print .Execute("SELECT RND FROM OneRowTable;")(0)
      .Close
    Next
  End With

End Sub

输出:

 0.705547511577606 
 0.705547511577606 
 0.705547511577606 

每次记下相同的值.

第二个例子在每次迭代中使用相同的连接(.Open 和 .Close 语句被重定位到循环外):

The second example uses the same connection on each iteration (the .Open and .Close statements are relocated outside the loop):

Sub TestSame()

  Dim con As Object
  Set con = CreateObject("ADODB.Connection")
  With con
    .ConnectionString = _
        "Provider=MSDataShape;Data " & _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Tempo\Test_Access2007.accdb"
    .CursorLocation = 3

    .Open

    Dim i As Long
    For i = 0 To 2
      Debug.Print .Execute("SELECT RND FROM OneRowTable;")(0)
    Next

    .Close

  End With

End Sub

输出:

 0.705547511577606 
 0.533424019813538 
 0.579518616199493 

每次注意不同的值.

在 VBA 代码中，您可以使用 Randomize 关键字为 Rnd() 函数设置种子，但我认为这不能在 ACE/Jet 中完成.一种解决方法是使用 ACE/Jet NOW() niladic 函数的最低有效小数部分，例如类似:

In VBA code you can use the Randomize keyword to seed the Rnd() function but I don't think this can be done in ACE/Jet. One workaround is to use the least significant decimal portion of the ACE/Jet the NOW() niladic function e.g. something like:

SELECT CDBL(NOW()) - ROUND(CDBL(NOW()), 4) FROM OneRowTable

这篇关于为什么 AccessDataSource 会返回不同的结果在 Access 中查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！