VB5 & SQL6.5, Nulls with Text Columns 
Author Message
 VB5 & SQL6.5, Nulls with Text Columns

Hello

I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I
am having trouble with some of the texts fields. When referencing a text
column that contains data, the string is valid the first time and NULL
the second. The following is sample code that fails.

If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
    lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
End if

Since the statement will be true, an error will occur on the second line
because the column is now NULL. Not all text fields are affected this
way, but the ones that are, seem to be consistent. I have been
monitoring the newsgroups for awhile regarding this issue, but I haven't
seen any solution. One person said to "store the value in a string the
first time", wouldn't that cause an error if the column was actually
NULL?

According to others it seems to be happening with ORACLE and DAO, the
only common element is VB5. I am surprised that this is not an issue
with most people, somebody must have encountered this and found a
solution or at least a Band-Aid solution.

Hope you can help.

Thank you in advance



Thu, 08 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

HI!

See ISNULL in VB! Read well the last paragraph.
Example:
If ISNULL(recReal.rdoColumns("ColumnName")) THEN
             'Nothing here
ELSE
    lblTemp.Caption = recReal.rdoColumns("ColumnName")
End if

Luis Andr Soares
www.ulusiada.pt


    Hello
    I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I am having trouble with some of the texts fields. When referencing a text column that contains data, the string is valid the first time and NULL the second. The following is sample code that fails.

    If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
        lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
    End if

    Since the statement will be true, an error will occur on the second line because the column is now NULL. Not all text fields are affected this way, but the ones that are, seem to be consistent. I have been monitoring the newsgroups for awhile regarding this issue, but I haven't seen any solution. One person said to "store the value in a string the first time", wouldn't that cause an error if the column was actually NULL?

    According to others it seems to be happening with ORACLE and DAO, the only common element is VB5. I am surprised that this is not an issue with most people, somebody must have encountered this and found a solution or at least a Band-Aid solution.

    Hope you can help.

    Thank you in advance



Fri, 09 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

Another method is:

lblTemp.Caption - recReal.rdoColumns("ColumnName") & vbNullString

This method will append an empty string to the end of the field's value even if it's null.  This will avoid any null value errors.

--
John Tabor

http://members.bellatlantic.net/~jftabor
___________


    If ISNULL(recReal.rdoColumns("ColumnName")) THEN
                 'Nothing here
    ELSE
        lblTemp.Caption = recReal.rdoColumns("ColumnName")
    End if



Fri, 09 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

Quote:

> I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I
> am having trouble with some of the texts fields. When referencing a
> text column that contains data, the string is valid the first time and
> NULL the second. The following is sample code that fails.

> If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
>     lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
> End if

As someone else mentioned, if you're checking for Null,
you should be using the IsNull function:
  If Not IsNull(recReal.rdoColumns("ColumnName")) Then

Quote:
> Since the statement will be true, an error will occur on the second
> line because the column is now NULL. Not all text fields are affected
> this way, but the ones that are, seem to be consistent.

From what I understand, it's only the SQL Server Text fields
that are affected in this way.  Varchar fields seem to be fine.
If you'll never have data longer than the max for a Varchar,
you may want to use that as the datatype instead (assuming, of
course, that you can change the field type).

Quote:
> I have been
> monitoring the newsgroups for awhile regarding this issue, but I
> haven't seen any solution. One person said to "store the value in a
> string the first time", wouldn't that cause an error if the column was
> actually NULL?

Use a Variant instead of a String, then.  Variants can be Null.

If your program is the only one writing data to this database,
you have another option... When storing the data, if one of the
affected fields is Null, store an empty string instead.  That
can always be assigned to a String variable, so you won't have
to worry about the extra overhead of a Variant.

cwp
--

I am not a child of nature.
I am a child of asphalt & toxic fumes.
                     - Joel Fleishman



Fri, 09 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns
We are experiencing the same problem as Robert. I thought great VB6 is out
and M$ might have fixed this bug. But no VB6 still has this bug where u look
at a text field through RDO once and its there and again and its gone. All
these mickey mouse band aid solutions are not good enough (ie retrieveing
the value once into a variant type).  I dont understand why they just don't
fix it. Or are we suppose to use their new model for data access -->
ADO......

Czecho

Quote:


>> I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I
>> am having trouble with some of the texts fields. When referencing a
>> text column that contains data, the string is valid the first time and
>> NULL the second. The following is sample code that fails.

>> If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
>>     lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
>> End if

