Automation variable has not been instantiated

rsaritzky

Hello all:

I'm having a problem getting an ADO access of an external database to work. The error is:
"This message is for C/AL programmers: This Automation variable has not been instantiated. You can instantiate it by either creating or assigning it"

My NAV is a 3.60 database running under the 5.0SP1 NAV client (SQL).
I've built a test form that calls a function in a codeunit. The button on the test form executes the following:

TestCodeUnit.CheckForWorkOrder(InputField)


In my Codeunit "TestCodeUnit", the function "CheckForWorkOrder" is coded as follows. Watching the debugger, the error appears when the ADORECORDSET.OPEN command is executed:

ADOError := FALSE;
CLEAR(ADOConnection);
IF NOT CREATE(ADOConnection) THEN BEGIN
MESSAGE('Cannot connect to Work Order Database right now - problem in Codeunit 50003 creating ADO Connection automation variable.');
ADOError := TRUE;
END;

IF ADOError = FALSE THEN BEGIN

ADOConnection.ConnectionString('Provider=SQLOLEDB;' +
'Integrated Security=SSPI;' +
'Data Source=MyDatabaseName;' +
'Initial Catalog=MyCatalog;') +
'User ID=NavAppReadOnly;Password=xxxxx;');

ADOConnection.Open;

SQLString[1] := 'SELECT case_number from casetable ';
SQLString[1] += 'WHERE case_number = ''' + COPYSTR(PWorkOrderNo,1,10) + '''' ;
OpenMethod := 0;
LockMethod := 1;
ADORecSet.Open(SQLString[1],ADOConnection,OpenMethod,LockMethod); // <---Error occurs here

IF NOT ADORecSet.EOF THEN BEGIN
....<code to display info is here>....
END;


The codeunit is NOT SingleInstance.

Globals in the codeunit:
Name DataType Subtype Length
ADOConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ADORecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
ADOError Boolean

Locals in the function "CheckForWorkOrder":
Name DataType Subtype Length
SQLString Text 1000
OpenMethod Integer
LockMethod Integer

Parameters in the function:

Var Name DataType Subtype Length
No PWorkOrderNo Code 20

I've tried moving all the variables to locals in the function, also moving all the variables to Globals in the function.

I don't have much experience using ADO, and haven't been able to find any references on how to "instantiate" an Automation variable.

Any suggestions?

Thanks

Ron

KYDutchie

Hi Ron,

to instantiate an automation variable is to Create it

here is some code I wrote, maybe this will help you:

IF ISCLEAR(tsAdoConnection) THEN CREATE(tsAdoConnection);
getConnectionString;

tsAdoConnection.ConnectionString(tsConnectionString);
tsAdoConnection.Open;

tsCompanyName := tsCompanyInfo.Name;

IF ISCLEAR(adocommand) THEN
     CREATE(adocommand);

OpenConnection := tsAdoConnection;

adocommand.ActiveConnection := OpenConnection;


adocommand.CommandText := 'Select * FROM [dbo].[Table]' +
                          'Where [dbo].[table].[Company Name] = ' + SQ + tsCompanyName + SQ +
                          '  And [dbo].[table].[No] = ' + SQ + tsJobNo_Param + SQ ;
adocommand.CommandType := 1;
adocommand.CommandTimeout := 0;

adocommand.Execute;

IF ISCLEAR(tsAdoRecordSet) THEN
     CREATE(tsAdoRecordSet);

tsAdoRecordSet.ActiveConnection := OpenConnection;
tsAdoRecordSet.Open(adocommand);
CLEAR(tsTotalLines);
CLEAR(tsLineCount);
WHILE NOT tsAdoRecordSet.EOF DO BEGIN

I hope this helps you out,

Regards,

Willy

matttrax

You need to do a CREATE, just like you do here

IF NOT CREATE(ADOConnection) THEN BEGIN

but with the other Automation variable.

rsaritzky

matttrax wrote:

You need to do a CREATE, just like you do here

IF NOT CREATE(ADOConnection) THEN BEGIN

but with the other Automation variable.

So you're saying I have to do:

CREATE(ADORecordSet)

? (see my code above)

I didn't think an automation variable of type Recordset had to be CREATE'd - but my knowledge is limited - just looked at examples here on MIBUSO and viewed some other sample code.

But I'll definitely try it - thanks!

Ron

matttrax

My experience with automation is limited, but I thought you always had to create an instance of it.

rsaritzky

Well it was as simple as that - I just added a CREATE(ADORecSet) line and that took care of the error. Thanks to Mattrax and KYDutchie for responding!

Ron

colingbradley

I have stopped getting the error message, I re-installed the variable and although the object did not need saving, when run now, no error.

I have read the thread carefully and I think my code is solid but I am getting the same error message.
I need to check the status of the Job Queue entries and Reset them if they have stalled.
Any ideas most welcome.

OnRun()
IF ISCLEAR(autNavisionTimer) THEN BEGIN
CREATE(autNavisionTimer);
autNavisionTimer.Interval(JobQueueSetup."Check Every N Seconds" * 1000);
autNavisionTimer.Enabled(TRUE);
END;

ta5

Where does the error occur?
Thomas

colingbradley

I don't get it now.
I found an answer elsewhere.
I re-inserted the timer variable and that sorted the problem.
:whistle: