Reading Directly form dBase files using ADO

shrekut

I have the following code placed behind a button on a form:

VAR
DBFConnect Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Connection";
DBFRecord Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Recordset";
Customer Record 18;
CommDialog OCX 'Microsoft Common Dialog Control, version 6.0 (SP6)'";
Path Text[250];
CutOut Text[250];
Position Integer;
Length Integer;
FileToUse Text[30];


CLEAR(DBFConnect);
CLEAR(DBFRecord);
CLEAR(CommDialog);

CREATE(DBFConnect);
CREATE(DBFRecord);
CommDialog.DialogTitle := 'Open dBase files';
CommDialog.Filter := '(*.dbf)|*.dbf';
CommDialog.ShowOpen;
IF CommDialog.FileName = '' THEN
EXIT;
Position := 1;
CutOut := CommDialog.FileName;
Length := STRLEN(CommDialog.FileName);
WHILE Position <> 0 DO BEGIN
Position := STRPOS(CutOut,'\');
IF Position = 0 THEN
FileToUse := CutOut;
Path += COPYSTR(CutOut,1,Position);
CutOut := DELSTR(CutOut,1,Position);
END;
DBFConnect.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Path + ';Extended Properties=DBASE III;');
DBFRecord.Open(FileToUse,DBFConnect); // Open file
IF NOT(DBFRecord.EOF) THEN
DBFRecord.MoveFirst();
IF NOT(DBFRecord.EOF) THEN BEGIN
REPEAT
Customer.INIT;
Customer."No." := FORMAT(DBFRecord.Fields().Item('CUSTOMER').Value); // No.
Customer.Name := DBFRecord.Fields().Item('Name').Value; // Name
Customer.Address := DBFRecord.Fields().Item('ADDR1').Value; // Address 1
Customer."Address 2" := DBFRecord.Fields().Item('ADDR2').Value; // Address 2
Customer.City := DBFRecord.Fields().Item('CITY').Value; // City
Customer.County := DBFRecord.Fields().Item('STATE').Value; // State
Customer."Post Code" := DBFRecord.Fields().Item('ZIP').Value; // Zip
Customer."Phone No." := DBFRecord.Fields().Item('PHONE').Value; // Phone
Customer.INSERT;
DBFRecord.MoveNext();
UNTIL DBFRecord.EOF;
END;

DBFRecord.Close();
DBFConnect.Close();

The Problem I'm having is if a filed in the dbf file has no value, and I'm trying to assign it to a field in Navision, I get an error message telling me that I'm using a wrong variant type. Does anyone know what to do with a blank value like this?

:?:

kine

It is problem, because Navision is not able to work with NULL type. Try to test the variable through FORMAT(DBFRecord.Fields().Item('ZIP').Value)='xxx'. At first, you need to found which string return the FORMAT when the value is the null value... after that, you can create your "Retype" function...

shrekut

Thanks kine. I had tried your suggestion already. But after reading your suggestion I tried displaying the returned text through using:

MESSAGE('Null Value: %1',FORMAT(DBFRecord.Fields().Item('ADDR2').Value));
EXIT;

but the error still occurs. I wish there was an IsError function. I don't think that c/AL will let me read from the ado control if it does not understand the data type.

kine

Than you can change your SQL query to format the output IFNULL if it is possible...

shrekut

I found the answer it seems. I used the ActualSize property of the recordset.


IF DBFRecord.Fields().Item('ADDR1').ActualSize <> 0 THEN
Customer.Address := DBFRecord.Fields().Item('ADDR1').Value; // Address 1


Thanks for the help!

Dakkon

I did something similar but I always recommend creating your own wrapper dll and using that from within Navision. That way you can better predict what return values you get and you can implement proper error handling inside your custom dll. I also used the Microsoft ActiveX Data Objects library (version up to you ...I prefer 2.7) instead of the ocx. I always like going straight to the dll's instead of an ocx when possible (my preference). Just my 2 cents

Asallai

shrekut wrote:

I found the answer it seems. I used the ActualSize property of the recordset.


IF DBFRecord.Fields().Item('ADDR1').ActualSize <> 0 THEN
Customer.Address := DBFRecord.Fields().Item('ADDR1').Value; // Address 1


Thanks for the help!

Hi shrekut and all!
I've tried this above but the this error message is show up when I try to compile the source (F11):

This message is for C/AL programmers:
This data type is not supported by C/SIDE. You can access data of
any of the following data types:
VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR and
VT_BOOL

