Today, I try to create simple testing to call store procedure from navision using ADO. In fact, we can do a lot of thing by using ADO and store procedure in SQL. This testing only for sample ( I use Cronus Company for testing). This testing will show salesperson name after we send salesperson code parameter to the store procedure.
1. Create Store Procedure in SQL
CREATE PROCEDURE SP_Test @SName as Varchar(20) OUTPUT
AS
SELECT [CRONUS International Ltd_$Salesperson_Purchaser].Name as Nama from
[CRONUS International Ltd_$Salesperson_Purchaser] where
[CRONUS International Ltd_$Salesperson_Purchaser].Code = @SName
GO
2. Call Store Procedure from Navision
Variables:
ADOConnection : 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ADOCommand : 'Microsoft ActiveX Data Objects 2.8 Library'.Command
ADOParameter : 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter
ADORecSet : 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
VarActiveConnection : Variant
IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection);
ADOConnection.ConnectionString:=
'Driver={SQL Server};'
+ 'Server='+'cronus'+';'
+ 'Database='+'cronus'+';'
+ 'Uid='+'sa'+';'
+ 'Pwd='+'sa'+';';
ADOConnection.Open;
IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand);
VarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := VarActiveConnection;
// CommandType property
ADOCommand.CommandText := 'SP_Test';
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;
//CreateParameter method
ADOParameter:=ADOCommand.CreateParameter('@SalesCode', 200, 1, 30,'AH');
ADOCommand.Parameters.Append(ADOParameter);
ADOCommand.Execute;
IF ISCLEAR(ADORecSet) THEN
CREATE(ADORecSet);
ADORecSet.ActiveConnection := VarActiveConnection;
ADORecSet.Open(ADOCommand);
WHILE NOT ADORecSet.EOF DO BEGIN
MESSAGE(FORMAT(ADORecSet.Fields.Item('Nama').Value));
ADORecSet.MoveNext;
END;
ADOConnection.Close;
CLEAR(ADOConnection);
Thursday, November 5, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment