Several texts and websites have suggestions for dealing with NULLs, but I 

can't find one that really

                               fits my situation. I have an SQL statement 

that Updates my Access2K database table with the values

                               from the form (I first pull them in with an 

SQL statement that brings the nulls in with no

                               problem).  Anything with a null value 

(strings or numbers) keeps the SQL statement from executing. (If I

                               take the null fields out of the query, it 

runs like a dream).  

                               I have found suggestions to concatenate a 

blank space " " to all the string values when they are read in, or

                               run the IsNull function on every field. My 

fields on the form are not read in with a loop.  I used Visual

                               Interdev to create tables and drop the 

fields into cells, so in the code they are strung all over the page. I

                               can't think of an efficient way to check 

all these for null.  

                               Anyone out there who is smarter than me?

                               Thanks,

                               Garnette Lilly

Have you checked your table definition? You can defined a field with that

allows NULL values.

By default, I believe that Access creates a field that doesn't allow NULL

values. Simply re-design

the table to allow NULL values and empty strings.



I do this all the time, especially with my web related databases. Why??? The

less code that has to

run on the ASP page, the faster the performance. Thus if I allow NULLS and

blank values, then

I don't have to test for them in my ASP pages.



hth

Dallas









----- Original Message -----

From: "Garnette Lilly Price" <garnettelillyprice@w...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, March 28, 2001 5:16 PM

Subject: [asp_databases] Best Solution for Null





> Several texts and websites have suggestions for dealing with NULLs, but I

can't find one that really

>                                fits my situation. I have an SQL statement

that Updates my Access2K database table with the values

>                                from the form (I first pull them in with an

SQL statement that brings the nulls in with no

>                                problem).  Anything with a null value

(strings or numbers) keeps the SQL statement from executing. (If I

>                                take the null fields out of the query, it

runs like a dream).

>                                I have found suggestions to concatenate a

blank space " " to all the string values when they are read in, or

>                                run the IsNull function on every field. My

fields on the form are not read in with a loop.  I used Visual

>                                Interdev to create tables and drop the

fields into cells, so in the code they are strung all over the page. I

>                                can't think of an efficient way to check

all these for null.

>                                Anyone out there who is smarter than me?

>                                Thanks,

>                                Garnette Lilly

>

>

>

How about "field IS NUL" or "field IS NOT NULL" in your TSQL statments?



Tomm



> -----Original Message-----

> From: Garnette Lilly Price [mailto:garnettelillyprice@w...]

> Sent: Wednesday, March 28, 2001 5:16 PM

> To: ASP Databases

> Subject: [asp_databases] Best Solution for Null

>

>

> Several texts and websites have suggestions for dealing with

> NULLs, but I can't find one that really

>                                fits my situation. I have an SQL

> statement that Updates my Access2K database table with the values

>                                from the form (I first pull them

> in with an SQL statement that brings the nulls in with no

>                                problem).  Anything with a null

> value (strings or numbers) keeps the SQL statement from executing. (If 

I

>                                take the null fields out of the

> query, it runs like a dream).  

>                                I have found suggestions to

> concatenate a blank space " " to all the string values when they

> are read in, or

>                                run the IsNull function on every

> field. My fields on the form are not read in with a loop.  I used 

Visual

>                                Interdev to create tables and drop

> the fields into cells, so in the code they are strung all over the 

page. I

>                                can't think of an efficient way to

> check all these for null.  

>                                Anyone out there who is smarter than 

me?

>                                Thanks,

>                                Garnette Lilly

>

>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: Have you checked your table definition? You can defined a field with that

: allows NULL values.

: By default, I believe that Access creates a field that doesn't allow NULL

: values. Simply re-design

: the table to allow NULL values and empty strings.

:

: I do this all the time, especially with my web related databases. Why???

The

: less code that has to

: run on the ASP page, the faster the performance. Thus if I allow NULLS and

: blank values, then

: I don't have to test for them in my ASP pages.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



That's not a good way to design a database.



Typically  a NULL value indicates *undefined*, or *unknown*, whereas a zero

length string indicates exactly that, an absence of a value, or no value.



They are not the same: NULL <> ZLS



For example, suppose you have a Fax field. If you don't know whether I have

a fax number or not, then you should store a NULL in that field. If you know

that I don't have a fax number, you store a ZLS. This way you can

differentiate between those people that don't have a fax number, and those

people where you don't know whether they have a fax number or not.



Clear as mud?



Cheers

Ken

Garnette:



