Referencing retrieved external SQL fields in report

CobaltSS
CobaltSS Member Posts: 137
edited 2009-12-03 in NAV/Navision Classic Client
Hi all,

I've created an SQL connection from our NAV (SQL) database to an external database stored on a different server.

I would like to display the returned results in a report. How do I reference the data fom my recordset?
Here's the code I'm using: 
  ADOConnection.Open;
  IF CREATE(ADORecSet) THEN BEGIN
    SQLString[1] := 'SELECT DisplayName, Location from  dbo.DOCUMENTS_PEOPLE';
    OpenMethod := 0;
    LockMethod := 1;
    ADORecSet.Open(SQLString[1],ADOConnection,OpenMethod,LockMethod);
  END;

  IF NOT ADORecSet.EOF THEN BEGIN
    IF ADORecSet.BOF THEN;
    WHILE NOT ADORecSet.EOF DO BEGIN
      prnName := ADORecSet.DisplayName;

      ADORecSet.MoveNext;
    END;
and I'd like to print "DisplayName" and "Location" on the report. But prnName := ADORecSet.DisplayName; compiles with an error.

Any help greatly appreciated,
cheers,

Comments

  • CobaltSS
    CobaltSS Member Posts: 137
    HI all,

    I've found an answer: 
      IF NOT ADORecSet.EOF THEN BEGIN
    ADORecSet.MoveFirst;
    WHILE NOT ADORecSet.EOF DO BEGIN
      prnName := ADORecSet.Fields().Item('DisplayName').Value;
      ADORecSet.MoveNext;
    END;
  END;

    Thanks for any additional pointers anyone may provide.

    cheers,
  • kapamarou
    kapamarou Member Posts: 1,152
    You'd have to add an Integer dataitem and after each integer record move to the next item in you set.
  • CobaltSS
    CobaltSS Member Posts: 137
    HI again,

    I'm experiencing some trouble with the datatypes. DisplayName is nvarchar(150), and Location is nvarchar(50). When I try to reference them via: 
      IF NOT ADORecSet.EOF THEN BEGIN
    ADORecSet.MoveFirst;
    WHILE NOT ADORecSet.EOF DO BEGIN
      prnName := ADORecSet.Fields().Item('DisplayName').Value;
      prnLocation := ADORecSet.Fields().Item('Location').Value;
      ADORecSet.MoveNext;
    END;
    I receive the message :
    The data type is not supported by C/SIDE. You can access data from 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.

    Again, any help greatly appreciated.

    cheers,
  • kapamarou
    kapamarou Member Posts: 1,152
    Have you read this?
    http://dynamicsuser.net/blogs/waldo/arc ... -side.aspx
  • CobaltSS
    CobaltSS Member Posts: 137
    Hi,

    I just finished reading the article. But I do not want to create a stored procedure, just read two TEXT variables. Additionally, I don't think the CAST function shouldn't apply here, as the variables I'm trying to extract are just TEXT. I hope I'm not being too thick here on a Monday morning.....

    cheers,
  • CobaltSS
    CobaltSS Member Posts: 137
    Hi all,

    Sorry to bring this back up to the top.

    I've two challenges with my code that I would appreciate some help with.

    This code will not compile. The error is "Type conversion is not possible because 1 of the operators contains an invalid type. Variant <> Integer" 
          IF ADORecSet.Fields().Item('DisplayName').ActualSize <> 0 THEN
        prnName := ADORecSet.Fields().Item('DisplayName').Value;

    When I comment out the "IF" part of the line above, I can compile. But when I try to run I get the following:
    The data type is not supported by C/SIDE. You can access data from 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.

    Here is my updated code using the CAST function: 
      ADOConnection.Open;
  IF CREATE(ADORecSet) THEN BEGIN
    SQLString[1] := 'SELECT CAST(DisplayName AS Text) AS DisplayName, CAST(Location AS Text) AS Location from  DOCUMENTS_PEOPLE';
    OpenMethod := 0;
    LockMethod := 1;
    ADORecSet.Open(SQLString[1],ADOConnection);
  END;

    As always, any help greatly appreciated.

    cheers,

Categories