Vanishing values... Why!!!

[bubarooni]

Take a look at my code below and tell me why the value for isComment disappears and how to correct it. I haven't the foggiest idea what is going on here.

isComment is a memo field being pulled off an Access 2000 db. As soon as I check the value in the IF statement it immediately turns to NULL so the expression always evaluates to NULL whether there is a value initially or not. I can break execution on the line and have watched it change from a string to a NULL as I step through it.

Here is the code.
--------------------------------------------------------

If  Not IsNull(adoRecordset!isComment) Then txtisComment.Text = adoRecordset!isComment

--------------------------------------------------------
When I try the following code and pause execution on the first line it does the following:
1. Shows a value for !isComment and returns false and proceeds to the Else code.
2. When I check !isComment in the Else section it now shows Null and throws the dreaded 'Invalid Use of Null' error message at me.
----------------------------------------------------------

If IsNull(adoRecordset.Fields("isComment").Value) Then
      txtisComment.Text = ""
Else
      txtisComment.Text = adoRecordset!isComment
        
End If

-----------------------------------------------------------
I have other fields that are working just fine. It is just this one field that is throwing an error at me. Any ideas or lessons on Nulls will be greatly appreciated.

Thanks

 

[vb5prgrmr]

I belive you need to do...
[tt]
txtisComment.Text = adoRecordset.Fields("isComment&quot.Value
[/tt]

Good Luck

 

[bubarooni]

I tried that too. Same thing. I have it working right now by assiging a variable the value of isComment and then checking for NULL. Not very elegant and makes me wonder if my other fields will do the same thing since I can't fiure out why the IF statement changes isComment's value to NULL.

 

[johnwm]

Try this:

txtisComment.Text = "" & adoRecordset.Fields("isComment&quot.Value
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[SemperFiDownUnda]

I suggest you use one meathod of accessing field values
either
adoRecordset.Fields("isComment&quot.Value
or
adoRecordset!isComment

don't mix the 2 it leads to confusion. I've taught (because it is what microsoft say in the MOC) that the second meathod is supposed to be faster but I also have shown my classes that it isn't a measureable amount even over thousands of calls. I suggest useing the first method and NEVER using default values as they too can cause confusion.

I would have th see your SQL statement to tell you more.

go with johnwm suggestion or replace the "" with vbNullString

But if you really think the value is changing out of the blue then I would put a watch expression on
adoRecordset.Fields("isComment&quot.Value
to break when it changes and follow the code.

 

[strongm]

I wouldn't use ! - it's obsolete...

 

[PraveenMenon]

How many times i'll have to post this?

txtisComment.Text = adoRecordset.fields("isComment&quot & ""
All the Best
Praveen Menon
pcmin@rediffmail.com

 

[SemperFiDownUnda]

xtisComment.Text = adoRecordset.fields("isComment&quot.Value & ""

would be better IMHO

 

[Transcend]

or ..

if not isnull(adoRecordset.fields("isComment&quot) then
xtisComment.Text = adoRecordset.fields("isComment&quot.Value
end if

Transcend

 

[CCLINT]

> wouldn't use ! - it's obsolete...

Thank you strongm for mentioning this.

The use of the "bang !" seems to be an on-going discussion that some continue to use inspite of warnings.
But, this is a fault of MS, especially MS Office programers (ACCESS!), as this (the use of it) has been taught for years by MS and even the sample/help files and Wizards use it.

SemperFiDownUnda is also correct in saying not to use any default properties. They could also change or become obsolete, or not even work someday - even your own defined default values for class objects.

In ONLY the case of a recordset object, I often myself use default values out of habit, but a change in my code is very easy for this.

I always use a proccedure to get the field value and format it accordingly to it's type. In doing so, allows you to overcome the problem with NULL values, regardless of the type aof variable the field value is being assigned to (adoRecordset.fields("isComment&quot.Value & "" only works when assigning the value to a string/text), and reduceds the amount of typing and "forgetting" not to use default values.


Referencing the fields absolute position is the fastest
(rs.Fields(0).Value). (Not recommended unless you know it will always be in that same position, such as a primary key)

Then referencing the field via Alias name is second fastest:
(rs.Fields("TheField&quot.Value)

If referencing a fields more than one or two methods or propertys, or referencing a single one more than a few times, then using:

With rs.Fields("TheField&quot
.Value = "Some Value"
End With

is better, or,

Dim fld As ADODB.Field
Set fld=rs.Fields("TheField&quot

fld.Value = "Some Value"
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Inwoner]

What bothers me here is that no one could give an explanation for bubarooni's problem. If we could understand why this would happen....it will be much better...


Can someone out there help with an explenation

 

[THOMASNG]

A common reason for "disappearing assigned values" is
that locally declared variables go "out of scope" whenever
a different method or module "interrupts" to handle a new
event.
Add a standard module to your project, then move the
variable with the "disappearing assigned value" to it.
Rember to remove the original declaration - or it will override the new one.

 

[CCLINT]

THOMASNG: You do not apply here.

>What bothers me here is that no one could give an explanation for bubarooni's problem

Inwoner: First, the cause of the problem needs to be found, and solved, and then an explaination can be given, possiibly.
bubarooni hasn't responded as to whether any suggestions or ideas have helped or not.

bubarooni:
Is the problem solved?

If not, then how much data is in the memo field? Alot?
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[bubarooni]

The memo fields contain very little info. 100-200 characters at max and some are empty.

SemperFi and Transcend's answers have done the trick. I used Transcend's:

if not isnull(adoRecordset.fields("isComment")) then
   txtisComment.Text = adoRecordset.fields("isComment").Value
end if

On this one project I'll have to replace a ton of code with that and I feel duty bound to go back and change every other project I have out there. I have used that !bang sign a lot.

I broke execution on the original offending code, checked the value of !isComment and it had a string in it. I moved the mouse away from it and back over it and it was NULL, before I even hit F8! I assume it evaluated the expression on the break and changed it's value then. I don't know. I wish I understood this behavior.

I thank you all for your help but still wonder what it is I see occuring and why I have never ran into this before. If I knew why I might be able to justify not changing all that old code.

I found these references in my msdn library on NULL:

Expressions involving Null always result in Null. Thus, Null is said to "propagate" through expressions; if any part of the expression evaluates to Null, the entire expression evaluates to Null.

Null is the result of an explicit assignment of Null to a variable or any operation between expressions that contain Null.

Could this be what is happening?

 

[CasperTFG]

Well,

The only thing I can think of as to why it looses it's value is this. isComment may be related to a call or other DLL.

For instance if isComment was actually a column called Name. In the walk through or mouse over of the code.
[tt]aosRecordset!Name[/tt]
it would show the value of the column, but when it executed the code, it would think Name is a call or reserved word, so it would fail.

I did a quick search on the internet. So far It has come up that isComment is a reserved word in:
* Scripting
* Java
* PHP
* Something to do with VB parser as well, more here:

http://www.visibleprogress.com/support_faq.htm

So as a general rule don't use ! Craig, mailto:sander@cogeco.ca

Si hoc legere scis, nimis eruditionis habes

 

[JPD3]

Did you ever get this answered? I find myself with the same problem.