I use 2.6 Navision, so the
Variant type of variable is not supported! :-k

Anyone can help me? :?:
Thank you!

A

Arjan_Somers

the VT_SOMETHING types are variants types (VT = Variant Type). So you got:
VT_BOOL wich means, you can use a variant with a boolean value.
which means you can do:
navision_var := ado.field.value;
If ado.field.type is boolean.

So you got a list of datatypes. But there is for example no VT_TIME, making it impossible to use time variables (Or use strings, and parse them yourself.)

So if you use a datatype directly which is not on your list of VT's, you will get that error you mentioned.

Hope this makes sense


And for the null-vlues:

i use a function i found here on mibuso:

NULL(pfldCheck : Automation "'Microsoft ActiveX Data Objects 2.8
Library'.Field") : Boolean

EVALUATE(lintLengte, FORMAT(pfldCheck.ActualSize));
EXIT(lintLengte = 0);

Asallai

Thanks Arjan, I'll try this composition, but everywhere I stuck in the same problem, because isn't Variant type in 2.6 and every outside function what is call Variant type, is not operating well. ](*,)
But the method is OK.
Thanks again!

Arjan_Somers

Exactly what are you trying to do?

Could you post some code wich produces this error?

Asallai

Arjan Somers wrote:

Exactly what are you trying to do?

Could you post some code wich produces this error?

Yes, here it is a part of that:

ADOConnect;

...
   ADORecSet.Open('Select * from '+ txtMDBTableName+' where ProcessCode="TELT"',ADOConnection2,OpenMethod,LockMethod);
   ADORecSet.MoveFirst;
   WHILE NOT ADORecSet.EOF DO BEGIN
     InitVariablesValue;
     SetVariablesValue;
     ...
    
     ADORecSet.MoveNext;
   END;

The Process Code is a field of the Access database
The InitVariablesValue and SetVariablesValue are sets the global variable because this is a Dataport Object! So first read every field to global variables, then I use these variables after this.

The ADO Connect procedure:

OpenMethod := 2;
LockMethod := 3;

IF ISCLEAR(ADOConnection2) THEN BEGIN
   IF NOT CREATE(ADOConnection2) THEN CREATE(ADOConnection2);
END;

IF ISCLEAR(ADORecSet) THEN BEGIN
   IF NOT CREATE(ADORecSet) THEN CREATE(ADORecSet);
END;

IF ISCLEAR(ADORecSet) THEN BEGIN
   IF NOT CREATE(ADOCommand) THEN CREATE(ADOCommand);
END;

IF ISCLEAR(ADOStream) THEN BEGIN
   IF NOT CREATE(ADOStream) THEN CREATE(ADOStream);
END;

ConnString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+txtMDBFileName +';';
Source=C:\Temp\t.mdb'+CurrDataport.FILENAME+';';

ADOConnection2.ConnectionString(ConnString);

ADOConnection2.Open;

CREATE(pfldCheck);

Variables:

Name DataType Subtype Length ADOConnection2 Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection ADORecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset ADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command ADOStream Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Stream pfldCheck Automation 'Microsoft ActiveX Data Objects Recordset 2.8 Library'.Field

In summary my problem is to check, if some field is NULL or EMPTY because I cannot handle these values in Navision 2.6.
Maybe I have to change my Access database, where the value is EMPTY there will be '0'? :?:

ashu_bhatt99

Asallai

Did you get a solution of your problem. I am also having a same problem and dont know what to do!! ](*,)

Ashok

fedor

shrekut wrote:

I have the following code placed behind a button on a form:

VAR
DBFConnect Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Connection";
DBFRecord Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Recordset";
Customer Record 18;
CommDialog OCX 'Microsoft Common Dialog Control, version 6.0 (SP6)'";
Path Text[250];
CutOut Text[250];
Position Integer;
Length Integer;
FileToUse Text[30];


CLEAR(DBFConnect);
CLEAR(DBFRecord);
CLEAR(CommDialog);

