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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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;


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.