Running ADO through webservice call

Fommo · 2010-04-19

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

ara3n · 2010-04-19

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.

Fommo · 2010-04-19

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.

ara3n · 2010-04-19

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.

sitra · 2010-12-03

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

sitra · 2011-01-13

This could help

http://mibuso.com/blogs/ara3n/2011/01/1 ... tc-in-nav/

ara3n · 2011-01-13

lol I ran into the same issue.

So I posted the solution.

Nitetiger1 · 2011-10-14

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

Running ADO through webservice call

Comments

Categories