Solved

NULL RecordSet in ADO,  VB Bug???

Posted on 2000-05-10
27
615 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 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