To Null, or not to Null...? 
Author Message
 To Null, or not to Null...?

How can i change a field from one that wont accept nulls, to one that will,
in Access.  Programmatically (using VB).  Is this actually possible, 'cos
I've tried everything that I can think of, and it still wont work.


Sun, 07 Dec 2003 16:47:31 GMT  
 To Null, or not to Null...?
Here's an example that works within Access. Of course you'll have to set a
reference to DAO and open the database instead of using currentdb. Bobby
Dim db As Database, tdef As TableDef, fld As Field
Set db = CurrentDb()
Set tdef = db.TableDefs("Table2")
Set fld = tdef.Fields("col1")
fld.Required = False
db.TableDefs.Refresh
Quote:

> How can i change a field from one that wont accept nulls, to one that will,
> in Access.  Programmatically (using VB).  Is this actually possible, 'cos
> I've tried everything that I can think of, and it still wont work.



Mon, 08 Dec 2003 12:26:10 GMT  
 To Null, or not to Null...?
It is better to avoid nulls altogether.
Note that a null is NOT the same  as an empty string
and this can be used to good effect

mydb.myfield("RecordName")= "" &  newrecord

if the variable newrecord contains a value then the empty string ("") is
ignored and the value
passed to the recordset field, otherwise the empty string is passed to the
field.

Hope this helps
Iain


Quote:
> How can i change a field from one that wont accept nulls, to one that
will,
> in Access.  Programmatically (using VB).  Is this actually possible, 'cos
> I've tried everything that I can think of, and it still wont work.



Wed, 24 Dec 2003 14:57:58 GMT  
 To Null, or not to Null...?
Curious as to your reasons why to avoid Nulls. I certainly don't agree with
you.

As you point out, Null is not the same as an empty string: it means the
value isn't known (where as an empty string implies that the value is known,
and there isn't one) As a trivial example, assume you're capturing peoples
names, and you're separating them into First Name, Middle Initial and Last
Name. If you don't know someone's middle initial, you'd want Null for that
field, whereas if you know that they don't have a middle initial, you'd use
an empty string to represent that piece of information.

Yes, handling Nulls involves extra code, but if the information is
important, avoiding more code doesn't strike me as reasonable.

HTH

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele/


Quote:
> It is better to avoid nulls altogether.
> Note that a null is NOT the same  as an empty string
> and this can be used to good effect

> mydb.myfield("RecordName")= "" &  newrecord

> if the variable newrecord contains a value then the empty string ("") is
> ignored and the value
> passed to the recordset field, otherwise the empty string is passed to the
> field.

> Hope this helps
> Iain



> > How can i change a field from one that wont accept nulls, to one that
> will,
> > in Access.  Programmatically (using VB).  Is this actually possible,
'cos
> > I've tried everything that I can think of, and it still wont work.



Wed, 24 Dec 2003 18:45:31 GMT  
 To Null, or not to Null...?
Douglas,

Null is very, very, very DB specific thing. Most programming languages don't
have such a concept. I don't know when nulls appeared in relational (or even
pre-relational) database theory, but it was way long time ago. I believe
that at that time data were very expensive in terms of storage space and
retrieval time, so Null came handy. It allowed to reduce both disk space
usage and network traffic.

Now harddrives are cheaper than popcorn - and I don't use nulls in my
development. I convert values received from database engine to VB intrinsic
types early and work with normal data types. When I yield values to DB
engine I convert them back to that (outdated) format.

Douglas, I see from your sig that you're Access MVP. That means to me that
you spend 99.99% of your development time dealing with databases and you're
very used to Null. OK, but what if you have to perform the same logic as you
developed for Customer entity in a database to be applied to Customer object
which need to be persisted to plain text files?

In your example, why not NoMiddleName property?

Alexander Shirshov, MCSD
Starting NoNull Ribbon Campaign



Quote:
> Curious as to your reasons why to avoid Nulls. I certainly don't agree
with
> you.