>As someone else mentioned, if you're checking for Null,
>you should be using the IsNull function:
>  If Not IsNull(recReal.rdoColumns("ColumnName")) Then

>> Since the statement will be true, an error will occur on the second
>> line because the column is now NULL. Not all text fields are affected
>> this way, but the ones that are, seem to be consistent.

>From what I understand, it's only the SQL Server Text fields
>that are affected in this way.  Varchar fields seem to be fine.
>If you'll never have data longer than the max for a Varchar,
>you may want to use that as the datatype instead (assuming, of
>course, that you can change the field type).

>> I have been
>> monitoring the newsgroups for awhile regarding this issue, but I
>> haven't seen any solution. One person said to "store the value in a
>> string the first time", wouldn't that cause an error if the column was
>> actually NULL?

>Use a Variant instead of a String, then.  Variants can be Null.

>If your program is the only one writing data to this database,
>you have another option... When storing the data, if one of the
>affected fields is Null, store an empty string instead.  That
>can always be assigned to a String variable, so you won't have
>to worry about the extra overhead of a Variant.

>cwp
>--

>I am not a child of nature.
>I am a child of asphalt & toxic fumes.
>                     - Joel Fleishman



Sat, 10 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns
You can not use "aVar<>null" to judge whether aVar is null or not, you must
use "IsNull" function. Please read vb online help.
"aVar<>null" will return null, not "True" or "False"

Quote:

>We are experiencing the same problem as Robert. I thought great VB6 is out
>and M$ might have fixed this bug. But no VB6 still has this bug where u
look
>at a text field through RDO once and its there and again and its gone. All
>these mickey mouse band aid solutions are not good enough (ie retrieveing
>the value once into a variant type).  I dont understand why they just don't
>fix it. Or are we suppose to use their new model for data access -->
>ADO......

>Czecho


>>> I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I
>>> am having trouble with some of the texts fields. When referencing a
>>> text column that contains data, the string is valid the first time and
>>> NULL the second. The following is sample code that fails.

>>> If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
>>>     lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
>>> End if

>>As someone else mentioned, if you're checking for Null,
>>you should be using the IsNull function:
>>  If Not IsNull(recReal.rdoColumns("ColumnName")) Then

>>> Since the statement will be true, an error will occur on the second
>>> line because the column is now NULL. Not all text fields are affected
>>> this way, but the ones that are, seem to be consistent.

>>From what I understand, it's only the SQL Server Text fields
>>that are affected in this way.  Varchar fields seem to be fine.
>>If you'll never have data longer than the max for a Varchar,
>>you may want to use that as the datatype instead (assuming, of
>>course, that you can change the field type).

>>> I have been
>>> monitoring the newsgroups for awhile regarding this issue, but I
>>> haven't seen any solution. One person said to "store the value in a
>>> string the first time", wouldn't that cause an error if the column was
>>> actually NULL?

>>Use a Variant instead of a String, then.  Variants can be Null.

>>If your program is the only one writing data to this database,
>>you have another option... When storing the data, if one of the
>>affected fields is Null, store an empty string instead.  That
>>can always be assigned to a String variable, so you won't have
>>to worry about the extra overhead of a Variant.

>>cwp
>>--

>>I am not a child of nature.
>>I am a child of asphalt & toxic fumes.
>>                     - Joel Fleishman



Sat, 10 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

There is simple and elegant workaround:

lblTemp.Caption = recReal.rdoColumns("ColumnName") & vbNullString

HTH,

Alexander Shirshov


    Hello
    I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I am having trouble with some of the texts fields. When referencing a text column that contains data, the string is valid the first time and NULL the second. The following is sample code that fails.

    If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
        lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
    End if

    Since the statement will be true, an error will occur on the second line because the column is now NULL. Not all text fields are affected this way, but the ones that are, seem to be consistent. I have been monitoring the newsgroups for awhile regarding this issue, but I haven't seen any solution. One person said to "store the value in a string the first time", wouldn't that cause an error if the column was actually NULL?

    According to others it seems to be happening with ORACLE and DAO, the only common element is VB5. I am surprised that this is not an issue with most people, somebody must have encountered this and found a solution or at least a Band-Aid solution.

    Hope you can help.

    Thank you in advance



Sat, 10 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns
Quote:

>There is simple and elegant workaround:
>lblTemp.Caption = recReal.rdoColumns("ColumnName") & vbNullString

<cut>

