Check for NULL then....

[Always_Confused]

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

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.

[dilettante]

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).

[techgnome]

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.

[Always_Confused]

Thanks all,

Added a Function

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

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

'In my SQL String
CInt(clid)

[jemidiah]

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.)

[dilettante]

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.

[techgnome]

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

[jemidiah]

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