NULL RecordSet in ADO, VB Bug???

I am using ADO to get a recordset.
I know the data is not Null and is a regular string.
If I say:
Form1.rtbNotes = adoRS("Notes")
then it works fine and puts the data in the text box.

If I say:

If IsNull(adoRS("Notes")) Then
             Form1.rtbNotes = "This is Null"
 Else
            Form1.rtbNotes = adoRS("Notes")  ' Is Not Null but gives Null Error
End If

then it drops to the ELSE section (because it's not Null) and then
gives a runtime error which says "Invalid Use of Null" !!!!
Upon placing mouse over adoRS("Notes") during debug it says Null.

Why can I put the statement by itself and it works!
But when I put in a check for Null then it  gives runtime error!
-=<<< Weird! >>>=-

P.S. Sorry bout low points. All I have ;-)
jgoreAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gaetansavoieConnect With a Mentor Commented:
When you open your recordset, be sure to use (fowardonly and readonly).  Mabe you open it openkeyset and the record gets updated to null.

yourRS.Open yourSQL,yourConnection,adOpenForwardOnly,adLockReadOnly

As you saw in all comments, the isnull should be working.

My last suggestion is this


adoRS.Open yourSQL,yourConnection,adOpenForwardOnly,adLockReadOnly
if not (adoRS.eof and adoRS.bof) then
   If IsNull(adoRS("Notes")) Then
             Form1.rtbNotes = "This is Null"
       Else
            Form1.rtbNotes = adoRS("Notes")  ' Is Not Null but gives Null Error
    End If
end if


0
 
gaetansavoieCommented:
Strange problem. the Isnull should have worked.

What database are you using?  if you are using Orcale, a neat SQL function you could use for example is

sSQL="SELECT NVL(notes,' ') as notes FROM yourTABLE where yourid=" & theid

Which would replace any null value.

0
 
jgoreAuthor Commented:
I'm using Access 2000 and VB6.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jgoreAuthor Commented:
I'm using Access 2000 and VB6.
0
 
AzraSoundCommented:
try this:

If IsNull(adoRS("Notes")) Then
             Form1.rtbNotes = "This is Null"
 Else
            Form1.rtbNotes = "" & adoRS("Notes")  ' Is Not Null but gives Null Error
End If



0
 
jgoreAuthor Commented:
It shows nothing now!
I know the data is there!
When I put:

Form1.rtbNotes = adoRS("Notes")

it works! It looks fine! But if I use
an IsNull() function it skips to the
Else and gets a null error saying "invalid use of Null".  And yes I tried it backwards with
Not IsNull ..... same difference. Beats me!

I even tried a delay loop to possibly wait for the data from the SQL statement, thinking maybe it
hadn't arrived in time or something. No change.

0
 
jgoreAuthor Commented:
It shows nothing now!
I know the data is there!
When I put:

Form1.rtbNotes = adoRS("Notes")

it works! It looks fine! But if I use
an IsNull() function it skips to the
Else and gets a null error saying "invalid use of Null".  And yes I tried it backwards with
Not IsNull ..... same difference. Beats me!

I even tried a delay loop to possibly wait for the data from the SQL statement, thinking maybe it
hadn't arrived in time or something. No change.

0
 
ChewAhBengCommented:
Did you initialise your ADO in code or on the control in the form?
Need to have a look at the code.
0
 
AzraSoundCommented:
instead of IsNull try if = vbNullString
0
 
ChewAhBengCommented:
well i tried and i had no problem with it?

Private Sub Form_Load()
Dim adoRs As Recordset
Adodc1.Refresh
Set adoRs = Adodc1.Recordset
' using biblio.mdb
If IsNull(adoRs("Author")) Then
      Form1.rtbNotes = "This is Null"
 Else
 Form1.rtbNotes = "" & adoRs("Author")  End If
End Sub
0
 
ChewAhBengCommented:
well i tried and i had no problem with it?