The 'vbNullString' constant is not an empty string - it is a null pointer.
While the above line currently works, it is not supported syntax according
to the help for the constants.  For an empty string you should use an empty
string:

lblTemp.Caption = recReal.rdoColumns("ColumnName") & ""



Sat, 10 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

Quote:

> You can not use "aVar<>null" to judge whether aVar is null or not,
> you must use "IsNull" function. Please read vb online help.
> "aVar<>null" will return null, not "True" or "False"

Perhaps you missed this part of the post, which you quoted
in its entirety...

Quote:

> >>As someone else mentioned, if you're checking for Null,
> >>you should be using the IsNull function:
> >>  If Not IsNull(recReal.rdoColumns("ColumnName")) Then

Please read the entire post before responding.

Note followups.
cwp
--

I am not a child of nature.
I am a child of asphalt & toxic fumes.
                     - Joel Fleishman



Sat, 10 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

Quote:

> We are experiencing the same problem as Robert. I thought great VB6
> is out and M$ might have fixed this bug. But no VB6 still has this
> bug where u look at a text field through RDO once and its there and
> again and its gone.

My guess is that MS isn't viewing this as a bug.

Quote:
> All these mickey mouse band aid solutions are not good enough (ie
> retrieveing the value once into a variant type).

Much work with VB is in "band aid solutions."  It goes
with the territory.  Just because your code is 100%
correct doesn't mean it'll work right.

Quote:
> I dont understand why they just don't fix it.

Possibly because they feel they don't need to.  We can
get the info; therefore it's not a problem big enough
to waste time solving.

Quote:
> Or are we suppose to use their new model for data access --> ADO.....

That's my understanding.
I've heard that RDO and DAO will not be supported
in the future, and that we're supposed to use ADO.
Secondhand information, of course.

cwp
--

I am not a child of nature.
I am a child of asphalt & toxic fumes.
                     - Joel Fleishman



Sat, 10 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

Quote:

>  Hello

> I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I
> am having trouble with some of the texts fields. When referencing a
> text column that contains data, the string is valid the first time and
> NULL the second. The following is sample code that fails.

> If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
>     lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
> End if

Strange but true if your change it to

If recReal.rdoColumns("ColumnName") <> "" Then             'OK here
    lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
End if

It will work, but a better method is to replace the entire above code
with

    lblTemp.caption = recReal.rdoColumns("ColumnName")&""

--
=================================
There is nothing noble in being superior to your fellow men,
True nobility is being superior to your former self.

http://www.users.globalnet.co.uk/~chrisg06



Mon, 12 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

Quote:

> I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I
> am having trouble with some of the texts fields. When referencing a
> text column that contains data, the string is valid the first time and
> NULL the second. The following is sample code that fails.

> If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
>     lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
> End if
> <snip>

I don't know if this is your real problem, but your test for Null is
incorrect.  To test for Null, you should use the IsNull() function.

Also... if you are just checking for Null to protect the assignment
operation, you can use the following code:

lblTemp.Caption = "" & recReal.rdoColumns("ColumnName")

Brent Langdon, Software Engineer
Sequel Technology, http://www.sequeltech.com
(remove "nospam" to reply directly)



Mon, 12 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns
I had this problem a while back and I believe that it is due to text
data being returned as "chunks".
What I believe you are experiencing is the first chunk of the text field
at the first access and subsequent chunks (containing no data) at
subsequent accesses.
The ChunkRequired property of the rdoColumn object is the giveaway here.
If it is true, I believe you will experience this behaviour.

Here is a piece of code I put together for one of my resultsets. It is
still in production so may not be "watertight" but if you use the online
help, you should get an idea of what to bear in mind with text / BLOB
fields.

   Dim i As Integer
   Dim temp As Variant
   Dim thisColumnData As Variant

   'place the data in the array
    For i = 0 To mrsResultset.rdoColumns.Count - 1
        If mrsResultset(i).Type = rdTypeLONGVARCHAR Then
            If mrsResultset(i).ColumnSize = -1 Then
                temp = mrsResultset(i).GetChunk(mlBindThreshold)
                Do
                    thisColumnData = thisColumnData & temp
                    temp = mrsResultset(i).GetChunk(mlBindThreshold)
                Loop While Len(temp) > 0
            Else
                If mrsResultset(i).ColumnSize > 0 Then
                    thisColumnData =
mrsResultset(i).GetChunk(mrsResultset(i).ColumnSize)
                End If
            End If
        Else
            thisColumnData = mrsResultset(i).Value
        End If

         mavColumns(i) = vFieldVal(thisColumnData) 'Store in module
