Database result is passed as null unless it's read

Question

Set rslistings = my_conn.Execute(strSQL)

Do while NOT rslistings.Eof
    description = strip(rslistings("description"))
rslistings.MoveNext
loop

In strip - NULL is being passed. However, if I attach a debugger and inspect the contents of rslistings("description"), then the actual Field object is passed through

It's quite old asp code, but it works on IIS6, just not IIS7

EDIT This only happens on the "description" field with is a text type (MySQL database)

strip doesn't do a lot:

If NOT IsNull(passedinvalue) Then 
    // do something
Else
    // do something else

If I call strip like strip(rs("description")), it is never null as the Field object is passed in. If I assign it to another value, then pass it in (like strip(mynewvar)) then the correct value is passed in.

Edit - database bits as requested below

Set my_conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
my_conn.Open "DSN=mydb"

SQL

Set rs = my_conn.Execute("SELECT description FROM table")

This only happens on the "description" field with is a text type (MySQL database) — Paul, Mar 16 '11 at 15:04
Have you tried: `description = strip(rslistings("description") & "")`? — Shadow The GPT Wizard, Mar 17 '11 at 10:23
yeah, that works - but it doesn't really explain why the existing code doesn't work... — Paul, Mar 21 '11 at 14:52

score 1 · Answer 1 · answered Mar 22 '11 at 08:56

the Field Collection is the default member of the Recordset object.

so is the value property for the Field object.

so the following two code statements are equivalent.

Debug.Print objRs.Fields.Item(0)  ' Both statements print 
Debug.Print objRs(0)              '  the Value of Item(0).

it is a difference if you assign a value to a variable or use it as a parameter in a function.

score 0 · Answer 2 · answered Mar 16 '11 at 16:00

0

@Paul: If strip doesn't check if description is NULL before working on it, you could do this --

Do while NOT rslistings.Eof
    description = rslistings("description")

    If NOT IsNull(description) Then 
        description = strip(description)
    Else
        description = "" ' or you could have description = "&nbsp;" 
                         ' if you output to screen later on
    End If
rslistings.MoveNext
loop

answered Mar 16 '11 at 16:00

stealthyninja

10,343
11
51
59

Ah, you've actually come across my issue. the first thing strip does is check for null (IF NOT ISNULL(passedinvalue)). However, passing rslistings("description") means passedinvalue is never null as it seems to be passing in the Field object, not the value. Passing in rslistings("description").Value passed in the correct thing - but I can't do this (sooo much code to change!) – Paul Mar 16 '11 at 16:36
@Paul: That's really odd...could you perhaps post how you create and set `my_conn`, the connection string you use and the SQL query you're running? – stealthyninja Mar 17 '11 at 21:13

Database result is passed as null unless it's read

2 Answers2