AdoTable FieldByName OR FieldValues

Hi there,

I have a database and all runs smooth, except sometimes I get the error, can't convert type NULL to type STRING, and I notice this happens when I use either FieldbyName or FieldValues (can't quite remember)

Just wonder which is the better to use when getting data from a database which may not be all there... (ie contain NULL values)

LVL 10
Who is Participating?
Pierre CorneliusCommented:
I normally use a little helper function that may help you.

function nz(AValue, ValueIfNull: variant): variant; //name comes from similar VB function
  if VarIsNull(AValue)
    then result:= ValueIfNull
    else result:= AValue;

Now you can use something like this anywhere:
  SomeEdit.Text:= nz(SomeADOTable['SomeField'], '');
  SomeEdit.Text:= nz(SomeADOTable.FieldByName('SomeField').Value, '');

In essence it is the same as:
  if SomeDataset.FieldByName('abc').IsNull
    then SomeEdit.Text:= ''
    else SomeEdit.Text:= SomeDataset.FieldByName('abc').AsString;

TheRealLokiSenior DeveloperCommented:
can you do something like
if FieldByName('NAME').IsNull
sorry,dont use ADO here, just guessing
Kyle FosterCEOCommented:
You can check FieldByName('FieldName').IsNull but I don't have to do this with ADO and SQL Server using FieldByName.  I do have to do it when reading dates though.  

If memory serves me right the FieldByName function looks up the field index and then calls FieldValue[ndx] so you should get the error no matter which one you use.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

If you are looping through a table then you could also use

if <TableName>.Fields[iCounter].IsNull then <do something>
Kyle FosterCEOCommented:
NZ ??  Cool, sounds like Access though :)
Pierre CorneliusCommented:
kfoster: Exactly where the name comes from... hehe
Hello Sir,

 You could check it as follows

if  ADOTable1.FieldByName('InvNo').IsNull or (ADOTable2.FieldByName('InvNo').AsString='') then...

where InvNo is assumed to be of String.

with regards
wildzeroAuthor Commented:
It seems that doing

Doesn't throw an exception like
SomeADOTable.FieldValues ['SomeField']  does when the value is null...
you should either use
  VarToStr(FieldValues ['SomeField'])

Using null variants for string operations can raise that kind of exceptions.
yes, Imthiyz_ph is right, if you use FieldByName(SomeField).asString an empty string will be returned if the value in the DB is NULL.
Kyle FosterCEOCommented:
The answer is in the db.pas source file.

With FieldValues[] it is returning a variant.  there is no internal check for NULL it is just returned as NULL.

With a TStringField which is what FieldByName returns if the field is a string the following function translates the Variant into a string for you so a STRING is returned.

function TStringField.GetAsString: string;
  if not GetValue(Result) then Result := '';

if you dig deeper into the code you will find that the .GetValue function returns false is the datasize=0.  This results in an emptystring being returned ''.

So FieldValues may be a little faster (it still uses variants) because it removes 1 layer of encapsulation and it also removes the lookup of the FieldName in FieldByName(FieldName).  But, unless you then lose some of this 'time saved' because you will have to check for NULL yourself.  

Also keep in mind that with FieldValues you are requiring the field order to be the same.
If you are doing a query 'SELECT * FROM TABLENAME' and the fields returned are
    RID                     : Integer
    Name                  : String
    SomethingElse     : String;

and you add a new field to the table 'It must be at the end of the table or your indexes will be off'
WHEREAS; 'Select RID,NAME,SOMETHINGELSE from Table' will always result in the correct field order no matter where the new field is in the table structure.


Kyle FosterCEOCommented:
Also, I think you should just use PierreC's NZ function and get on with it  :)
wildzeroAuthor Commented:
Thanks guys :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.