Check for NULL then....

I am accessing an Excel sheet via ADO. No problems there.

What I am doing now is taking the sheet data and inserting it into an SQL Table and I'm having a problem with NULL

Invalid Use Of NULL

Code:

---

Dim clid as Integer
clid = IIf(IsNull(rstExcel.Fields("clientid")), 9999, CInt(rstExcel.Fields("clientid")))

---

I would think that if I am evaluating NULL then If NULL giving a value, else Converting it to INT I would get what I want. Debug shows ClientID as NULL.

Don't use IIf() for this operation, use a full If statement.

IIf() is designed to evaluate all three expressions before returning a value, so it will fail on the Null value (3rd argument here).

Yeah, it's a "flaw" of sorts in the VB6 implementation of the IIF() function.... the fact that it is a function means that all three of the parameters get evaluated... you'll need to use a full If statement as dilettante has suggested. PITA I know, but it is what it is.

-tg
side note - in .NET this has been fixed with the If() ternary function... which evaluates the condition FIRST... then and only then determines which of the other two expressions to then evaluate.

Thanks all,

Added a Function

Code:

---

Function IfNull(value As Variant, Optional NullValue As Variant = "") As Variant
    If IsNull(value) Then
        IfNull = NullValue
    Else
        IfNull = value
    End If
End Function

---

Then

Code:

---

clid = IfNull(rstExcel.Fields("clientid"), 9999)

'In my SQL String
CInt(clid)

---

Quote:

---

Originally Posted by techgnome

side note - in .NET this has been fixed with the If() ternary function... which evaluates the condition FIRST...

---

Do you happen to know how this is implemented internally? It can't be a regular function, though it makes the language grammar uglier to make it syntactic sugar for a full If block. I suppose it could be implemented by passing closures instead, but that seems over the top in an imperative language. (I only use C#.NET which uses the c ? t : f syntax instead, which is just fine as a feature of the grammar.)

I would suspect it is just compiled as an inline construct rather than any sort of function or method call at all.

I.e. just syntactic sugar hiding an If statement.

No, I don't know the specifics of how it's implemented under the hood. I think dilettante is right... during compile it gets expanded out... almost like a compiler macro in C. the If terniary function is essentially the VB version of the c ? t : f construct. It isn't any different.

-tg

Well, we seem to agree--we don't know, but it's almost certainly just syntactic sugar. Sounds good :).