Data Converstion Error using ADO

JoshuaSaves

When I use the code below, everything is fine unless a decimal value is used. It always fails on the first record, regaurdless if it is a 0 value or a positive number. When I pass the value into a variant variable and step through the debugger it says the value of the Variant is "Value". The error message is:

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 searched the forum and found a few threads about this but none of the solutions worked for this issue. ](*,)

FOR i := 1 TO adoRS.Fields.Count DO BEGIN
  IF NOT IsNull(adoRS.Fields.Item(i-1)) THEN BEGIN
    SQLFieldName := adoRS.Fields.Item(i-1).Name;
    vFieldVal := adoRS.Fields.Item(i-1).Value;
    ValueString := FORMAT(adoRS.Fields.Item(i-1).Value); //Error occures here
  END;
END;

Where IsNull is a function to check for Null Values
Where vFieldVal is a Varient

garak

u read also the field Picture :?: . This is an Blob.
How looks you sql query (select * or select "No_", Description, etc.) :?:

Regards

JoshuaSaves

No, I am not reading a BLOB. The issue is the first SQL field in the recordset that is a decimal value. NAV doesn't convert any of them using Evaluate or Format.

garak

ok, you've get the first record with adoRS.moveFirst :?: or loop throw the RecordSet while using adoRS.BOF and the adoRS.Movenext :?:

mhm, I think you have it, so ive no problems with using decimals
do you will connect to an access "database" :?:

if yes is the field datatype long integer or double?

JoshuaSaves

Yes, I am looping through a recordset. I didn't include all the code as I doubt it is relevent.

I have tried both the evaluate and format commands and neither seem to work. Can you give me an example of code where you are taking a decimal value from a ADO Recordset Field and converting it to decimal?

garak

tomorow i give you an example, because i'm @home an not in my office.

Waldo

Does this help?

WHILE NOT lADORecordset.EOF DO BEGIN 
  ldlgWindow.UPDATE(2,lADORecordset.Fields.Item('CompanyName').Value); 

  rectmpMyTempRecord.INIT; 
  rectmpMyTempRecord."Item No" := lADORecordset.Fields.Item('ItemNo').Value; 
  rectmpMyTempRecord."Company Name" := lADORecordset.Fields.Item('CompanyName').Value; 
  rectmpMyTempRecord."Location Code" := lADORecordset.Fields.Item('LocationCode').Value; 
  rectmpMyTempRecord."Location Name" := lADORecordset.Fields.Item('LocationName').Value; 
  rectmpMyTempRecord."Main Location" := lADORecordset.Fields.Item('MainLocation').Value;
  EVALUATE(ldecResult,FORMAT(lADORecordset.Fields.Item('Inventory').Value)); 
  rectmpMyTempRecord.Inventory := ldecResult; 
  EVALUATE(ldecResult,FORMAT(lADORecordset.Fields.Item('QtyOnPurchaseOrder').Value)); 
  rectmpMyTempRecord."Qty. On Purchase Order" := ldecResult; 
  EVALUATE(ldecResult,FORMAT(lADORecordset.Fields.Item('QtyOnSalesOrder').Value)); 
  rectmpMyTempRecord."Qty. On Sales Order" := ldecResult; 
  EVALUATE(ldecResult,FORMAT(lADORecordset.Fields.Item('PhysicalReservedQty').Value)); 
  rectmpMyTempRecord."Physical Reserved Qty." := ldecResult; 
  rectmpMyTempRecord.INSERT; 

  lADORecordset.MoveNext; 
END;

This works for me

Waldo

By the way, I wrote a blog about ADO once. Might be interesting:
- my blog
- mibuso

girish.joshi

Joshua, I have seen this error before, and there is a workaround.

Its a little strange, but you first have to write the variant out to an outstreem, and then read the data back in as text via an instream.

Once you do that, you can evaluate it to a decimal.

I'll look around for some code samples, but you can definitely find detailed posts about this on mibuso and on dynamics users group (I think I posted about it a year or so ago)

garak

Waldo was a little bit faster then i ;-)

Waldo

sorry ...

garak

no waldo, i'*m not amused [-X =P~

JoshuaSaves

