error 94, Invalid use of NULL

[vovan1415]

Sometimes my users get the followign error:

error 94
Invalid use of null

But problem is there are just some users who got this error
I am using :

Public Function RNull(sIn As Variant) As String
If IsNull(sIn) Then
RNull = ""
Else
RNull = sIn
End If
End Function

to avoid the error.

The code is:
=================================================
Private Sub InsertNewIllustrationNumberRoyalty()
On Error GoTo ERRORHANDLER
Dim sSQL As String
Dim lRecordsAffected As Long

Screen.MousePointer = vbHourglass

lRecordsAffected = clsDB.Insert(RNull(txt.illustrationID.text))

If lRecordsAffected <> 1 Then
MsgBox "There was an error."
bError = True
End If

Set rs = New ADODB.Recordset
sSQL = "SELECT @@IDENTITY " & vbCrLf
rs.Open sSQL, CnDB, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
glCardArtID = CLng(rs.Fields(0).Value)
Else
bError = True
End If

If glCardArtID = 0 Then
bError = True
End If

rs.Close

Screen.MousePointer = vbNormal

Exit Sub

ERRORHANDLER:

bError = True

goErr.ErrorType = iTYPE_FATAL
goErr.Source = "InsertNewIllustrationNumberRoyalty"
goErr.OriginalError = Err.Number
goErr.Msg = Err.Description
goErr.Additional = "Line: " & Erl
goErr.Title = sEmpty
goErr.SQL = sEmpty

goErr.DisplayError

Screen.MousePointer = vbNormal
End Sub
===================================================

Please help...

 

[stoolpigeon]

the line

glCardArtID = CLng(rs.Fields(0).Value)

would throw that error if the field is null.

 

[zemp]

Two ways that you can handle this. First trap for the error and use

Resume Next

to continue in the code (or whatever action you prefer). The second method, which I prefer, is to check if the offending value is null and provide and alternative. Something like this,

glCardArtID = CLng(IIF(IsNull(rs.Fields(0).Value),0,rs.Fields(0).Value)

This says that if the field is a null then substitute a zero(0) in its place. What you substitute is up to you. I chose zero because you are feeding a numeric variable, for a string I would substitute "" (zero length string).


Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[SonOfEmidec1100]

Three ways - You could also change the query

select isnull(@@identity,0)

 

[LostInCode]

Four:

For an Integer (tiny, short, or long) field:

MyNum = Val(Format$(rs.Fields(0).Value))

This will also work for numbers with decimal places, if the decimal seperator is in US format.

For text fields just use:

MyString = Format$(rs.Fields(0).Value)

which will convert a NULL to a zero length string.

 

[northrd]

Yeah, but look at the cpu usage. Sometimes more code is OK. What's wrong with

if !field & "" <> "" then
use it
else
dont use it
end if

... or even
variable = !field & ""

Just a thought

 

[LostInCode]

Sure you can, for string data.

But if speed is a concern, this will be faster:

= rs.Fields(0).Value & vbNullString

and this even more so:
= rs.Collect(0) & vbNullString

But you cannot using just like that for numbers.
Then you need to do something like:

MyNum = Val(rs.Collect(0) & vbNullString)

or actually check for a null using IsNull()