sql-servervb.netadodbvarcharmax

Can't read data from varchar(max) using ADODB

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.

Here is the code:

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

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

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)): enter image description here

Solution

  • 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