level array of Variants
     Next

Hope it helps.
John Hussey

http://www.gresham-computing.com

Quote:

> Hello

> I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO 2.0 and I
> am having trouble with some of the texts fields. When referencing a
> text column that contains data, the string is valid the first time and
> NULL the second. The following is sample code that fails.

> If recReal.rdoColumns("ColumnName") <> Null Then             'OK here
>     lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
> End if

> Since the statement will be true, an error will occur on the second
> line because the column is now NULL. Not all text fields are affected
> this way, but the ones that are, seem to be consistent. I have been
> monitoring the newsgroups for awhile regarding this issue, but I
> haven't seen any solution. One person said to "store the value in a
> string the first time", wouldn't that cause an error if the column was
> actually NULL?

> According to others it seems to be happening with ORACLE and DAO, the
> only common element is VB5. I am surprised that this is not an issue
> with most people, somebody must have encountered this and found a
> solution or at least a Band-Aid solution.

> Hope you can help.

> Thank you in advance

--


Tue, 13 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns
FWIW, the problem is that

   If recReal.rdoColumns("ColumnName") <> Null Then

is improper syntax.

You can't use the usual operators with Null: that statement should be

   If Not IsNull(recReal.rdoColumns("ColumnName")) Then

Chris's solution should work, though.

Quote:


>       Hello

>      I am using VB5 Enterprise to access MS-SQL Sever 6.5 via RDO
>      2.0 and I am having trouble with some of the texts fields.
>      When referencing a text column that contains data, the
>      string is valid the first time and NULL the second. The
>      following is sample code that fails.

>      If recReal.rdoColumns("ColumnName") <> Null Then
>      'OK here
>          lblTemp.Caption = recReal.rdoColumns("ColumnName")
>      'Fails here
>      End if

> Strange but true if your change it to

> If recReal.rdoColumns("ColumnName") <> "" Then             'OK here
>     lblTemp.Caption = recReal.rdoColumns("ColumnName")    'Fails here
> End if

> It will work, but a better method is to replace the entire above code
> with

>     lblTemp.caption = recReal.rdoColumns("ColumnName")&""

> --
> =================================
> There is nothing noble in being superior to your fellow men,
> True nobility is being superior to your former self.

> http://www.users.globalnet.co.uk/~chrisg06

--

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



Tue, 13 Mar 2001 03:00:00 GMT  
 VB5 & SQL6.5, Nulls with Text Columns

Quote:

> FWIW, the problem is that

>    If recReal.rdoColumns("ColumnName") <> Null Then

> is improper syntax.

Actually, no, that isn't the problem.
The problem is, as the original poster wrote,
that the second reference to a SQL Server Text
field will always result in NULL, even if there
is supposed to be text there.
This problem doesn't go away even if proper
syntax (IsNull) is used.

Quote:
> Chris's solution should work, though.

One of them will, anyway.

Quote:

> > Strange but true if your change it to

> > If recReal.rdoColumns("ColumnName") <> "" Then             'OK here
> >     lblTemp.Caption = recReal.rdoColumns("ColumnName")   'Fails here
> > End if

> > It will work,

No, it won't.  The second reference to the Text field
will still be Null.

Quote:
> but a better method is to replace the entire above code with

> >     lblTemp.caption = recReal.rdoColumns("ColumnName")&""

That does work.

cwp
--

I am not a child of nature.
I am a child of asphalt & toxic fumes.
                     - Joel Fleishman



Tue, 13 Mar 2001 03:00:00 GMT  
 
 [ 19 post ]  Go to page: [1] [2]

 Relevant Pages 

1. VB5 & SQL6.5, Nulls with Text Columns

2. VB5 & SQL6.5 multi joins

3. Read only problem:VB5 & SQL6.5

4. URGENT: VB5 & SQL6.5 Employee Database Help

5. Illegal use of Null Values !^&*$@^&*^$&*

6. dbgrid - dynamic columns in VB5 & access 97

7. VB5 failed to update all records in SQL6.5 SP

8. VB5, SQL6.5: row identity after INSERT?

9. ODBC 3.5 may slow down the VB4 or VB5 to access MS SQL6.5

10. VB5/Informix TEXT columns

11. VB5/Informix TEXT columns

12. Sporadic Errors AND Slow Data Retrieval -- VB5, SQL6.5

 

 
Powered by phpBB® Forum Software