Running ADO through webservice call

FommoFommo Member Posts: 138
edited 2011-10-14 in NAV Three Tier
Hi

I have some old integration code that have worked perfectly fine in previous versions, but now in 2009 we get problems when running through webservice. The main idea is that we would like to run without NAS and be able to call the integration code through VS so that the peripheral system can get the control over when the integration is run.

BUT, the following ADO code gives us problem:
EstablishDBConnectionWRefresh(pRefreshConnection);

IF NOT ISCLEAR(ADOCommand) THEN  // Recreate Command object
  CLEAR(ADOCommand);
CREATE(ADOCommand);

ADOConnStatus := MFLADOConnection.State;
ADOConnString :=  MFLADOConnection.ConnectionString;

MFLADOConnVariant := MFLADOConnection;
ADOCommand.ActiveConnection := MFLADOConnVariant;    //THIS LINE BLOWS

ADOCommand.CommandText := pMainCommand + pArguments;
ADOCommand.CommandType := StrToInt(pCmdType);
ADOCommand.CommandTimeout := 0;

CREATE(tmpADORecordSet);
tmpADORecordSet.Open(ADOCommand);
Note that this code works perfectly fine with NAS and has worked for years now, but when calling this code through a webservice call it blows on the marked (commented) line. I get the failure "The expression Variant cannot be type-converted to string".

How come? Is it possible to avoid the somewhat crazy assign (the variant conversions) or is it possible to solve this situation. I guess that the variant conversion works with NAS as NAS is running more close to the NAV core and webservice is run on the service tier on a higher layer, right? Is it possible to write the code without using VARIANT variables? ](*,)

Please help
/Simon

Comments

  • ara3nara3n Member Posts: 9,256
    Have you tried

    ADOCommand.ActiveConnection := MFLADOConnection;




    Also I don't see in your code making the connection.
    MFLADOConnection.ConnectionString := 'sql blah blah';
    MFLADOConnection.Open;


    I'm guessing you just omitted it from the post.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • FommoFommo Member Posts: 138
    ara3n wrote:
    Have you tried
    ADOCommand.ActiveConnection := MFLADOConnection;
    Yes, I've tried that and it gives me an error saying that
    Type conversion is not possible because 1 of the operators contains an invalid type.
    Integer := Automation
    
    Which is quite right. It is an automation. It's irritating though that I have to go through a variant to assign it.
    ara3n wrote:
    Also I don't see in your code making the connection.
    MFLADOConnection.ConnectionString := 'sql blah blah';
    MFLADOConnection.Open;

    I'm guessing you just omitted it from the post.
    Yeah, you're right. I do the preparation of the connection objekt in another function trigger. It works running through NAS so I guess it should be OK.
  • ara3nara3n Member Posts: 9,256
    Your code should work and I suggest to test it in 2009 sp1 and if you still get the error, I suggest to contact MS.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • sitrasitra Member Posts: 8
    Did you find a solution ?

    We have the same problem. If we try the codeunit who uses ADO in a form no problem if we use it as webservice like

    textBox2.Text= NAVWebService.LastlocationTractor(textBox1.Text);

    we receive an error

    System.Web.Services.Protocols.SoapException was unhandled
    Message=The expression Variant cannot be type-converted to a String value.
    Source=System.Web.Services
    Actor=""
    Lang=nl-BE
  • ara3nara3n Member Posts: 9,256
    lol I ran into the same issue. :) So I posted the solution.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Nitetiger1Nitetiger1 Member Posts: 4
    I searched around and I have found the solution for those that cannot upgrade to 2009 R2 for some reason:
    gtxtQuery :=  //*** Some Query String ***//
    CREATE(gautADOConn);
    CREATE(gautADORecordSet);
    
    gautADOConn.ConnectionString := //***Some Connection String***//
    gautADOConn.Open;
    gautADORecordSet := gautADOConn.Execute(gtxtQuery);
    
    WHILE NOT gautADORecordSet.EOF DO BEGIN
    //*** Some record processing ***
      gautADORecordSet.MoveNext;
    
    gautADOConn.Close;
    

    This works in both Classic and RTC
Sign In or Register to comment.