decimal and recorset

cletenaff

Hello,

I tried to import datas from a SQL database to navision using ADO.

My new record is inserted correctly for text or date or integer values, except for decimal values which are always equal to 0. ](*,)

Note that I use a view in SQL and not a table. I also tried to pass through a variant but I have this error message(translated from French):
"This data type is not supported by C/SIDE. You can only have access to datas from the following data types: VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BTSR and VT_BOOL."

Any help will be welcome!
Thanks.


My locals are :

AdoCon_l   'Microsoft ActiveX Data Objects 2.8 Library'.Connection   // Automation 
AdoRecordSet_l   'Microsoft ActiveX Data Objects Recordset 2.8 Library'.Recordset   // Automation 
AdoRecordSetConn_l   'Microsoft ActiveX Data Objects 2.8 Library'.Recordset   // Automation 
AdoStream_l   'Microsoft ActiveX Data Objects 2.8 Library'.Stream   // Automation 
AdoField_l      'Microsoft ActiveX Data Objects 2.8 Library'.Field   //Automation 
RecClient_l    //Record      //Customer 

My C/AL code looks like:

CREATE(AdoCon_l); 
CREATE(AdoRecordSet_l); 

AdoCon_l.ConnectionString := 'Provider=SQLOLEDB.1;Data Source=QUALISH;Initial Catalog=aigle;User Id=username;Password=pwd'; 
AdoCon_l.Open; 

AdoRecordSetConn_l := AdoCon_l.Execute('SELECT * FROM  MyView WHERE BoolNew=1','',0); 
AdoRecordSet_l.Open('SELECT * FROM  MyView WHERE BoolNew=1',AdoCon_l,3,1);    
//3 for CursorType=adOpenStatic and 1 for LockType = adOpenKeyset 

IF NOT AdoRecordSet_l.EOF THEN 
REPEAT 
  IF AdoRecordSet_l.Fields.Item('numClient').Value.ISTEXT THEN BEGIN 
    CREATE(AdoStream_l); 
    AdoStream_l.Open; 
    AdoField_l := AdoRecordSet_l.Fields.Item('numClient')) 
    AdoStream_l.WriteText(AdoField_l.Value); 
    AdoStream_l.Position := 0; 
    IF EVALUATE(ReturnValue_R,AdoStream_l.ReadText) THEN 
        RecClient_l."No." := AdoStream_l.ReadText; 
  END; 
  IF AdoRecordSet_l.Fields.Item('name').Value.ISTEXT THEN BEGIN 
    CREATE(AdoStream_l); 
    AdoStream_l.Open; 
    AdoField_l := AdoRecordSet_l.Fields.Item('name')) 
    AdoStream_l.WriteText(AdoField_l.Value); 
    AdoStream_l.Position := 0; 
    IF EVALUATE(ReturnValue_R,AdoStream_l.ReadText) THEN 
       RecClient_l.Name := AdoStream_l.ReadText; 
  END; 
  IF AdoRecordSet_l.Fields.Item('amount').Value.ISDECIMAL THEN BEGIN 
    CREATE(AdoStream_l); 
    AdoStream_l.Open; 
    AdoField_l := AdoRecordSet_l.Fields.Item('amount')) 
    AdoStream_l.WriteText(AdoField_l.Value); 
    AdoStream_l.Position := 0; 
    IF EVALUATE(ReturnValue_R,AdoStream_l.ReadText) THEN 
       RecClient_l.Amount := AdoStream_l.ReadText; 
  END; 

  RecClient_l.INSERT; 

  AdoRecordSet_l.MoveNext; 

UNTIL AdoRecordSet_l.EOF; 

AdoRecordSet_l.Close; 
AdoCon_l.Close; 

CLEAR(AdoRecordSet_l); 
CLEAR(AdoCon_l);

kine

You cannot read the NULL value. You need to test the field size for 0 to prevent reading NULL.

tentacle

I have the same problem here but I am sure I dont read NULL values.

If my source is a text field everything is ok but if my source field is e.g. decimal the use of "...item(xy).value;" produces this error!?

kine

I have no problem with that... may be that the Server backend is returning the vaule in another format than the client is expecting...

tentacle

Very strange behaviour:

...item.ActualSize gives me the right number for every text field. But for all other fields like DECIMAL and NUMERIC it gives me "19"!?

And still ...item.Value produces the above mentioned error!

If I take a look into the database (with e.g. dBASE) my DECIMAL field has a length of 3!

Can anyone help please?

tentacle

hmmm...item.status gives "131" and MSDN reports:

adNumeric
131
Indicates an exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).

Is Navision unable to handle this data type??

Saalek

Hi

Try using FLOAT datatype in SQL. I had the same problem las year, and I solved it changing SQL table datatype

Regards

tentacle

Thanks now it is working.

Although I could not change the source datatype I added a CAST to FLOAT to my SQL SELECT statement.