Private Sub Form_Load()
Dim adoRs As Recordset
Adodc1.Refresh
Set adoRs = Adodc1.Recordset
' using biblio.mdb
If IsNull(adoRs("Author")) Then
      Form1.rtbNotes = "This is Null"
 Else
 Form1.rtbNotes = "" & adoRs("Author")  End If
End Sub
0
 
ChewAhBengCommented:
well i tried and i had no problem with it?

Private Sub Form_Load()
Dim adoRs As Recordset
Adodc1.Refresh
Set adoRs = Adodc1.Recordset
' using biblio.mdb
If IsNull(adoRs("Author")) Then
      Form1.rtbNotes = "This is Null"
 Else
 Form1.rtbNotes = "" & adoRs("Author")  End If
End Sub
0
 
AzraSoundCommented:
go easy on the refresh button there chewah  =)
0
 
ChewAhBengCommented:
Thanks Azra! Been quite upset about Visual Basic lately...
0
 
wsh2Commented:
To fix the problem.. change your CursorLocation to adUseClient.. <smile>.
0
 
jgoreAuthor Commented:
' The whole enchilada
Public Sub DisplayNodeInfo(ThisNodeKey As String)

Dim SQLCommand As String
Dim PicName As String
Dim i As Integer

' On Error Resume Next

ThisNodeKey = Val(ThisNodeKey)
SQLCommand = "SELECT Item,Notes,ImageName FROM " & _
CurrentTable & " WHERE Key = " & ThisNodeKey
           
adoCmd.CommandText = SQLCommand
Set adoRS = adoCmd.Execute  
     
   ' Can't use IsNull here!
   Form1.rtbCodeWindow.TextRTF = adoRS("Item")
   Form1.rtbNotes.TextRTF = adoRS("Notes")

   ' Works on this one below!
   If IsNull(adoRS("ImageName")) Then PicName = "" Else PicName = "" & CStr(adoRS("ImageName"))
   


'  Do other stuff down here......

End Sub
0
 
AzraSoundCommented:
you may also try putting brackets around those column names...

select [item],[notes],....
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I recommend to avoid using default properties. IsNull on Field object will never return false!

So try this:

If IsNull(adoRS("Notes").Value) Then
  Form1.rtbNotes = "This is Null"
 Else
  Form1.rtbNotes = "" & adoRS("Notes")  ' Is Not Null but gives Null Error
End If
0
 
sergedornseifferCommented:
Ensure that you have the latest version of the MDAC_TYPE installed

http://www.microsoft.com/data/download_21242023.htm
0
 
sergedornseifferCommented:
Have you tried reading the value first into a string variable and then affect it to the control

Dim mResult as string

mResult adoRS("Notes").Value

Form1.rtbNotes = mResult
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hi serge.
Welcome at EE
FYI: If you don't want to have ennemies in EE, don't copy comments like that :-)


0
 
aidan_gillCommented:
Possibly that you are using the ISNULL on a char field maybe throwing it.

rgds
aidan
0
 
jgoreAuthor Commented:
I have tried everything on this page and still get
the same. Could it be that there is a difference
between type=String and type=Memo in Access2000 ?
I thought they were treated the same except Memo can be much larger. And, I have no idea why an IsNull
statement would make any difference!

Still perplexed........

0
 
jgoreAuthor Commented:
When the value IS NULL, that is, nothing has ever been put in database for that value then I get:

Error: [Microsoft][ODBC Microsoft Access Drivers] Too
Few Parameters. Expected 1.

And:
"Ado Couldn't find the object in the collection corr....."
when it gets to line

If IsNull(adoRS("Item").Value) Then

0
 
jgoreAuthor Commented:
Forget that last entry, very sorry. That was a different small bug I fixed. I forgot to add a certain column in that table.

I am going to make a very small test project to reproduce the IsNull error. I will post an address
when it's done. It will be very small.
0
 
jgoreAuthor Commented:
' Here is a whole program that demonstrates the error.
' On a form put 3 buttons and a textbox.
' Add REFERENCE - MS Activex Data Objects 2.1 library
' Use MS Access2000 to make database with one table.
' Table name = "table1".
' One column called "field1"
' Two entries (records) of type Memo!
' Entry one leave blank,entry two enter some text.
' Make note of ID number and see button2-3.
' Note Error in DisplayInfo when program is run.
' Thats it!



