无法使用ADODB从varchar（max）中读取数据 [英] Can't read data from varchar(max) using ADODB

问题描述

我有一个使用ADODB执行存储过程并将结果作为记录集读取的方法。一切工作正常，直到我将输出字段之一从varchar（2000）更改为varchar（max）（SQL2008）。现在，我无法读取该字段中的数据。奇怪的是，在运行Execute之后，数据立即在调试器中可见，但是进入调试器会使数据消失。

I've got a method that uses ADODB to execute a sproc and read the results as a recordset. Everything was working fine until i changed one of the output fields from varchar(2000) to varchar(max) (SQL2008). Now I can't read the data from that field. The strange thing is that the data is visible in the debugger immediately after running the Execute, but stepping in the debugger makes the data vanish.

以下是代码：

Public Function sproc_RptEAComment(ByVal accountName As String, ByVal contractName As String,
                                            ByVal acctType As String, ByVal asOfDate As DateTime,
                                            ByVal sessionID As String, ByVal clin As String,
                                            ByVal dollarDisplay As String) As List(Of sproc_RptEAComment_Row) Implements ISprocRepository.sproc_RptEAComment
    Try

        Dim cmd = New Command()
        cmd.ActiveConnection = Connection
        cmd.CommandType = CommandTypeEnum.adCmdStoredProc
        cmd.CommandText = "sproc_RptEAComment"

        ADOUtilities.AddParamToSproc(cmd, "@ChargeNum", accountName)
        ADOUtilities.AddParamToSproc(cmd, "@Contract", contractName)
        ADOUtilities.AddParamToSproc(cmd, "@EmployeeName", "")
        ADOUtilities.AddParamToSproc(cmd, "@Org", acctType)
        ADOUtilities.AddParamToSproc(cmd, "@HoursVal", "TRUE")
        ADOUtilities.AddParamToSproc(cmd, "@Sort", "1")
        ADOUtilities.AddParamToSproc(cmd, "@Employer", "")
        ADOUtilities.AddParamToSproc(cmd, "@Type", "1")
        ADOUtilities.AddParamToSproc(cmd, "@FromD", asOfDate.ToShortDateString())
        ADOUtilities.AddParamToSproc(cmd, "@ToD", asOfDate.AddMonths(-5).ToShortDateString()) 
        ADOUtilities.AddParamToSproc(cmd, "@SessionID", sessionID)
        ADOUtilities.AddParamToSproc(cmd, "@Clin", clin)
        ADOUtilities.AddParamToSproc(cmd, "@RptDisplay", "")
        ADOUtilities.AddParamToSproc(cmd, "@Parm_DT", dollarDisplay)

        Dim lst = New List(Of sproc_RptEAComment_Row)
        Dim rs = cmd.Execute()
        While Not (rs.EOF)
            Dim newEntity = New sproc_RptEAComment_Row(rs)
            lst.Add(newEntity)
            rs.MoveNext()
        End While
        Return lst

    Catch ex As Exception
        MsgLogger.Err(ex)
        Return Nothing
    End Try
End Function

如果我在执行后立即查看调试器，则会看到此信息。请注意，字段EacJustCom具有正确的字符串值：

If I look in the debugger immediately after the Execute I see this. Note the field EacJustCom has the proper string value:

我在调试器中迈了一步，看到了这一点。价值不复存在。请注意，字段 _Account仍然完整（定义为varchar（100））：

I take one step in the debugger and see this. THe value is gone. Note the field "_Account" is still intact (it's defined as varchar(100)):

推荐答案

我从未找到问题的答案，但确实找到了解决方法。如果我先创建记录集并从命令中填充它，它就会起作用。

I never found the answer to the problem, but did find a workaround. If I create the recordset first and fill it from the command, it works.

        Dim rs As New ADODB.Recordset()
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
        rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
        rs.StayInSync = False
        rs.Open(cmd)
        rs.ActiveConnection = Nothing

        Dim lst = New List(Of sproc_RptEAComment_Row)
        While Not (rs.EOF)
            Dim newEntity = New sproc_RptEAComment_Row(rs)
            lst.Add(newEntity)
            rs.MoveNext()
        End While
        Return lst

这篇关于无法使用ADODB从varchar（max）中读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！