Problem with ADO.Command.ActiveConnection:=aConnection

jesam

I have the following code in a PROCEDURE (Navision Attain 3.10) :

CREATE(aConnection);

aConnection.ConnectionString := LBGen.GetConnectionString;
aConnection.Open;

IF aConnection.State = 1 THEN BEGIN // adStateOpen = 1
CREATE(aCommand);

aCommand.ActiveConnection := aConnection;
aCommand.CommandType := 4; //adCmdStoredProc
aCommand.CommandText := 'INVInsItemLedgerEntrySummary';
aCommand.Execute;
END ELSE BEGIN
// Connection is not Open.
END;

This is the definition of the variables :
aConnection Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Connection
aCommand Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Command

The problem is that the compiler gives an error on the line :
aCommand.ActiveConnection := aConnection;

The error says : Type conversion is not possible because 1 of the operator contains an invalid type.

Integer := Automation

Can anybody explain what I have done wrong ?

jesam

I have found why the Compiler complains, but I do not know how to fix it, so help is still welcome.

When looking at the aCommand variable through C/AL symbols, it show the following definition :

[_CONNECTION ActiveConnection :=] aCommand.ActiveConnection([BOOL ActiveConnection])

This is the root of the problem, for some reason Navision thinks that the ActiveConnection property needs to take a Bool, when in VB it clearly can accept an object of type Connection.
Does anybody know what the remedy is ?

jesam

Aparrantly MS realised that there was a problem and in ADO 2.8, the Command Object has a lot of additional methods and properties so I was able to rewrite the code :

CREATE(aCommand);

aCommand.ConnectionString := LBGen.GetConnectionString;
aCommand.Open;

IF aCommand.State = 1 THEN BEGIN //Open
  aCommand.Execute('exec INVInsItemLedgerEntrySummary ' + '''' + myStockFilter + '''');
END ELSE BEGIN
  // Connection is not Open.
END;

CLEAR(aCommand);

I still find it strange that there is not a single reference to find about this on the internet. I can't believe that we are the only ones that want to fire a stored procedure from Navision ...

sbhamidi

I'm trying to insert data from Navision into MS Access. I'm using the following code:

// DataConn is an Automation datatype and is a 'Microsoft ActiveX Data Objects 2.7  Library'.Connection
// DataRSet is an Automation datatype and is a 'Microsoft ActiveX Data Objects 2.7 Library'.Recordset
 
CREATE(DataConn);  
DataConn.ConnectionString := 'DBQ=F:\247 Deliverable\Testdb.mdb;DRIVER={Microsoft Access Driver (*.mdb)}';
CREATE(DataRSet);
DataConn.Open;
Window.UPDATE(1,'Customer');
strSQL := 'INSERT INTO Customer_Service_Level(Customer_No_,Service_Level_Code,Default,Service_Level_Description) ';
strSQL := strSQL + 'VALUES("2464212230","2","-1","SLOW")';
RecordsAffected := '';
Options := 0;
DataRSet := DataConn.Execute(strSQL,RecordsAffected,Options);

When I try to run this test, I get the error:

"The call to member Execute failed. Microsoft OLE DB Provider for ODBC Drivers returned the following message:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4."

I was doing a simple DataConn.Execute at first, but now used the empty variables RecordsAffected and Options as parameters to overcome this error. But still the error persists.
I would appreciate it if anyone could point me in the right direction.
TIA

mans.akerlund

sbhamidi:

I think the problem is with the character " surrounding your values, you need to use two consecutives of the character ' instead, like:

 'VALUES(' '2464212230' ',' '2' ',' '-1' ',' 'SLOW' ')';

(the spaces between the ' are shown only so that my example makes some sense and should be deleted in actual code)

Please note that if you are posting to a number field in Access you do not need the delimiters at all for example:

'VALUES(2464212230,2,-1,' 'SLOW' ')';

sbhamidi

That did it!! Thanks a bunch mans.akerlaund!!

aleksb

This problem can be solved if you declared the variable var_aConnection as variant

var_aConnection:= aConnection;
aCommand.ActiveConnection := var_aConnection;
aCommand.CommandType := 4; //adCmdStoredProc
aCommand.CommandText := 'INVInsItemLedgerEntrySummary';
aCommand.Execute;

By this way program will be compiling and will be work !!!