' ================ Form1 =============
Private Sub Command1_Click() ' Quit
EndDB
End
End Sub

Private Sub Command2_Click() ' Show Null Info
DisplayInfo (22) ' Change to right ID number!
End Sub

Private Sub Command3_Click() ' Show Non-Null Info
DisplayInfo (23)  ' Change to right ID number!
End Sub

Private Sub Form_Load()
gblConnectString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\db1.mdb"
gblNewCode = True 'start off with a clean slate
StartDB           ' start the database connection
Command1.Caption = "Quit"
Command2.Caption = "Show Null"
Command3.Caption = "Show Non-Null"
End Sub


' ============ Module1 ==============
Global gblNewCode As Boolean
Global gblConnectString As String
Global adoCon As Connection
Global adoCmd As Command
Global adoRS As Recordset

' -------------------------------------------------------
Public Sub StartDB()
'Connect to the database and retrieve the valid code types
 
On Error GoTo errHandler

    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Open gblConnectString
    Set adoCmd = CreateObject("ADODB.Command")
    adoCmd.ActiveConnection = adoCon
 
Exit Sub
errHandler:
   MsgBox "Error: " & Err.Description & "Start DB"        'just incase anything went wrong
   
End Sub

' -------------------------------------------------------
Public Sub EndDB()

On Error GoTo errHandler

    'Clean House
  If Not (adoRS Is Nothing) Then
       adoRS.Close
       Set adoRS = Nothing
       Set adoCmd = Nothing
  End If
 
  If Not (adoCon Is Nothing) Then
       adoCon.Close
       Set adoCon = Nothing
  End If
 
 
Exit Sub
errHandler:
   MsgBox "Error: " & Err.Description & " - End DB"        'just incase anything went wrong

End Sub
' ------------------------------------------------------
Public Sub SendSQL(ThisCommand As String)

On Error GoTo errHandler

adoCmd.CommandText = ThisCommand
Set adoRS = adoCmd.Execute

Exit Sub
errHandler:
   MsgBox "Error: " & Err.Description & " - Send SQL"        'just incase anything went wrong

End Sub
' -------------------------------------------------------
Public Sub DisplayInfo(ThisID As String)

Dim SQLCommand As String
Dim CurrentTable As String

CurrentTable = "[Table1]"

SQLCommand = "SELECT Field1 FROM " & CurrentTable & " WHERE Id=" & ThisID
SendSQL (SQLCommand)
       
   ' This works Only if it's NOT Null
   ' Form1.Text1.Text = adoRS("Field1").Value
 
  ' This Works ONLY if it Is Null!
   If IsNull(adoRS("Field1").Value) Then
     Form1.Text1.Text = "This is Null"
   Else
     Form1.Text1.Text = adoRS("Field1").Value
   End If
     
  End Sub
' ============= END SOURCE ============
0
 
jgoreAuthor Commented:
Fixed it!
For some reason the MoveFirst command seems to
fix it! I assume because the first read updates it to Null.
Very strange.
I give  gaetansavoie  my pitifull points because I beleive he came closest. Thanks everyone for your help! I really mean it. You all gave me lots to check and think about. Thanks!



' ============ New Routine that works!
adoRS.MoveFirst
   
   If IsNull(adoRS("Item").Value) Then
     Form1.rtbCodeWindow = ""
   Else
     adoRS.MoveFirst
     Form1.rtbCodeWindow = adoRS("Item").Value
   End If
   
   
   If IsNull(adoRS("Notes").Value) Then
     Form1.rtbNotes = ""
   Else
     adoRS.MoveFirst
     Form1.rtbNotes = adoRS("Notes").Value
   End If
   
   
   If IsNull(adoRS("ImageName").Value) Then
      PicName = ""
   Else
      adoRS.MoveFirst
      PicName = adoRS("ImageName").Value
   End If
 ' ============================
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.