> As you point out, Null is not the same as an empty string: it means the
> value isn't known (where as an empty string implies that the value is
known,
> and there isn't one) As a trivial example, assume you're capturing peoples
> names, and you're separating them into First Name, Middle Initial and Last
> Name. If you don't know someone's middle initial, you'd want Null for that
> field, whereas if you know that they don't have a middle initial, you'd
use
> an empty string to represent that piece of information.

> Yes, handling Nulls involves extra code, but if the information is
> important, avoiding more code doesn't strike me as reasonable.

> HTH

> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele/



> > It is better to avoid nulls altogether.
> > Note that a null is NOT the same  as an empty string
> > and this can be used to good effect

> > mydb.myfield("RecordName")= "" &  newrecord

> > if the variable newrecord contains a value then the empty string ("") is
> > ignored and the value
> > passed to the recordset field, otherwise the empty string is passed to
the
> > field.

> > Hope this helps
> > Iain



> > > How can i change a field from one that wont accept nulls, to one that
> > will,
> > > in Access.  Programmatically (using VB).  Is this actually possible,
> 'cos
> > > I've tried everything that I can think of, and it still wont work.



Wed, 24 Dec 2003 21:35:19 GMT  
 To Null, or not to Null...?
Comments in-line below.

I assume we'll have to agree to disagree...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele/


Quote:
> Douglas,

> Null is very, very, very DB specific thing.

Not sure I agree with this. If you're filling out a questionnaire, and don't
answer question 5, how do you refer to it? Assume there aren't "Not
Applicable" or "No Comment" boxes. In this case, Null would be the
equivalent of the English "No answer provided", which is certainly how I
would describe it in talking about the response with another person.

Quote:
> Most programming languages don't have such a concept.

Lots of languages have Null in them. For example, in VB, Variants can have
null values.

Quote:
> I don't know when nulls appeared in relational (or even
> pre-relational) database theory, but it was way long time ago.

Actually, I'm not sure the first version of IBM's DB2 I worked with in the
early 1980s had Null in it.

Quote:
> I believe
> that at that time data were very expensive in terms of storage space and
> retrieval time, so Null came handy. It allowed to reduce both disk space
> usage and network traffic.

Afraid I don't buy the network traffic argument. Why would returning Null be
any cheaper than returning an empty string? And as to disk space usage, it
isn't necessarily true. In Access, for example, text only takes up as much
room as required. A 1 character string takes up 1 byte, whereas a 25
character string takes up 25 bytes (assuming non-Unicode).

Quote:
> Now harddrives are cheaper than popcorn - and I don't use nulls in my
> development. I convert values received from database engine to VB
intrinsic
> types early and work with normal data types. When I yield values to DB
> engine I convert them back to that (outdated) format.

That's your perogative. I happen to be someone who sees great value in Null.

Quote:
> Douglas, I see from your sig that you're Access MVP. That means to me that
> you spend 99.99% of your development time dealing with databases and
you're
> very used to Null.

Actually, I happen to spend the majority of my newsgroup time answering
Access questions, but I spend very little of my time at work dealing with
databases anymore.

Quote:
> OK, but what if you have to perform the same logic as you
> developed for Customer entity in a database to be applied to Customer
object
> which need to be persisted to plain text files?

I fail to see any difference between the two. If I wanted to know that a
particular value was unknown, I certain wouldn't want it represented as ""
in a text file. Undoubtedly, I'd be forced to put something like "<Unknown>"
(or "<Null>") as its value. (Of course, in XML, I'd just leave out that
attribute.)

Quote:
> In your example, why not NoMiddleName property?

Because it's redundant. Null serves the need perfectly well: why add an
additional field?

Quote:
> Alexander Shirshov, MCSD
> Starting NoNull Ribbon Campaign



> > Curious as to your reasons why to avoid Nulls. I certainly don't agree
> with
> > you.

