VB6 ADO断开连接的记录集不返回任何记录 [英] VB6 ADO Disconnected recordset returns no records

问题描述

我正在创建，打开然后断开与MySQL数据库的记录集的连接。这对于一个查询正确工作，但是对于数据库中存在行的另一个查询返回0条记录。在可行的地方，我也可以从记录集中删除记录。

I am creating, opening and then disconnecting a recordset against a MySQL database. This works correctly for one query but returns 0 records for another query where the rows exist in the database. Where it works, I can delete the records from the recordset as well.

返回记录的查询：

sql = "select convert(v.wonotes using UTF8) as WonData, v.wo_cat_id, v.id As wo_desc_id, v.line_no as line_no, " & _
      " v.wo_id as wo_id, v.prop_id as prop_id, convert(v.description using UTF8) as description, v.cat_id as cat_id, " & _
      " v.completion_date as completion_date from vw_property_wo_desc v " & _
      " where v.wo_cat_id= 6 and **(v.wo_status = 'completed' or v.wo_status = 'approved')** " & _
      " and v.wonotes is not null and v.wonotes<> '' "

不返回记录的查询：

sql = "select convert(v.wonotes using UTF8) as WonData, v.wo_cat_id, v.id As wo_desc_id, v.line_no as line_no, " & _
      " v.wo_id as wo_id, v.prop_id as prop_id, convert(v.description using UTF8) as description, v.cat_id as cat_id, " & _
      " v.completion_date as completion_date from vw_property_wo_desc v " & _
      " where v.wo_cat_id= 6 and **v.wo_status = 'unassigned'** " & _
      " and v.wonotes is not null and v.wonotes<> '' "

没有其他变化。

如果我将游标类型更改为adOpenDynamic，则查询将返回记录，但之后无法断开连接。这只是为了证明数据库具有满足该查询的记录。

If I change the cursor type to adOpenDynamic, the query returns records, but I cannot then disconnect it. This is just to prove that the database has records satisfying that query.

断开记录集的代码：

With rsToUse

    If .State = adStateOpen Then .Close

    .ActiveConnection = GetConnection
    .Source = sql
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockBatchOptimistic
    .Open

    If .EOF Then
        .Close
        Exit Function
    End If

    .ActiveConnection = Nothing

End With

我的想法已经用完了，请帮忙。

I have run out of ideas, please help.

推荐答案

这是我用来获取代码从SQL Server数据库断开记录集。我怀疑它也可以用于MySQL数据库（当然，除了连接字符串）。

This is the code I use to get disconnected recordsets from a SQL Server database. I suspect it would also work for a MySQL database (except for the connection string, of course).

Public Function GetRecordset(ByVal SQL As String) As ADODB.Recordset

    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset

    Set DB = CreateObject("ADODB.Connection")
    DB.ConnectionString = globalConnectionString
    DB.CursorLocation = adUseClient
    DB.CommandTimeout = 0
    Call DB.Open

    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = adUseClient
    Call RS.Open(SQL, DB, adOpenForwardOnly, adLockReadOnly)
    Set RS.ActiveConnection = Nothing
    Set GetRecordset = RS
    Set RS = Nothing
    DB.Close
    Set DB = Nothing

End Function

此精确代码已经生产了至少5年，没有任何问题。我鼓励您尝试一下。

This exact code has been in production for at least 5 years now without any issue. I encourage you to give it a try.

我认为使用断开记录集的神奇组合是确保连接对象的CursorLocation设置为UseClient，并且记录集对象是ForwardOnly和LockReadOnly。

I think the magic combination to using a disconnected recordset is to make sure the connection object has the CursorLocation set to UseClient, and the recordset object is ForwardOnly and LockReadOnly.

这篇关于VB6 ADO断开连接的记录集不返回任何记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！