Monday, September 3, 2018

How to call stored procedure from Dotnet Variable in NAV?

Process 1.

Add These Variables.

Name                 DataType                              Subtype Length

SQLConnection DotNet                              System.Data.SqlClient.SqlConnection.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

SQLCommand DotNet                              System.Data.SqlClient.SqlCommand.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

SqlParamCycleCode DotNet                      System.Data.SqlClient.SqlParameter.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

SQLDbType                 DotNet                      System.Data.DbType.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

CAL CODE :

SQLCommand := SQLCommand.SqlCommand();

SQLConnection := SQLConnection.SqlConnection();

SQLConnection := SQLConnection.SqlConnection('Server='+'172.16.18.158'+';'+ 'Database='+'DB NAME'+';'
+ 'Uid='+'userID'+';'
+ 'Pwd='+'Password'+';');

SQLConnection.Open;

SQLCommand.CommandText('Sp_UpdateWindowsAuthenticationFromNav');

SQLCommand.Connection := SQLConnection;

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();

SqlParamCycleCode.ParameterName:='@EmployeeId';

SqlParamCycleCode.DbType := SQLDbType.String;

SqlParamCycleCode.Value:=Rec.U_ID;

SQLCommand.Parameters.Add(SqlParamCycleCode);

SQLCommand.ExecuteNonQueryAsync();

SQLConnection.Close;

CLEAR(SQLCommand);

CLEAR(SQLConnection);