> > As you point out, Null is not the same as an empty string: it means the
> > value isn't known (where as an empty string implies that the value is
> known,
> > and there isn't one) As a trivial example, assume you're capturing
peoples
> > names, and you're separating them into First Name, Middle Initial and
Last
> > Name. If you don't know someone's middle initial, you'd want Null for
that
> > field, whereas if you know that they don't have a middle initial, you'd
> use
> > an empty string to represent that piece of information.

> > Yes, handling Nulls involves extra code, but if the information is
> > important, avoiding more code doesn't strike me as reasonable.

> > HTH

> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele/



Wed, 24 Dec 2003 22:13:37 GMT  
 To Null, or not to Null...?
Douglas,

OK, let's design a simple Customer class:

Public OID As Long
Public FirstName As String
Public MiddleName as String
Public LastName as String

Where's a place for Null here? Or are you proposing to give up data types
and use Variant everywhere?



Quote:
> Comments in-line below.

> I assume we'll have to agree to disagree...

> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele/



> > Douglas,

> > Null is very, very, very DB specific thing.

> Not sure I agree with this. If you're filling out a questionnaire, and
don't
> answer question 5, how do you refer to it? Assume there aren't "Not
> Applicable" or "No Comment" boxes. In this case, Null would be the
> equivalent of the English "No answer provided", which is certainly how I
> would describe it in talking about the response with another person.

> > Most programming languages don't have such a concept.

> Lots of languages have Null in them. For example, in VB, Variants can have
> null values.

> > I don't know when nulls appeared in relational (or even
> > pre-relational) database theory, but it was way long time ago.

> Actually, I'm not sure the first version of IBM's DB2 I worked with in the
> early 1980s had Null in it.

> > I believe
> > that at that time data were very expensive in terms of storage space and
> > retrieval time, so Null came handy. It allowed to reduce both disk space
> > usage and network traffic.

> Afraid I don't buy the network traffic argument. Why would returning Null
be
> any cheaper than returning an empty string? And as to disk space usage, it
> isn't necessarily true. In Access, for example, text only takes up as much
> room as required. A 1 character string takes up 1 byte, whereas a 25
> character string takes up 25 bytes (assuming non-Unicode).

> > Now harddrives are cheaper than popcorn - and I don't use nulls in my
> > development. I convert values received from database engine to VB
> intrinsic
> > types early and work with normal data types. When I yield values to DB
> > engine I convert them back to that (outdated) format.

> That's your perogative. I happen to be someone who sees great value in
Null.

> > Douglas, I see from your sig that you're Access MVP. That means to me
that
> > you spend 99.99% of your development time dealing with databases and
> you're
> > very used to Null.

> Actually, I happen to spend the majority of my newsgroup time answering
> Access questions, but I spend very little of my time at work dealing with
> databases anymore.

> > OK, but what if you have to perform the same logic as you
> > developed for Customer entity in a database to be applied to Customer
> object
> > which need to be persisted to plain text files?

> I fail to see any difference between the two. If I wanted to know that a
> particular value was unknown, I certain wouldn't want it represented as ""
> in a text file. Undoubtedly, I'd be forced to put something like
"<Unknown>"
> (or "<Null>") as its value. (Of course, in XML, I'd just leave out that
> attribute.)

> > In your example, why not NoMiddleName property?

> Because it's redundant. Null serves the need perfectly well: why add an
> additional field?

> > Alexander Shirshov, MCSD
> > Starting NoNull Ribbon Campaign



> > > Curious as to your reasons why to avoid Nulls. I certainly don't agree
> > with
> > > you.

> > > As you point out, Null is not the same as an empty string: it means
the
> > > value isn't known (where as an empty string implies that the value is
> > known,
> > > and there isn't one) As a trivial example, assume you're capturing
> peoples
> > > names, and you're separating them into First Name, Middle Initial and
> Last
> > > Name. If you don't know someone's middle initial, you'd want Null for
> that
> > > field, whereas if you know that they don't have a middle initial,
you'd
> > use
> > > an empty string to represent that piece of information.

