Call Stored Procedure from Dynamics Navision (Automation Variable)

 

IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection,FALSE,TRUE);

ADOConnection.ConnectionString:=
‘Driver={SQL Server};’
+ ‘Server=’+’SERVER NAME’+’;’
+ ‘Database=’+’DATABASE NAME’+’;’
+ ‘Uid=’+’SQLUID’+’;’
+ ‘Pwd=’+’SQLPWD’+’;’;

ADOConnection.Open;

IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand,FALSE,TRUE);

VarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := VarActiveConnection;
ADOCommand.CommandText := ‘ProcedureName’;
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;

ADOParameter:=ADOCommand.CreateParameter(‘@EventCode’,3,1,3,eventcode);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P1’,3,1,3,0);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P2’,3,1,3,0);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P3’,3,1,3,0);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P4’,3,1,3,WebUserId);
ADOCommand.Parameters.Append(ADOParameter);
ADOParameter:=ADOCommand.CreateParameter(‘@P5’,3,1,3,NoOfOrder);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P6’,200,1,50,TriggerModule);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P7’,200,1,50,TriggerSource);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P8’,200,1,50,ActionUser);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@p9’,200,1,50,OrderNo);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P10’,200,1,50,Custno);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P11’,200,1,100,”);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P12’,200,1,100,”);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P13’,200,1,100,”);
ADOCommand.Parameters.Append(ADOParameter);
ADOCommand.Execute;
ADOConnection.Close;
CLEAR(ADOConnection);
ADOCommand  – Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Command
ADOParameter – Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Parameter
VarActiveConnection – Variant
ADOConnection Automation –  Microsoft ActiveX Data Objects 2.8 Library’.Connection

Advertisements

4 comments on “Call Stored Procedure from Dynamics Navision (Automation Variable)

  1. hi i have tried above code in NAV2013 but i got “Multi step oledb error on “ADOCommand.Execute;” line. Please tell me how to resolve that error.

  2. Hey Vidhi,

    The “multistep OLEDB” error is usually caused by wrong parameters, something like passing a string to a parameter expecting a numeric value, or by specifying a wrong value for length. So please check this is not the case.

    If still this Error exists and you need to pass numeric Parameter then please add the below piece of code

    ADOParameter.Precision := 10;
    ADOParameter.NumericScale := 0;

    Add this code after

    ADOParameter:=ADOCommand.CreateParameter(‘@OrderID’,200,1,50,OrderNo);

  3. Hi ,
    Am not clear with the driver and other parameter . could you please explain with sample example. am getting an error while save this code. says Driver is unknown variable.. Please assist me. Thanks in Advance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s