I tried the code example from Waldo but received the same error. I am going to try the InStream / OutStream method as suggested above and see if that works. :roll:

JoshuaSaves

All that I am getting out of the stream is: I am also noticing that any time a Decimal value is sent into a Varient, the debugger shows "Value" as value. I have checked the data and the field does have a valid number in it. ](*,)

Please ignore any of my custom functions...

SQLStr := 'Select * From ' +  SQLIntegrationTable."SQL Table Name";
adoRS := SQLConn.Execute(SQLStr);
IF NOT adoRS.EOF THEN BEGIN
  RecRef.OPEN(SQLIntegrationTable."NAV Table No.");

  adoRS.MoveFirst;
  REPEAT
    
    FOR i := 1 TO adoRS.Fields.Count DO BEGIN

      IF NOT IsNull(adoRS.Fields.Item(i-1)) THEN BEGIN
        SQLFieldName := adoRS.Fields.Item(i-1).Name;
        IF GetMapping(SQLFieldName) > 0 THEN BEGIN
          GetFieldIndex(SQLIntegrationTable."NAV Table No.",SQLIntegrationFldMapping."NAV Field No.");
          IF FldIndex > 0 THEN BEGIN
            vFieldVal := adoRS.Fields.Item(i-1).Value;
            FldRef :=RecRef.FIELDINDEX(FldIndex);
            IF SQLIntegrationFldMapping."Nav Field Type" = SQLIntegrationFldMapping."Nav Field Type"::"Text/Code" THEN BEGIN
              ValueString := FORMAT(adoRS.Fields.Item(i-1).Value);
              UpdateFieldValue(FldRef,ValueString);
            END ELSE BEGIN
              IF ISCLEAR(adoStream) THEN
                CREATE(adoStream)
              ELSE BEGIN
                CLEAR(adoStream);
                CREATE(adoStream);
              END;
              adoStream.Open;
              adoStream.WriteText(adoRS.Fields.Item(i-1).Value);
              adoStream.Position := 0;
              //IF EVALUATE(ValueString,adoStream.ReadText) THEN
              ValueString := FORMAT(adoStream.ReadText);
                UpdateFieldValue(FldRef,ValueString);
              adoStream.Close;
            END;
          END;
        END;
      END;
    END;
    
    GetNextInboundDocNo(SQLIntegrationTable."NAV Table No.");
    FldRef := RecRef.FIELDINDEX(FldIndex);
    FldRef.VALUE := NextInboundDocNo;
    RecRef.INSERT;
    COMMIT;

    adoRS.MoveNext;
  UNTIL adoRS.EOF

girish.joshi

Try doing something like this:

adoStream.WriteText(adoRS.Fields.Item(i-1).Value); 
adoStream.Position := 0; 
EVALUATE(ValueString,adoStream.ReadText) ; //I have taken out the if statement because the evaluate should behave like an assertion
//Parse Exponential that was returned

Exponent := 0 ;  //Exponent and Number are integers
IF STRPOS(mytext,'E') <> 0 THEN BEGIN  //if contains the 'exponent'
  EVALUATE(Number, COPYSTR(mytext,1, STRPOS(mytext,'E') - 1 ) );
  EVALUATE(Exponent, COPYSTR(mytext,STRPOS(mytext,'E') + 1) ) ;
END ELSE
  EVALUATE(Number,COPYSTR(mytext,1) );

IF Exponent <> 0 THEN
  UpdateFieldValue( FldRef, Number * POWER(10, Exponent)  )
ELSE
  UpdateFieldValue( FldRef, Number ) ;

JoshuaSaves

The trick appears to be that NAV cannot support the Decimal data type from SQL...at least when using ADO. NAV does support the Float data type and so I created a new SQL table with one column and one row. The SQL table's only field is a Float data type. I just assign the value of the decimal into the float without ever touching NAV. Once the value is in the float field, it can be converted. I hope that this will help save someone else time in the future! \:D/

adoRS2.Fields.Item(1).Value := adoRS.Fields.Item(i-1).Value;

//where adoRS2 represents a SQL staging table with 1 row.
//although the value of the row is changed, no update is actually performed.

[/code]