?
Solved

AdoTable FieldByName OR FieldValues

Posted on 2006-04-10
13
Medium Priority
?
2,309 Views
Last Modified: 2011-09-20
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)

Thanks
Nick
0
Comment
Question by:wildzero
  • 4
  • 2
  • 2
  • +5
13 Comments
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 16422294
can you do something like
if FieldByName('NAME').IsNull
sorry,dont use ADO here, just guessing
0
 
LVL 7

Expert Comment

by:kfoster11
ID: 16423379
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.
0
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 1000 total points
ID: 16424026
I normally use a little helper function that may help you.

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

Now you can use something like this anywhere:
...
  SomeEdit.Text:= nz(SomeADOTable['SomeField'], '');
OR
  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;

Regards
Pierre
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:JDSkinner
ID: 16424756
If you are looping through a table then you could also use

if <TableName>.Fields[iCounter].IsNull then <do something>
0
 
LVL 7

Expert Comment

by:kfoster11
ID: 16431024
NZ ??  Cool, sounds like Access though :)
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 16433732
kfoster: Exactly where the name comes from... hehe
0
 
LVL 6

Expert Comment

by:saravananvg
ID: 16434204
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
0
 
LVL 10

Author Comment

by:wildzero
ID: 16442131
It seems that doing
SomeADOTable.FieldByName('SomeField').AsString

Doesn't throw an exception like
SomeADOTable.FieldValues ['SomeField']  does when the value is null...
0
 
LVL 6

Expert Comment

by:Imthiyaz_ph
ID: 16445167
you should either use
  FieldByName('SomeField').AsString
or
  VarToStr(FieldValues ['SomeField'])

Using null variants for string operations can raise that kind of exceptions.
0
 
LVL 1

Expert Comment

by:ruanlab123
ID: 16446156
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.
0
 
LVL 7

Expert Comment

by:kfoster11
ID: 16446686
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;
begin
  if not GetValue(Result) then Result := '';
end;

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.

Cheers...





0
 
LVL 7

Expert Comment

by:kfoster11
ID: 16446690
Also, I think you should just use PierreC's NZ function and get on with it  :)
0
 
LVL 10

Author Comment

by:wildzero
ID: 16492660
Thanks guys :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question