I use Len(Trim and it works well for me. Something like:



If Len(Trim(RS("OrderID"))) > 0 Then



So, if there is data in the field, it meets the If statement. This way, you

check for NULL, white spaces, etc.



HTH,



Bruce Clark

I think maybe I failed to spelled it out clearly.



When a field is defined to accept NULL values or ZLS, then

you won't get an error message if you attempt to save NULL or

ZLS data to the table.



When data is posted by a webpage, and one uses the syntax

fax = request.form("fax"), then if the user failed to enter a value

for the fax number, you get an empty string.



Attempting to  "INSERT fax INTO table" where fax is NOT defined

to allow NULLS or ZLS, will generate an error message. Thus,

before you insert/update the table, your code would have to perform

some checking, ie.



IF IsEmpty(fax) or IsNull(fax) then blah, blah....



Which to me is wasted CPU cycles. Because I either have a value

or I don't have a value. The reason for not having the value is irrelevant



Ofcourse, if I was really smart, I probably perform some client-side

validation on those fields where I cared to know whether they have

a value or not.



Clear as Mud??



Dallas Martin



----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, March 29, 2001 10:35 PM

Subject: [asp_databases] Re: Best Solution for Null





>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> : Have you checked your table definition? You can defined a field with

that

> : allows NULL values.

> : By default, I believe that Access creates a field that doesn't allow

NULL

> : values. Simply re-design

> : the table to allow NULL values and empty strings.

> :

> : I do this all the time, especially with my web related databases. Why???

> The

> : less code that has to

> : run on the ASP page, the faster the performance. Thus if I allow NULLS

and

> : blank values, then

> : I don't have to test for them in my ASP pages.

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

> That's not a good way to design a database.

>

> Typically  a NULL value indicates *undefined*, or *unknown*, whereas a

zero

> length string indicates exactly that, an absence of a value, or no value.

>

> They are not the same: NULL <> ZLS

>

> For example, suppose you have a Fax field. If you don't know whether I

have

> a fax number or not, then you should store a NULL in that field. If you

know

> that I don't have a fax number, you store a ZLS. This way you can

> differentiate between those people that don't have a fax number, and those

> people where you don't know whether they have a fax number or not.

>

> Clear as mud?

>

> Cheers

> Ken

>

>

> ---

> SoftArtisans helps developers build robust, scalable Web applications!

> Excel Web reports, charts: http://www.softartisans.com/excelwriter.html

> File uploads: http://www.softartisans.com/saf.html

> Transactional file management: http://www.softartisans.com/saf1.html

> Scalability: http://www.softartisans.com/saxsession.html

> ASPstudio value pack: http://www.softartisans.com/aspstudiosuite.html




$subst('Email.Unsub')

>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: IF IsEmpty(fax) or IsNull(fax) then blah, blah....

:

: Which to me is wasted CPU cycles. Because I either have a value

: or I don't have a value. The reason for not having the value is irrelevant



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Maybe it doesn't matter to you...but someday you'll be working on a large

project where it does matter. Zero length strings are not the same as NULLs.

NULLs are for undefined values. Zero length strings are where your want to

store nothing.



The type or argument above seems like the reasoning one would use for using

variants - if I use variants all the time I wont have to worry about what

goes into them...I don't have to check to make sure that they are actually

integers, or strings, or dates...because that wastes CPU cycles...



Cheers

Ken

Yeah, Ken. I know the difference between NULL AND ZLS.



As for variants, ASP only understands variants.

When I code in VB I absolutely defined my data types

because, variants use more space and cpu cycles.

But, since most of my current code is in ASP,

I favor using as many "default" logic constructs as possible.



Thanks,

Dallas



----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, March 30, 2001 7:32 AM

Subject: [asp_databases] Re: Best Solution for Null





> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> : IF IsEmpty(fax) or IsNull(fax) then blah, blah....

> :

> : Which to me is wasted CPU cycles. Because I either have a value

> : or I don't have a value. The reason for not having the value is

irrelevant

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

> Maybe it doesn't matter to you...but someday you'll be working on a large

> project where it does matter. Zero length strings are not the same as

NULLs.

> NULLs are for undefined values. Zero length strings are where your want to

> store nothing.

>

> The type or argument above seems like the reasoning one would use for

using

> variants - if I use variants all the time I wont have to worry about what

> goes into them...I don't have to check to make sure that they are actually

> integers, or strings, or dates...because that wastes CPU cycles...

>

> Cheers

> Ken

>

>