> > > Yes, handling Nulls involves extra code, but if the information is
> > > important, avoiding more code doesn't strike me as reasonable.

> > > HTH

> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele/



Sun, 28 Dec 2003 14:48:41 GMT  
 To Null, or not to Null...?

Quote:
> OK, let's design a simple Customer class:

> Public OID As Long
> Public FirstName As String
> Public MiddleName as String
> Public LastName as String

> Where's a place for Null here? Or are you proposing to give up data types
> and use Variant everywhere?

A simple class gets you simple features.

Let's design it a little differently:

Class Customer:

Public Properties As New Collection

Public Sub Fetch(ByVal lCustID As Long)

    Dim rs As Recordset
    Set rs = g_db.OpenRecordset("select * from customer where ID = " &
CStr(lCustID))

    If rs.EOF Then Err.Raise kNotFound, "Customer not found."

    If Not IsNull(rs("FirstName")) Then
        Properties.Add rs("FirstName"), "FirstName"
    End If
    If Not IsNull(rs("MiddleName")) Then
        Properties.Add rs("MiddleName"), "MiddleName"
    End If
    If Not IsNull(rs("LastName")) Then
        Properties.Add rs("LastName"), "LastName"
    End If

    rs.Close: Set rs = Nothing

End Sub

Public Sub Display()

    On Error GoTo catch

    frmCustomer.FirstName = Me.Properties("FirstName")
    frmCustomer.MiddleName = Me.Properties("MiddleName")
    frmCustomer.LastName = Me.Properties("LastName")

Exit Sub

catch:

    If Err.Number = kItemNotFoundInCollection Then
        Resume Next
    Else
        MsgBox Err.Description
    End If

End Sub

This is similar to Douglas' mention of an XML scenario, where if a property
isn't known, it simply isn't included. This could be done a little more
elegantly by replacing the standard VB Collection with a home-grown one
which has an Exists method to check for property existence:

    If Me.Properties.Exists("FirstName") Then
        frmCustomer.FirstName = Me.Properties("FirstName")
    Else
        frmCustomer.FirstName = "<unknown>"
    End If

"Null" may be a DB-specific thing, but "Unknown" most definitely isn't. Real
world code has to handle real world scenarios, meaning handling Unknown,
which your "simple" class doesn't do. Using Null is one way to do that,
using variants is another, using a NoMiddleName property is another, and
obviously there are others. Which one is best? Standard consultant's answer:
It depends.



Mon, 29 Dec 2003 00:35:10 GMT  
 To Null, or not to Null...?

Quote:
> OK, let's design a simple Customer class:

> Public OID As Long
> Public FirstName As String
> Public MiddleName as String
> Public LastName as String

> Where's a place for Null here? Or are you proposing to give up data types
> and use Variant everywhere?

A simple class gets you simple features.

Let's design it a little differently:

Class Customer:

Public Properties As New Collection

Public Sub Fetch(ByVal lCustID As Long)

    Dim rs As Recordset
    Set rs = g_db.OpenRecordset("select * from customer where ID = " &
CStr(lCustID))

    If rs.EOF Then Err.Raise kNotFound, "Customer not found."

    If Not IsNull(rs("FirstName")) Then
        Properties.Add rs("FirstName"), "FirstName"
    End If
    If Not IsNull(rs("MiddleName")) Then
        Properties.Add rs("MiddleName"), "MiddleName"
    End If
    If Not IsNull(rs("LastName")) Then
        Properties.Add rs("LastName"), "LastName"
    End If

    rs.Close: Set rs = Nothing

End Sub

Public Sub Display()

    On Error GoTo catch

    frmCustomer.FirstName = Me.Properties("FirstName")
    frmCustomer.MiddleName = Me.Properties("MiddleName")
    frmCustomer.LastName = Me.Properties("LastName")

Exit Sub

catch:

    If Err.Number = kItemNotFoundInCollection Then
        Resume Next
    Else
        MsgBox Err.Description
    End If

End Sub

