在 ASP 中使用存储过程更改游标类型

Question

使用 ASP，我想调用一个返回记录计数的存储过程。

我知道我必须将光标类型更改为 adOpenKeyset 或 adOpenStatic 才能返回记录计数。

我不明白的是如何修改我的 vbscript，以便在调用过程时更改光标类型。

目前我说 cm.commandtype = adCmdStoredProc …… rs = cm.execute

我怀疑我需要向 cm.execute 添加一个参数，但我不知道要添加什么以及如何添加它。

谢谢

DMD

Answer 1

请改用记录集上的 Open 方法。不是 100% 确定语法，但类似：

rs.Open cm, , adOpenStatic

Answer 2

Dzejms 是对的，但您还应该在 rs.Open cm, , adOpenStatic 之前放置此行 oRecordSet.CursorLocation = CursorLocationEnum.adUseClient

这是我的完整解决方案：

    '>>> ---------------- global declarations ---------------- >>>
    Option Explicit
    Option Base 0
    
    Public ogADODB As New ADODB.Connection ' global variable for performance reason
    
    Public Type tError
      sDescription As String
      sHelpContext As Long
      sHelpFile As String
      cLastDllError As Long
      cNumber As Long
      sSource As String
    End Type
    '<<< ---------------- global declarations ---------------- <<<
    
    
    Sub Test__fnReadSQL()
      Dim oError As tError, oRecordSet As ADODB.Recordset, sSQLSelect As String
      sSQLSelect = "select * from [dbo].[YOUR_TABLE]"
      'sSQLSelect = "[dbo].[sp_STORED_PROCEDURE]"
      oError = fnReadSQL(sSQLSelect, oRecordSet)
      If oError.cNumber <> 0 Then Err.Raise Number:=oError.cNumber, Description:=oError.sDescription & " " & vbCrLf & sSQLSelect, Source:=oError.sSource, HelpFile:=oError.sHelpFile, HelpContext:=oError.sHelpContext
        
      ' do something with your oRecordSet
        
    End Sub
    
    
    Public Function fnReadSQL(sSQLCommand As String, oRecordSet As ADODB.Recordset) As tError ' sSQLCommand can be "select field1, field2, .. from [dbo].[YOUR_TABLE]" or "[dbo].[YOUR_TABLE]" or "[dbo].[StoredProcedure]"
      Dim oCommand As ADODB.Command, sConnectionString As String
    
      On Error GoTo LABEL_Error
      If ogADODB.State = ObjectStateEnum.adStateClosed Then ' for performance reason
        sConnectionString = "Provider=MSOLEDBSQL;SERVER=" & sCONST__Server & ";Trusted_Connection=yes;Database=" & sCONST__Database & ";DataTypeCompatibility=80;" 'Windows authentication
        ogADODB.ConnectionTimeout = 300
        ogADODB.Open sConnectionString
      End If
    
      Set oCommand = New ADODB.Command
      oCommand.ActiveConnection = ogADODB
      oCommand.CommandType = CommandTypeEnum.adCmdUnknown 'autodetection of "select" or "stored procedure"
      oCommand.CommandText = sSQLCommand
      Set oRecordSet = New ADODB.Recordset
      oRecordSet.CursorLocation = CursorLocationEnum.adUseClient ' this line of code is necessary if you want to use: CursorType = adOpenStatic
      oRecordSet.Open oCommand, , CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly
      Exit Function
    
    LABEL_Error:
      fnReadSQL.sDescription = Err.Description
      fnReadSQL.sHelpContext = Err.HelpContext
      fnReadSQL.sHelpFile = Err.HelpFile
      fnReadSQL.cLastDllError = Err.LastDllError
      fnReadSQL.cNumber = Err.Number
      fnReadSQL.sSource = Err.Source
    End Function