ADO Recordset Error

jversusj

This has become a frustrating error - we have a Stored Procedure that calls an assembly to encrypt/decrypt some values.

When we execute the SP direct from SQL it works.
When we create a VB6 application to make the ADO connection and retrieve the recordset, it works.
When we try within NAV (same ADO Connection as VB6 app), however, it fails. We get two sequential errors and then the NAV client crashes with "Lost connection" error.

1.

---------------------------
Microsoft Dynamics NAV
---------------------------
This message is for C/AL programmers:The call to member Open failed. Microsoft OLE DB Provider for SQL Server returned the following message:Connection failure
---------------------------
OK   
---------------------------

2.

---------------------------
Microsoft Dynamics NAV
---------------------------
Your connection to the ABC server is no longer responding. This is probably because of one of the following reasons:* The server has been shut down, either manually or as a result of an error.* Your session has been killed by an administrator.* A network failure has occurred.* A hardware failure has occurred on the server computer or on your computer.Contact your system manager if you need assistance.
---------------------------
OK   
---------------------------

Here is the C/AL with a comment where debugger throws the error.

Name	DataType	Subtype	Length
lADOConnection	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Connection	
lADOCommand	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Command	
lADOParameter	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Parameter	
lADORecordSet2	Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Recordset	
lVarActiveConnection	Variant		
lrecILSSetup	Record	ILS Setup	
sResult	Text		250
Decrypt(EncryptedValue : Text[250];Direction : Text[30]) : Text[250]
sResult := '';

IF ISCLEAR(lADOConnection) THEN
   IF NOT CREATE(lADOConnection) THEN
      ERROR('Cannot create ADO Connection to Database. Contact IT.');

lrecILSSetup.GET;
WITH lrecILSSetup DO
   lADOConnection.ConnectionString(STRSUBSTNO(ConnectStr,Provider,"Status Data Source","Status Catalog",sUserID,Password));

lADOConnection.Open;

IF ISCLEAR(lADOCommand) THEN
   IF NOT CREATE(lADOCommand) THEN
      ERROR('Cannot create ADOCommand in connecting to Database. Contact IT.');

lVarActiveConnection         := lADOConnection;
lADOCommand.ActiveConnection := lVarActiveConnection;

 // CommandType 4 = adCmdStoredProc (evaluates to Stored Procedure)
 // http://www.devguru.com/technologies/ado/quickref/command_commandtype.html
lADOCommand.CommandText    := 'usp_Decrypt;
lADOCommand.CommandType    := 4;
lADOCommand.CommandTimeout := lrecILSSetup."ADO Timeout";

 // CreateParameter(Parameter,Type,Direction,Size,Value)
 // 200 = adVarChar (string), 135 = adDBTimeStamp (YYYYMMDDHHMMSS datetime)
 // http://www.devguru.com/Technologies/ado/quickref/command_createparameter.html

lADOParameter := lADOCommand.CreateParameter('@value', 200, 1, 250,EncryptedValue);
lADOCommand.Parameters.Append(lADOParameter);
lADOParameter := lADOCommand.CreateParameter('@direction', 200, 1, 250,Direction);
lADOCommand.Parameters.Append(lADOParameter);

lADOCommand.Execute;

 //Create record set to pull return (decrypt value)
IF ISCLEAR(lADORecordSet2) THEN
   CREATE(lADORecordSet2);
lADORecordSet2.ActiveConnection := lVarActiveConnection;
lADORecordSet2.Open('SET NOCOUNT ON');
lADORecordSet2.Open(lADOCommand);                         //  <-- ERRORS THROWN HERE

WHILE NOT lADORecordSet2.EOF DO BEGIN
   sResult := lADORecordSet2.Fields.Item('CCRESULT').Value;
   lADORecordSet2.MoveNext;
END;

lADORecordSet2.Close;
lADOConnection.Close;
CLEAR(lADOConnection);

EXIT(sResult);

If we hardcode in return results in the SP, the C/AL executes the recordset.Open without issue. If we pass an output parameter and move away from using the ADO Recordset, the C/AL executes without issue.

Does anyone see anything wrong with the above code that would throw the error I have described? We have tried to make sure the data types match.... another set of eyes looking at this would be helpful.

jversusj

We were able to get this working with a very minor code change. Not sure why this simple change solved it, since all C/AL examples we could find with ADO and recordsets were written this way.

Basically, if we comment out

lADOCommand.Execute;

the Recordset opens without error and we get our result back.

lADORecordSet.Open(lADOCommand);

I tried this because I noticed a subtle difference between my C/AL and the VB6 app our other developer threw together to test a 3rd party app calling the SP and receiving a result.
His code handled the recordset in a much simpler manner.

Set rs = cmd.Execute

I noticed that the C/AL examples always had:

ADOCommand.Execute;
//do some property setting
ADORecordSet.Open(ADOCommand);

I decided to try commenting out the explicit Execute as a test and it was successful. I received back the expected results.

Any thoughts on why I had to comment out the Command.Execute to get this to work?