This is similar to Douglas' mention of an XML scenario, where if a property
isn't known, it simply isn't included. This could be done a little more
elegantly by replacing the standard VB Collection with a home-grown one
which has an Exists method to check for property existence:

    If Me.Properties.Exists("FirstName") Then
        frmCustomer.FirstName = Me.Properties("FirstName")
    Else
        frmCustomer.FirstName = "<unknown>"
    End If

"Null" may be a DB-specific thing, but "Unknown" most definitely isn't. Real
world code has to handle real world scenarios, meaning handling Unknown,
which your "simple" class doesn't do. Using Null is one way to do that,
using variants is another, using a NoMiddleName property is another, and
obviously there are others. Which one is best? Standard consultant's answer:
It depends.



Mon, 29 Dec 2003 00:35:28 GMT  
 To Null, or not to Null...?


Fri, 19 Jun 1992 00:00:00 GMT  
 To Null, or not to Null...?
Stupid newsreader...


Mon, 29 Dec 2003 00:39:47 GMT  
 To Null, or not to Null...?


Fri, 19 Jun 1992 00:00:00 GMT  
 To Null, or not to Null...?
Everything is messed up in heads and code of Access programmers: GUI,
business logic, data access...

Dewayne, do you seriously consider late-bound, type-weak code as more
advanced? I surely don't. What for real-world scenario, add a reference to,
say, Outlook object model, and count how many properties there are of Type
Variant. My bet: zero.

Regards,
Alexander

BTW, there's a serious problem in your code:

Properties.Add rs("FirstName"), "FirstName"

it adds not the value of the field, but the field object instead. Hope this
is just a typo.


Quote:
> > OK, let's design a simple Customer class:

> > Public OID As Long
> > Public FirstName As String
> > Public MiddleName as String
> > Public LastName as String

> > Where's a place for Null here? Or are you proposing to give up data
types
> > and use Variant everywhere?

> A simple class gets you simple features.

> Let's design it a little differently:

> Class Customer:

> Public Properties As New Collection

> Public Sub Fetch(ByVal lCustID As Long)

>     Dim rs As Recordset
>     Set rs = g_db.OpenRecordset("select * from customer where ID = " &
> CStr(lCustID))

>     If rs.EOF Then Err.Raise kNotFound, "Customer not found."

>     If Not IsNull(rs("FirstName")) Then
>         Properties.Add rs("FirstName"), "FirstName"
>     End If
>     If Not IsNull(rs("MiddleName")) Then
>         Properties.Add rs("MiddleName"), "MiddleName"
>     End If
>     If Not IsNull(rs("LastName")) Then
>         Properties.Add rs("LastName"), "LastName"
>     End If

>     rs.Close: Set rs = Nothing

> End Sub

> Public Sub Display()

>     On Error GoTo catch

>     frmCustomer.FirstName = Me.Properties("FirstName")
>     frmCustomer.MiddleName = Me.Properties("MiddleName")
>     frmCustomer.LastName = Me.Properties("LastName")

> Exit Sub

> catch:

>     If Err.Number = kItemNotFoundInCollection Then
>         Resume Next
>     Else
>         MsgBox Err.Description
>     End If

> End Sub

> This is similar to Douglas' mention of an XML scenario, where if a
property
> isn't known, it simply isn't included. This could be done a little more
> elegantly by replacing the standard VB Collection with a home-grown one
> which has an Exists method to check for property existence:

>     If Me.Properties.Exists("FirstName") Then
>         frmCustomer.FirstName = Me.Properties("FirstName")
>     Else
>         frmCustomer.FirstName = "<unknown>"
>     End If

> "Null" may be a DB-specific thing, but "Unknown" most definitely isn't.
Real
> world code has to handle real world scenarios, meaning handling Unknown,
> which your "simple" class doesn't do. Using Null is one way to do that,
> using variants is another, using a NoMiddleName property is another, and
> obviously there are others. Which one is best? Standard consultant's
answer:
> It depends.



Mon, 29 Dec 2003 01:18:13 GMT  
 To Null, or not to Null...?
Because you think I'm an Access programmer, I'm automatically an idiot? And
what if I told you I have a Mac at home, and drive a Toyota truck, and live
in Kansas?

You consider a class that can't handle the simple concept of Unknown as
advanced?

And about the Outlook object model; I counted over ten items defined As
Object or Variant in just the first three entries.

Oh, excuse me, I see you're Russian. That explains everything.


Quote:
> Everything is messed up in heads and code of Access programmers: GUI,
> business logic, data access...

> Dewayne, do you seriously consider late-bound, type-weak code as more
> advanced? I surely don't. What for real-world scenario, add a reference
to,
> say, Outlook object model, and count how many properties there are of Type
> Variant. My bet: zero.

> Regards,
> Alexander

> BTW, there's a serious problem in your code:

> Properties.Add rs("FirstName"), "FirstName"

> it adds not the value of the field, but the field object instead. Hope
this
> is just a typo.



> > > OK, let's design a simple Customer class:

> > > Public OID As Long
> > > Public FirstName As String
> > > Public MiddleName as String
> > > Public LastName as String

> > > Where's a place for Null here? Or are you proposing to give up data
> types
> > > and use Variant everywhere?

> > A simple class gets you simple features.

> > Let's design it a little differently:

> > Class Customer:

> > Public Properties As New Collection

> > Public Sub Fetch(ByVal lCustID As Long)

> >     Dim rs As Recordset
> >     Set rs = g_db.OpenRecordset("select * from customer where ID = " &
> > CStr(lCustID))

> >     If rs.EOF Then Err.Raise kNotFound, "Customer not found."

> >     If Not IsNull(rs("FirstName")) Then
> >         Properties.Add rs("FirstName"), "FirstName"
> >     End If
> >     If Not IsNull(rs("MiddleName")) Then
> >         Properties.Add rs("MiddleName"), "MiddleName"
> >     End If
> >     If Not IsNull(rs("LastName")) Then
> >         Properties.Add rs("LastName"), "LastName"
> >     End If

> >     rs.Close: Set rs = Nothing

> > End Sub

> > Public Sub Display()

> >     On Error GoTo catch

> >     frmCustomer.FirstName = Me.Properties("FirstName")
> >     frmCustomer.MiddleName = Me.Properties("MiddleName")
> >     frmCustomer.LastName = Me.Properties("LastName")

> > Exit Sub

> > catch:

> >     If Err.Number = kItemNotFoundInCollection Then
> >         Resume Next
> >     Else
> >         MsgBox Err.Description
> >     End If

> > End Sub

> > This is similar to Douglas' mention of an XML scenario, where if a
> property
> > isn't known, it simply isn't included. This could be done a little more
> > elegantly by replacing the standard VB Collection with a home-grown one
> > which has an Exists method to check for property existence:

> >     If Me.Properties.Exists("FirstName") Then
> >         frmCustomer.FirstName = Me.Properties("FirstName")
> >     Else
> >         frmCustomer.FirstName = "<unknown>"
> >     End If

> > "Null" may be a DB-specific thing, but "Unknown" most definitely isn't.
> Real
> > world code has to handle real world scenarios, meaning handling Unknown,
> > which your "simple" class doesn't do. Using Null is one way to do that,
> > using variants is another, using a NoMiddleName property is another, and
> > obviously there are others. Which one is best? Standard consultant's
> answer:
> > It depends.



Mon, 29 Dec 2003 02:21:53 GMT  
 To Null, or not to Null...?
I'm really sorry Dewayne, I didn't mean to offend you. Should we continue
the discussion in normal tone?


Quote:
> Because you think I'm an Access programmer, I'm automatically an idiot?
And
> what if I told you I have a Mac at home, and drive a Toyota truck, and
live
> in Kansas?

> You consider a class that can't handle the simple concept of Unknown as
> advanced?

> And about the Outlook object model; I counted over ten items defined As
> Object or Variant in just the first three entries.

