simple error

[dvst8]

Hi. I'm stepping through a record set from a database, such as in the following code:

oRS.MoveFirst
Do while NOT oRS.eof
Dim fx as string
fx = oRS("FaxNumber")
oRS.MoveNext
Loop
oRS.Close


But in my database some of the faxnumber fields are empty. This is causing the following error:

"Runtime error 94: Invalid use of null"

I tried putting an if statement like

if oRS("FaxNumber") is NOT NULL then..

but that didn't work, for the same reasons...

I don't know enough about VB to know how to handle this... seems this worked fine when I did it in ASP...

tx
d8

[Jimbob]

string variables don't like being set to null, same for text boxes. it can be a pain. the easiest way i've found for getting round it is: -

if isnull(oRS("FaxNumber")) = false then
fx = oRS("FaxNumber")
oRS.MoveNext
Loop
oRS.Close

i don't know why 'is not null' and '<> null' don't work. maybe someone out there who knows could explain it....

[SkippySolutions]

Hey dvst

This is a normal problem that everyone struggles thorugh when doing database work for the first time. Here is a quick peice of code that I wrote that illeviates that problem:


Function strClean(pvarString As Variant) As String

If IsNull(pvarString) Or IsEmpty(pvarString) Then
strClean = ""
Exit Function
End If

strClean = Trim(CStr(pvarString))

End Function


Use it like this:


oRS.MoveFirst
Do While NOT oRS.EOF
Dim fx as string
fx = strClean(oRS!FaxNumber)
oRS.MoveNext
Loop
oRS.Close


Hope this helps!

[dvst8]

thanks jimbob, and skippy!

skippy: nice work with that code! I'm implementing it!!
tx