CREATE(DBFConnect);
CREATE(DBFRecord);
CommDialog.DialogTitle := 'Open dBase files';
CommDialog.Filter := '(*.dbf)|*.dbf';
CommDialog.ShowOpen;
IF CommDialog.FileName = '' THEN
EXIT;
Position := 1;
CutOut := CommDialog.FileName;
Length := STRLEN(CommDialog.FileName);
WHILE Position <> 0 DO BEGIN
Position := STRPOS(CutOut,'\');
IF Position = 0 THEN
FileToUse := CutOut;
Path += COPYSTR(CutOut,1,Position);
CutOut := DELSTR(CutOut,1,Position);
END;
DBFConnect.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Path + ';Extended Properties=DBASE III;');
DBFRecord.Open(FileToUse,DBFConnect); // Open file
IF NOT(DBFRecord.EOF) THEN
DBFRecord.MoveFirst();
IF NOT(DBFRecord.EOF) THEN BEGIN
REPEAT
Customer.INIT;
Customer."No." := FORMAT(DBFRecord.Fields().Item('CUSTOMER').Value); // No.
Customer.Name := DBFRecord.Fields().Item('Name').Value; // Name
Customer.Address := DBFRecord.Fields().Item('ADDR1').Value; // Address 1
Customer."Address 2" := DBFRecord.Fields().Item('ADDR2').Value; // Address 2
Customer.City := DBFRecord.Fields().Item('CITY').Value; // City
Customer.County := DBFRecord.Fields().Item('STATE').Value; // State
Customer."Post Code" := DBFRecord.Fields().Item('ZIP').Value; // Zip
Customer."Phone No." := DBFRecord.Fields().Item('PHONE').Value; // Phone
Customer.INSERT;
DBFRecord.MoveNext();
UNTIL DBFRecord.EOF;
END;

DBFRecord.Close();
DBFConnect.Close();

The Problem I'm having is if a filed in the dbf file has no value, and I'm trying to assign it to a field in Navision, I get an error message telling me that I'm using a wrong variant type. Does anyone know what to do with a blank value like this?

:?:

Help! I have use this example, but error in line:
DBFRecord.Open(FileToUse,DBFConnect); // Open file

fedor

fedor wrote:

shrekut wrote:

I have the following code placed behind a button on a form:

VAR
DBFConnect Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Connection";
DBFRecord Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Recordset";
Customer Record 18;
CommDialog OCX 'Microsoft Common Dialog Control, version 6.0 (SP6)'";
Path Text[250];
CutOut Text[250];
Position Integer;
Length Integer;
FileToUse Text[30];


CLEAR(DBFConnect);
CLEAR(DBFRecord);
CLEAR(CommDialog);

CREATE(DBFConnect);
CREATE(DBFRecord);
CommDialog.DialogTitle := 'Open dBase files';
CommDialog.Filter := '(*.dbf)|*.dbf';
CommDialog.ShowOpen;
IF CommDialog.FileName = '' THEN
EXIT;
Position := 1;
CutOut := CommDialog.FileName;
Length := STRLEN(CommDialog.FileName);
WHILE Position <> 0 DO BEGIN
Position := STRPOS(CutOut,'\');
IF Position = 0 THEN
FileToUse := CutOut;
Path += COPYSTR(CutOut,1,Position);
CutOut := DELSTR(CutOut,1,Position);
END;
DBFConnect.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + Path + ';Extended Properties=DBASE III;');
DBFRecord.Open(FileToUse,DBFConnect); // Open file
IF NOT(DBFRecord.EOF) THEN
DBFRecord.MoveFirst();
IF NOT(DBFRecord.EOF) THEN BEGIN
REPEAT
Customer.INIT;
Customer."No." := FORMAT(DBFRecord.Fields().Item('CUSTOMER').Value); // No.
Customer.Name := DBFRecord.Fields().Item('Name').Value; // Name
Customer.Address := DBFRecord.Fields().Item('ADDR1').Value; // Address 1
Customer."Address 2" := DBFRecord.Fields().Item('ADDR2').Value; // Address 2
Customer.City := DBFRecord.Fields().Item('CITY').Value; // City
Customer.County := DBFRecord.Fields().Item('STATE').Value; // State
Customer."Post Code" := DBFRecord.Fields().Item('ZIP').Value; // Zip
Customer."Phone No." := DBFRecord.Fields().Item('PHONE').Value; // Phone
Customer.INSERT;
DBFRecord.MoveNext();
UNTIL DBFRecord.EOF;
END;

DBFRecord.Close();
DBFConnect.Close();

The Problem I'm having is if a filed in the dbf file has no value, and I'm trying to assign it to a field in Navision, I get an error message telling me that I'm using a wrong variant type. Does anyone know what to do with a blank value like this?

:?:

Help! I have use this example, but error in line:
DBFRecord.Open(FileToUse,DBFConnect); // Open file

Please help!