> Oh, excuse me, I see you're Russian. That explains everything.



> > Everything is messed up in heads and code of Access programmers: GUI,
> > business logic, data access...

> > Dewayne, do you seriously consider late-bound, type-weak code as more
> > advanced? I surely don't. What for real-world scenario, add a reference
> to,
> > say, Outlook object model, and count how many properties there are of
Type
> > Variant. My bet: zero.

> > Regards,
> > Alexander

> > BTW, there's a serious problem in your code:

> > Properties.Add rs("FirstName"), "FirstName"

> > it adds not the value of the field, but the field object instead. Hope
> this
> > is just a typo.



> > > > OK, let's design a simple Customer class:

> > > > Public OID As Long
> > > > Public FirstName As String
> > > > Public MiddleName as String
> > > > Public LastName as String

> > > > Where's a place for Null here? Or are you proposing to give up data
> > types
> > > > and use Variant everywhere?

> > > A simple class gets you simple features.

> > > Let's design it a little differently:

> > > Class Customer:

> > > Public Properties As New Collection

> > > Public Sub Fetch(ByVal lCustID As Long)

> > >     Dim rs As Recordset
> > >     Set rs = g_db.OpenRecordset("select * from customer where ID = " &
> > > CStr(lCustID))

> > >     If rs.EOF Then Err.Raise kNotFound, "Customer not found."

> > >     If Not IsNull(rs("FirstName")) Then
> > >         Properties.Add rs("FirstName"), "FirstName"
> > >     End If
> > >     If Not IsNull(rs("MiddleName")) Then
> > >         Properties.Add rs("MiddleName"), "MiddleName"
> > >     End If
> > >     If Not IsNull(rs("LastName")) Then
> > >         Properties.Add rs("LastName"), "LastName"
> > >     End If

> > >     rs.Close: Set rs = Nothing

> > > End Sub

> > > Public Sub Display()

> > >     On Error GoTo catch

> > >     frmCustomer.FirstName = Me.Properties("FirstName")
> > >     frmCustomer.MiddleName = Me.Properties("MiddleName")
> > >     frmCustomer.LastName = Me.Properties("LastName")

> > > Exit Sub

> > > catch:

> > >     If Err.Number = kItemNotFoundInCollection Then
> > >         Resume Next
> > >     Else
> > >         MsgBox Err.Description
> > >     End If

> > > End Sub

> > > This is similar to Douglas' mention of an XML scenario, where if a
> > property
> > > isn't known, it simply isn't included. This could be done a little
more
> > > elegantly by replacing the standard VB Collection with a home-grown
one
> > > which has an Exists method to check for property existence:

> > >     If Me.Properties.Exists("FirstName") Then
> > >         frmCustomer.FirstName = Me.Properties("FirstName")
> > >     Else
> > >         frmCustomer.FirstName = "<unknown>"
> > >     End If

> > > "Null" may be a DB-specific thing, but "Unknown" most definitely
isn't.
> > Real
> > > world code has to handle real world scenarios, meaning handling
Unknown,
> > > which your "simple" class doesn't do. Using Null is one way to do
that,
> > > using variants is another, using a NoMiddleName property is another,
and
> > > obviously there are others. Which one is best? Standard consultant's
> > answer:
> > > It depends.



Mon, 29 Dec 2003 03:36:30 GMT  
 
 [ 18 post ]  Go to page: [1] [2]

 Relevant Pages 

1. To Null, or not to Null...?

2. Null Null Null!!!!!!!!!

3. Null, Null, Null!

4. double null question [2 * Null = Null] !!!

5. Alter Table with NULL/NOT NULL options

6. Null = Null Not Working ?

7. null not null

8. When is Null not Null

9. Null = Null error

10. null in vb and null in sql7

11. How can I pass NULL value to WinAPI which is expecting a NULL pointer

12. IS NULL vs = NULL - ODBC Drivers

 

 
Powered by phpBB® Forum Software