经典ASP断开记录集问题 [英] Classic ASP disconnected recordsets issue

问题描述

因此，我被要求更新旧的Classic ASP网站.它不使用参数化查询，并且很少有输入验证.为简化起见，我编写了一个辅助函数，该函数打开与数据库的连接，设置带有任何参数的命令对象，并创建断开连接的记录集[我认为！?！ :)]代码如下:

So, I've been asked to update an old Classic ASP website. It did not use parameterized queries and there was very little input validation. To simplify things I wrote a helper function that opens a connection to the database, sets up a command object with any parameters, and creates a disconnected recordset [I think!?! :)] Here's the code:

Function GetDiscRS(DatabaseName, SqlCommandText, ParameterArray)

    'Declare our variables
    Dim discConn
    Dim discCmd
    Dim discRs

    'Build connection string
    Dim dbConnStr : dbConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & rootDbPath & "\" & DatabaseName & ".mdb;" & _
              "Persist Security Info=False"

    'Open a connection
    Set discConn = Server.CreateObject("ADODB.Connection")
    discConn.Open(dbConnStr)

    'Create a command
    Set discCmd = Server.CreateObject("ADODB.Command")
    With discCmd
        Set .ActiveConnection = discConn
        .CommandType = adCmdText
        .CommandText = SqlCommandText

        'Attach parameters to the command
        If IsArray(ParameterArray) Then
            Dim cnt : cnt = 0
            For Each sqlParam in ParameterArray
                discCmd.Parameters(cnt).Value = sqlParam
                cnt = cnt + 1
            Next
        End If
    End With

    'Create the Recordset object
    Set discRs = Server.CreateObject("ADODB.Recordset")
    With discRs
        .CursorLocation = adUseClient     ' Client cursor for disconnected set
        .LockType = adLockBatchOptimistic
        .CursorType = adOpenForwardOnly
        .Open discCmd
        Set .ActiveConnection = Nothing   ' Disconnect!
    End With

    'Return the Recordset
    Set GetDiscRS = discRS

    'Cleanup
    discConn.Close()
    Set discConn = Nothing
    discRS.Close()                  ' <=== Issue!!!
    Set discRs = Nothing
    Set discCmd = Nothing
End Function

我的问题是，如果我在函数末尾调用discRS.Close()，则不会填充返回的记录集.这使我想知道记录集是否确实断开连接.如果我对此发表意见，一切都会正常进行.在设置ActiveConnection = Nothing之前和之后，我还使用discRS值在函数内做了一些Response.Write()，它正确地返回了记录集值.因此它似乎被隔离为discRS.Close().

My problem is that if I call discRS.Close() at the end of the function, then the recordset that is returned is not populated. This made me wonder if the recordset is indeed disconnected or not. If I comment that line out everything works properly. I also did some Response.Write() within the function using discRS values before and after setting ActiveConnection = Nothing and it properly returned the recordset values. So it seems to be isolated to discRS.Close().

我在 4guysfromrolla.com 上找到了一篇旧文章，并发布了记录集函数中的Close().我在其他网站上也看到过同样的事情.我不确定这是否是一个错误，还是发生了什么变化?

I found an old article on 4guysfromrolla.com and it issues the recordset Close() in the function. I've seen the same thing on other sites. I'm not sure if that was a mistake, or if something has changed?

注意:我使用的是Visual Studio Express 2013中内置的IIS Express

Note: I'm using IIS Express built into Visual Studio Express 2013

推荐答案

据我所知，断开的记录集是指手动填充的记录集，而不是从数据库填充的记录集，例如用作多维数组或哈希表.

Disconnected recordset as far as I know refers to a recordset populated manually, not from database, e.g.used as multi dimensional array or kind of hash table.

因此，由于它是从数据库中填充的，因此您所拥有的不是一个未连接的记录集，并且通过处置其连接，只会导致您的代码无法正常工作.

So what you have is not a disconnected recordset since it's being populated from database, and by disposing its connection you just cause your code to not work properly.

由于代码中已经包含Set discConn = Nothing，因此不必通过记录集或命令对象将其设置为空，因此它是相同的连接对象.

Since you already have Set discConn = Nothing in the code you don't have to set it to nothing via the recordset or command objects, it's the same connection object.

总而言之，您确实应该删除代码中的以下几行:

To sum this all up, you should indeed get rid of tho following lines in your code:

• Set .ActiveConnection = Nothing ' Disconnect!
• discRS.Close() ' <=== Issue!!!
• Set discRs = Nothing

• Set .ActiveConnection = Nothing ' Disconnect!
• discRS.Close() ' <=== Issue!!!
• Set discRs = Nothing

然后，为防止内存泄漏或数据库锁定问题，应在代码中使用功能(例如，

Then to prevent memory leaks or database lock issues, you should close and dispose the recordset after actually using it in the code using the function e.g.

Dim oRS
Set oRS = GetDiscRS("mydatabase", "SELECT * FROM MyTable", Array())
Do Until oRS.EOF
    'process current row...
    oRS.MoveNext
Loop

oRS.Close ' <=== Close
Set oRS = Nothing ' <=== Dispose

为避免所有麻烦，您可以通过复制所有数据到新创建的记录集中来使函数返回真实的"断开记录集.如果相关，请告诉我，我将提供一些代码.

To avoid all this hassle you can have the function return "real" disconnected recordset by copying all the data into newly created recordset. If relevant let me know and I'll come with some code.

这篇关于经典ASP断开记录集问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！