Testing and reacting to NULL values

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Jerbear
Posts: 13
Joined: Sun 02 Nov 2008 10:33
Location: Ireland

Testing and reacting to NULL values

Post by Jerbear » Wed 03 Aug 2016 08:03

In my table I have a field called closed.
If the case is closed it will contain the date it was closed otherwise it remains empty.
I search my DB for a case finishing my query with

Code: Select all

"and closed is null" 
so it only displays open cases.
My Query also converts the closed date

Code: Select all

convert(varchar(12), closed,103) as closed
However, having executed the query and it returns no results it's possibly one of two things.
Either the case does not exist or the case is closed.
I need to know if this Field had a value so I can pop up a message
to the user to advise them that IT IS a CLOSED case.
I have tried the following two commands without success....

Code: Select all

var IsClosed : String;
IsClosed := DBSearchGrid.DataSource.DataSet.FieldByName('closed').AsString;
If IsClosed <> '' Then ShowMessage('Case is a CLOSED Case');

IsClosed := DBSearchGrid.DataSource.DataSet.Fields.Fields[13].AsString;
If IsClosed <> '' Then ShowMessage('Case is a CLOSED Case');
If I search for a known closed case both commands above return nothing in IsClosed.

I use both of the above in other parts of my code without problem.

Any Ideas....

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Testing and reacting to NULL values

Post by ViktorV » Wed 03 Aug 2016 10:30

To detect that a field is set to NULL, you should use the IsNull property. For example:

Code: Select all

if MSQuery.FieldByName('closed').IsNull then
  ShowMessage('Value is NULL');

Jerbear
Posts: 13
Joined: Sun 02 Nov 2008 10:33
Location: Ireland

Re: Testing and reacting to NULL values

Post by Jerbear » Thu 04 Aug 2016 13:48

Thank you ViKtorV.

That works great, but if the field is NOT NULL then how can I get the actual value of that field
into a variable. The code I used, shown in previous post, does not work on this field but works on all
other fields...

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Testing and reacting to NULL values

Post by ViktorV » Thu 04 Aug 2016 14:34

The code you provided above should return a correct field value. Please compose a small sample reproducing the described behavior and send it to viktorv*devart*com including scripts for creating database objects, in order for us to be able to answer you in more details.

Post Reply