Solved

NULL RecordSet in ADO,  VB Bug???

Posted on 2000-05-10
27
613 Views
Last Modified: 2013-11-26
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 ;-)
0
Comment
Question by:jgore
  • 10
  • 5
  • 4
  • +5
27 Comments
 
LVL 2

Expert Comment

by:gaetansavoie
ID: 2798272
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
 

Author Comment

by:jgore
ID: 2798293
I'm using Access 2000 and VB6.
0
 

Author Comment

by:jgore
ID: 2798304
I'm using Access 2000 and VB6.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2798514
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
 

Author Comment

by:jgore
ID: 2798708
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
 

Author Comment

by:jgore
ID: 2798709
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
 

Expert Comment

by:ChewAhBeng
ID: 2798757
Did you initialise your ADO in code or on the control in the form?
Need to have a look at the code.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2798773
instead of IsNull try if = vbNullString
0
 

Expert Comment

by:ChewAhBeng
ID: 2798826
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
 

Expert Comment

by:ChewAhBeng
ID: 2798835
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
 

Expert Comment

by:ChewAhBeng
ID: 2798842
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
 
LVL 28

Expert Comment

by:AzraSound
ID: 2798843
go easy on the refresh button there chewah  =)
0
 

Expert Comment

by:ChewAhBeng
ID: 2798938
Thanks Azra! Been quite upset about Visual Basic lately...
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

Expert Comment

by:wsh2
ID: 2798978
To fix the problem.. change your CursorLocation to adUseClient.. <smile>.
0
 

Author Comment

by:jgore
ID: 2799018
' 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
 
LVL 28

Expert Comment

by:AzraSound
ID: 2799030
you may also try putting brackets around those column names...

select [item],[notes],....
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2799078
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
 

Expert Comment

by:sergedornseiffer
ID: 2799536
Ensure that you have the latest version of the MDAC_TYPE installed

http://www.microsoft.com/data/download_21242023.htm
0
 

Expert Comment

by:sergedornseiffer
ID: 2799540
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2799583
hi serge.
Welcome at EE
FYI: If you don't want to have ennemies in EE, don't copy comments like that :-)


0
 
LVL 2

Accepted Solution

by:
gaetansavoie earned 21 total points
ID: 2799843
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
 
LVL 2

Expert Comment

by:aidan_gill
ID: 2799944
Possibly that you are using the ISNULL on a char field maybe throwing it.

rgds
aidan
0
 

Author Comment

by:jgore
ID: 2806597
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
 

Author Comment

by:jgore
ID: 2807328
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
 

Author Comment

by:jgore
ID: 2807371
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
 

Author Comment

by:jgore
ID: 2807453
' 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
 

Author Comment

by:jgore
ID: 2807557
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now