Recordset becomes NULL after the first use

[hehe panda]

I have Access as FrontEnd and SQL server as Backend. After I call a stored procedure on the SQL server, I try to read the fields in the recordset. However, one of the column can only be used once. After the first use/read, it becomes NULL for no reason.

-----VBA in Access------
strSQL = "EXEC SP_GetDetails '" & strInput & "'"
Set recSQL = ConnSQL.Execute(strSQL)

Do While Not recSQL.EOF
    Debug.Print "Customer = " & recSQL!Customer
    Debug.Print "Customer again = " & recSQL!Customer
    ...
    recSQL.MoveNext
Loop

-----Stored Procedure in SQL Server-----
CREATE PROCEDURE SP_GetDetails
    @RQFNumInput varchar(20)
As
    select Customer From List where Number =   @RQFNumInput Order by Date Desc

-----------------------------------------

The problems is the first output gives the value and the second output shows nothing.
Customer1 = My Customer
Customer2 =

This is the same to the following thread. However, there was not answer to it.

http://bytes.com/topic/access/answers/198611-ado-recordset-field-becoming-null

Please help!

[pod]

I was getting the same problem with VB Script on some external ASP web servers. When I was trying to access the same field value more than one time within one loop of a recordset, it would sporadically fail after the first time.

so for a solution, I set the fieldvalue to a variable and then I could access that variable multiple time.

I make it a habit of doing this most of the time now

dim fldVal as variant ' for versality
Do While Not recSQL.EOF
    fldVal = recSQL!Customer
    Debug.Print "Customer = " & fldVal 
    Debug.Print "Customer again = " & fldVal 
    ...
    recSQL.MoveNext
Loop

[hehe panda]

Thanks a lot for the reply. I actually thought about it but couldn't use this method because the column might be NULL. So I always has to test it by using:

If not isnull(recSQL!C ustomer) then
--the value is set to NULL after I test it :(
...
Endif

If the column is null, I can't use fldVal = recSQL!Customer any more. A NULL cannot be assigned to a variable like that.

[pod]

how about this then ...

  
Function txt(ByVal obj) As String
    Dim objReturn As Object
    objReturn = obj
    If IsDBNull(obj) Then
        objReturn = ""
    End If
    txt = objReturn
End Function

Dim fldVal As Variant ' for versality
Do While Not recSQL.EOF
    fldVal = txt(recSQL!Customer)
    Debug.Print "Customer = " & fldVal
    Debug.Print "Customer again = " & fldVal
    '...
    recSQL.MoveNext
Loop

[hehe panda]

I can't use IsDBNull(obj). I always got "Sub or Function is not defined." error message. Is there any library I need to refer to?

[pod]

sorry, I took code that was modified for VB.NET ... use IsNull instead

#
#     '...
If IsNull(obj) Then
    objReturn = ""
End If

[hehe panda]

Sorry for the late reply and thank you for your suggestion. However, it doesn't seem to work. If obj will be changed to NULL after <code>objRetu rn = obj</code>, then the IsNull(obj) will return true no matter what the original values it.

What is more weird to me is that even objReturn can not be called twice. It changes too!!!

[hehe panda]

So I have adapted the most stupid but working solution: I insert the obj into a local table and the read from there. The value will never disappear again :)

[pod]

Glad you found a solution...

I know it looks like we are beating a dead horse here but I just noticed an error in my code, again :)

we are supposed to use the objReturn once it is set and not the obj

#
 Function txt(ByVal obj) As String
     Dim objReturn As Object
     'here at this point the value is set to objReturn 
     objReturn = obj
     'and we do not use obj anymore
     If IsNull(objReturn) Then   'as opposed to...IsNull(obj) 
         objReturn = ""
     End If
     txt = objReturn
 End Function

[hehe panda]

Thanks. As I mentioned, the objReturn changes itself to NULL too after the first call. It really bothered me to see such weird behavior. I am using Access 2007 and SQL server 2008 R2. Not sure if there is any system or application bugs.

[Damian Wise]

I think that I may have discovered the cause, that is varchars which are too large. I had the problem with changing data when my fields were varchar(1022) and I converted them to varchar(512) and the data no longer changed. I am using ADO 2.5 in Excel, so perhaps later versions do not have this bug.