Why does an ADODB.Command Object throw - "Invalid use of Null: 'replace'"

Question

I have some code that was using an ADODB.Recordset object to query a MariaDb database. In my query I am using coalesce such as this -

SELECT COALESCE(offers_owner.description, offers.description) AS description FROM
offers_owner LEFT JOIN websites on offers_owner.website_id = websites.id LEFT JOIN offers on
offers_owner.offer_id = offers.id WHERE offers_owner.id = 401

and in my code I have this -

If Not IsNull(rs("description")) or rs("description") <> "" Then
    response.write "<p class=" & chr(34) & "clear" & chr(34) & "><br />" & replace(replace(rs("description"),"company_name",session("company")),"company_city",session("city2")) & "<br /><br /></p>" & vbcrlf
end if

This works fine, and outputs as need be.

But, as soon as I switch to using an ADODB.Command object, I get an "invalid use of null". If I remove the conditional If Then, it does not throw and error.

Any idea as to why?

Thank you.

I tried to limit the conditionals in the IF Then statement

What do you do when you "switch to using an ADODB.Command object"? — GSerg, Jan 17 '23 at 22:30
Why are you using `IsNull()`? A null and a DB null are not equivalent. — user692942, Jan 18 '23 at 02:12
@GSerg I [know that](https://stackoverflow.com/questions/38927714/asp-classic-check-for-database-null-value-in-recordset#comment65216465_38927714). — user692942, Jan 18 '23 at 09:25
The Invalid use of Null error occurs when you try to assign a value to a variable or field that cannot accept Null. Means that it tries to get the value of a Null variable. You can only get the value of a variable that contains a valid value. — TengFeiXie, Jan 18 '23 at 09:41
@user692942 Then why are you suggesting that? Using `IsNull` is correct for checking for nulls in VBScript, VBA and VB6. Your comment here and your [other](https://stackoverflow.com/questions/38927714/asp-classic-check-for-database-null-value-in-recordset#comment65216465_38927714) [comments](https://stackoverflow.com/questions/38927714/asp-classic-check-for-database-null-value-in-recordset#comment65235806_38939731) are wrong. — GSerg, Jan 18 '23 at 10:29
@GSerg Guess [I've been doing it wrong](https://stackoverflow.com/a/33254589/692942) for 25+ years. — user692942, Jan 18 '23 at 10:36
Just simplify the null check `If Len(rs("description") & "") > 0 Then`. — user692942, Jan 18 '23 at 10:40
@user692942 Bummer. From your answer there it would appear you have been confusing `Null` and `vbNull`. `vbNull` is an integer constant that [equals to 1](https://stackoverflow.com/a/44123629/11683) and it has nothing to do with the special value `Variant/Null`. Concatenating `Null & ""` to get `""`, [like I said](https://stackoverflow.com/questions/38927714/asp-classic-check-for-database-null-value-in-recordset#comment132627762_38927714), is a just hack, which won't even let you distinguish between `Null` and `""`. — GSerg, Jan 18 '23 at 10:42
@GSerg Not at all, `vbNull` is a vartype constant. But how "null" is interpreted by different ADODB database providers isn't always equivalent to `IsNull()` in VBScript. It's far easier to just eliminate the need to check null rather than wrestling with different variants (`Len(value & "") > 0`). If you need to distinguish between `Null` and `""` then don't use this method. — user692942, Jan 18 '23 at 10:49
@user692942 The `.Value` property of a recordset returns a [`Variant`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/variant-data-type), which you may see as a "typeless" result, but actually it is [very strictly defined](https://learn.microsoft.com/en-us/windows/win32/api/oaidl/ns-oaidl-variant). It can contain the special values `Null`, `Empty`, `Nothing` and `Error`, but it cannot contain something that is sort of null, but isn't one of those. The hypothetical database provider could not simply put it there. — GSerg, Jan 18 '23 at 10:53
@GSerg In which case, why if they're checking `IsNull()` (as you suggest) is it still causing "invalid use of null"? — user692942, Jan 18 '23 at 11:34
I believe I figure it out, or at least a way to get it to work. myCMD.commandtext = "......." set rs = myCMD.execute description = rs("description").value if not isNULL(description) and description <> "" Then replace(description,"xyz","abc") end if — TMHDesign, Jan 18 '23 at 12:39
@user692942 `IsNull()` cannot and does not cause "invalid use of null". It is caused by passing `Null` arguments to `Replace`, of which there are three (`rs("description")`, `session("company")`, `session("city2")`). The last two are not even checked for null. — GSerg, Jan 18 '23 at 13:09
@GSerg good catch the session values could be null, again why I prefer to use `& ""` whether it's a hack or not. — user692942, Jan 18 '23 at 14:27

score -1 · Answer 1 · edited Jan 20 '23 at 17:41

-1

The way I solved this was to assign the value of the field in question to a variable, such as this:

description = myRS("description").value

if not isNULL(description) and description <> "" Then
replace(description,"xyz","abc")
end if

edited Jan 20 '23 at 17:41

Catarina Ferreira

1,824
5
17
26

answered Jan 18 '23 at 12:45

TMHDesign

171
1
3
11

1

Saving to a variable cannot be the solution by itself, unless your recordset returns different values each time you access a `.Value`. It is way more likely that it is solved by using `and` instead of `or` and (apparently) string literals instead of `session("company")` and `session("city2")` which themselves can be null. – GSerg Jan 18 '23 at 13:13
As @GSerg has said you have two session variables that you do not null check the likelihood is one or both of them are null causing the exception when calling `Replace()`. – user692942 Jan 18 '23 at 14:29
I have control over all session variables and they are never null. Bottom line is it works as I showed up above. It throws an error when both and / or are absent. If I remove the if conditional all together and pass the recordset value into the replace it works. It's the if statement throwing the error – TMH_Design Jan 18 '23 at 15:57
As already explained the code above works because you've taken the source of the null issue out of the equation (the session values). That is the only bit you have replaced, so it stands to reason the issue is there. Double-check your `session("company")` and `session("city2")` session values as one of them or both are `Null`. – user692942 Jan 18 '23 at 18:11

Why does an ADODB.Command Object throw - "Invalid use of Null: 'replace'"

1 Answers1