VB6 ADO断开连接的记录集不返回任何记录

Question

我正在创建，打开并断开与MySQL数据库的记录集。这对一个查询正常工作，但返回0个记录，用于数据库中存在行的另一个查询。它在哪里工作，我也可以从记录集中删除记录。

返回记录查询：

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，查询将返回记录，但是我无法断开连接。这只是为了证明数据库具有满足该查询的记录。

代码连接记录：

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 

我已经江郎才尽了，请帮助。

Answer 1

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

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年，现在没有任何问题。我鼓励你试试看。

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