Referencing retrieved external SQL fields in report

CobaltSS

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,

CobaltSS

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

You'd have to add an Integer dataitem and after each integer record move to the next item in you set.

CobaltSS

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

Have you read this?
http://dynamicsuser.net/blogs/waldo/arc ... -side.aspx

CobaltSS

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

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,