NULL value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NULL value

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-28 : 08:37:15

Hi,

i m trying to insert some null values and not null values into DB with code segment below;

if( count < idsize)
{
TableQuery->Params->Items[1]->AsFloat = NULL;
count++;
}
else
{
temp = ListBox1->Items->Strings[count1];
pos = temp.Pos(' ');

TableQuery->Params->Items[1]->AsFloat = StrToFloat(temp.SubString(pos+1,7));
count1++;
}

There is no problem here, but when i wanna use this statement to select only not NULL values ;

select [column1] from [table] where [column1] IS NOT NULL

it brings me the whole results with NULL values included! And NULL values shows .0000 in DB. Would you please help?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-28 : 08:42:49

funny sql server code.

>> And NULL values shows .0000 in DB
Implies that you aren't inserting null which would account for the query returning everything.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 08:44:08

Can you post the table structure?
May be default value is defined on that column

Madhivanan

Failing to plan is Planning to fail

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-28 : 08:48:15

Whats so funny? Tell me

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-28 : 08:50:37

here is how i created the table

CREATE TABLE [MONTH1USD({SMA}(11)HIGH)] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[HIGH] [varchar] (20) COLLATE Turkish_CI_AS NULL ,
[SMA11] [money] NULL ,
PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-28 : 08:52:23

And no default value is defined

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 08:53:04

quote:
select [column1] from [table] where [column1] IS NOT NULL

it brings me the whole results with NULL values included! And NULL values shows .0000 in DB. Would you please help?

Did you execute in Query Analyser or you got result at front end application?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 08:55:52

I think u are confusing NULL in the front-end with the NULL as seen by the SQL server. Are u sure they are both same?

Harsh Athalye
India.
"Nothing is Impossible"

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-28 : 09:00:59

Both

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-28 : 09:07:26

TableQuery->Params->Items[1]->AsFloat = NULL;

i m trying to insert NULL and i expect to get not NULL values with
select [column1] from [table] where [column1] IS NOT NULL

how come they are NOT same?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-28 : 09:08:04

To repeat
>> And NULL values shows .0000 in DB
Implies that you aren't inserting null which would account for the query returning everything.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 09:12:37

Why I'm saying this is becoz after the insert in ur select query u are getting .0000 instead of NULL. So the NULL which u are inserting through front-end is not recognized as a NULL by the SQL server.

Harsh Athalye
India.
"Nothing is Impossible"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-28 : 10:09:39

The scalar values in the programming language (C++?) can't be "null", they are initialized to their default value (0) in the case of numerical datatypes.
The pointer to the value can be "null".
but a null reference is not the same thing as a database NULL.

Database NULL should be avoided anyway, their primary usage is to cover for bad database design and cause errors and confusion.

_________________________
I have null problems

rockmoose

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-02 : 09:00:07

In c/c++ NULL is a defined constant of value 0 (sometimes casted), but buttomline, NULL will in almost all cases end up being treated a litteral integer of value 0. Is has absolutly nothing in common with the a SQL NULL, which is not a value (cant be compared to in any way) but a state (or attribute) indicating not defined.
Admittely pointers in c/c++ assigned to NULL (or just plain 0) has a somewhat special meaning, but more by convention than mere language, so for instanse

void *p = NULL;
printf("%p", p + 1);

is is 1, where as

DECLARE @p int
SET @p = NULL
SELECT @p + 1

returns NULL.

From your code example it seems like TableQuery->Params->Items[] is some kind of a Variant object, and such often have a way to marked as NULL. So What you need to check out is if the Items[] has a method (or property or member function, call it what you like) like SetNull().

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources