VB6 ADODB.Recordset.Field = NULL Error

VB6 ADODB.Recordset.Field = NULL Error

Post by A. Scott Whit » Fri, 03 Sep 1999 04:00:00



Last night, I discovered something:

MyRecordSet.Field("Age") = NULL
MyRecordSet.Update
If IsNull(MyRecordSet.Field("Age")) Then
  MsgBox "It worked!"
Else
  MsgBox "It didn't work. Dang!."
End If

The second pithy message box always pops up. The IsNull always returns
false. However, the Database does contain a Null! That's great, or at
least less sucky than when I thought it wasn't working at all. Now my
program works, it just doesn't know it.

It's like the Value property of the Field object gets filled with some
value (possibly Empty or Nothing) that VB doesn't recognize as a Null,
even though a Null is inserted into the database. When I hover above
the field (in debug mode), no ToolTip appears to show me a value. When
I hover over any other field, the value is displayed in a ToolTip.
When I hover over a field that is recognized as Null (with IsNull) a
ToolTip with the word "Null" appears. When I hover over a field that
is recognized as Empty (with IsEmpty) a ToolTip with the word "Empty"
appears. When I hover over a field that is recognized as Nothing (with
Is Nothing) a ToolTip with the word "Nothing" appears.

This is something else, an anomaly. I wonder if I'm opening some
space/time vortex. I'm not sure, but I will research more. Perhaps I'm
mad to delve into these things, but I must know. My assistant is
obviously concerned, and scared...THE FOOL! Can't he see that we are
on the verge of one of the greatest programming discoveries of all

THEM ALL!!! They'll know I was right when I break through! I'm close!
I KNOW it!! I can FEEL it!!!!!

[Editor's note: This was the last log entry...the last known words of
Mr. White. As of today, his whereabouts are unknown.]

No, but seriously, has anyone ever seen this behavior before?

==================================
A. Scott White
Senior Systems Analyst
Affiliated Computer Services, Inc.

==================================

 
 
 

VB6 ADODB.Recordset.Field = NULL Error

Post by Doug Steel » Fri, 03 Sep 1999 04:00:00


Just out of curiosity, what happens if you change the instruction to:

   MsgBox "It didn't work. Instead, you've got " &
MyRecordSet.Field("Age")


> Last night, I discovered something:

> MyRecordSet.Field("Age") = NULL
> MyRecordSet.Update
> If IsNull(MyRecordSet.Field("Age")) Then
>   MsgBox "It worked!"
> Else
>   MsgBox "It didn't work. Dang!."
> End If

> The second pithy message box always pops up. The IsNull always returns
> false. However, the Database does contain a Null! That's great, or at
> least less sucky than when I thought it wasn't working at all. Now my
> program works, it just doesn't know it.

> It's like the Value property of the Field object gets filled with some
> value (possibly Empty or Nothing) that VB doesn't recognize as a Null,
> even though a Null is inserted into the database. When I hover above
> the field (in debug mode), no ToolTip appears to show me a value. When
> I hover over any other field, the value is displayed in a ToolTip.
> When I hover over a field that is recognized as Null (with IsNull) a
> ToolTip with the word "Null" appears. When I hover over a field that
> is recognized as Empty (with IsEmpty) a ToolTip with the word "Empty"
> appears. When I hover over a field that is recognized as Nothing (with
> Is Nothing) a ToolTip with the word "Nothing" appears.

> This is something else, an anomaly. I wonder if I'm opening some
> space/time vortex. I'm not sure, but I will research more. Perhaps I'm
> mad to delve into these things, but I must know. My assistant is
> obviously concerned, and scared...THE FOOL! Can't he see that we are
> on the verge of one of the greatest programming discoveries of all

> THEM ALL!!! They'll know I was right when I break through! I'm close!
> I KNOW it!! I can FEEL it!!!!!

> [Editor's note: This was the last log entry...the last known words of
> Mr. White. As of today, his whereabouts are unknown.]

> No, but seriously, has anyone ever seen this behavior before?

> ==================================
> A. Scott White
> Senior Systems Analyst
> Affiliated Computer Services, Inc.

> ==================================

--

Beer, Wine and Database Programming.  What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://webhome.idirect.com/~djsteele/

 
 
 

VB6 ADODB.Recordset.Field = NULL Error

Post by Jim in Clevelan » Sat, 04 Sep 1999 04:00:00



> Last night, I discovered something:

> MyRecordSet.Field("Age") = NULL
> MyRecordSet.Update
> If IsNull(MyRecordSet.Field("Age")) Then
>   MsgBox "It worked!"
> Else
>   MsgBox "It didn't work. Dang!."
> End If

> The second pithy message box always pops up. The IsNull always returns
> false. However, the Database does contain a Null!

  <snip>

  It is unfortunate that you discovered this, Mr. White.  For now, you
know too much, and that knowledge jeopardizes the agenda of the
Organization...
  Seriously, dude, ya gotta get outta the lab!  I think this was Doug's
point about displaying MyRecordSet.Fields("Age") - you'd see the value.
  A few things:  when you do the Refresh, you reload the recordset - so
you are now positioned at the first record, which is not necssarily the
one where you set the field value to Null.  
  Another thing - some updates are not instantaneous, so your code may
evaluate the "If IsNull" before the update actually occurs.
  Plus, using ADO on an Access97 db, the Refresh actually seems to
interfere with update - I set up a grid bound to an ADO DataControl, and
made a command button to set one field of the selected row to Null (like
the start of your code).  When I included the Refresh statement, the
grid would not show that the field had changed to Null (even though I
could open the table in Access and see it was now blank).  When I took
out the Refresh, after the field was set to Null the grid updated just
fine to show that field go blank.  
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"

"What's so funny 'bout peace, love & understanding?"
     - Nick Lowe

 
 
 

1. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

Dear Guru's,

When I make an ADODBconnection to Excel files with Jet 3.51 and execute a
SQLquery statement to an ADODBrecordset I have the following issue:
If there are numeric values in general cellformat the
recordset.fields(i).value=null. If I make the cellformat=text then the
returned value's aren't NULL.
What is the problem? Should I change some recordset properties?
If yes: what, how?

2. NY-NEW YORK-86754--Visual Basic-OLE-ORACLE-Windows NT-Senior DEVELOPER

3. VB6 - MSSQL Server 7 - ADO - Assigning NULL to field in RecordSet object

4. Progress window

5. Creating adodb.Recordset that allows NULLs.

6. How to update a column in a table wich is a counter with sql?

7. Preventing inserts - dictionary based

8. ADODB.connection & ADODB.Recordset

9. ADODB.Connection.Execute VS ADODB.Recordset.Update

10. .Close on ADODB.Connection and ADODB.RecordSet

11. How to get XML in an ADODB.Recordset or ADODB.Stream object