NAV 2013 ADOConnection

mathieuk

Hi Guys,
Hopefully someone can help me with this.
I'm doing a NAV migration from 2009 to 2013 and there is something I can't make working.
I've got a function that's executing a stored procedure in another SQL database using the following automations:
'Microsoft ActiveX Data Objects 2.8 Library'.Connection
'Microsoft ActiveX Data Objects 2.8 Library'.Command
'Microsoft ActiveX Data Objects 2.8 Library'.Parameter

Then, I've got this code:

IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection);

When I try to save the function, it gives me this error:
"You cannot create Automation object "ADOConnection" on NAV server. You must create it on the client computer."

Can't we use automations with NAV 2013?
If not, any other way I can execute a stored procedure from NAV 2013? using DotNet variables?

Cheers

mohana_cse06

Yes, use

CREATE(ADOConnection,FALSE,TRUE);

If you want to use DotNet variable then try below blog
http://mibuso.com/blogs/ara3n/2011/01/10/using-ado-on-rtc-in-nav/

Marije_Brummel

You can also look at the upgrade toolkit that uses dotnet interop for this.

mathieuk

Thanks guys for your answers.
I'm going to use the DotNet variables as mohana_cse06 suggested.

However, do you know how to add parameters to the SQLCommand?

mathieuk

Hi guys,
I've finally found the solution.
Here is the code, it might help someone else.

L_SQLConnection := L_SQLConnection.SqlConnection(ConnectionString);
L_SQLConnection.Open;
L_SQLCommand := L_SQLCommand.SqlCommand('[dbo].[DeleteUser]',L_SQLConnection);
L_SQLCommand.CommandType := 4; // for a stored proc

L_SQLCommand.Parameters.Add('@ApplicationName','test');
L_SQLCommand.Parameters.Add('@UserName',L_Login."User Id");
L_SQLCommand.ExecuteReader();

L_SQLConnection.Close;
CLEAR(L_SQLCommand);
CLEAR(L_SQLConnection);

Variables
Name DataType Subtype Length
L_SQLConnection DotNet System.Data.SqlClient.SqlConnection.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
L_SQLCommand DotNet System.Data.SqlClient.SqlCommand.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

Cheers