Hi Every one,
I have read all your contributions and your profile and thanks for good work you are doing is it help debutants like me especially in Dynamics GP.
Specifically,I ave read your article entitled Calling stored procedure from Dexterity and allow me to seek your assistance in the following queries.
Am New in dex and I have been trying to compile prototype procedure from dex in order to populate table fields but am finding an error that says "cannot find script 'name of stored procedure' ".This error is on Dex side not the Company Database side.the stored procedure in the DB is fine.
What could be the problem when I try to compile the prototype procedure codes bearing in mind that it has the same name as stored procedure in the database.
Secondly is it possible to add data back to sql server databse using sql insert stored procedure from a dex form?.
what about update and delete stored procedures can they be invoked by a click of a push button from dex form?.
Can you post your sql procedure name and your prototype code here?
In reply to almas:
Is the name of the store procedure and name of the dex procedure which call stored procedure using 'call sproc' are same.
Thanks for reply as i have been waiting for this.
Below are my prototype procedure and sql stored procedure.
1.stored procedure "profile"
/****** Object: StoredProcedure [dbo].[Profile] Script Date: 07/30/2012 11:36:41 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[Profile]
@zkItemNumber char(10) = null,
@zkItemDesc char(30) output,
@zkCostPrice numeric(19,5) output,
@zkSellingPrice numeric(19,5) output,
@zkStatus smallint output
SELECT zkItemNumber,zkItemDesc, zkCostPrice, zkSellingPrice,zkStatus
WHERE zkItemNumber = @zkItemNumber
GRANT EXECUTE ON Profile TO DYNGRP
2.Prototype procedure “profile”
sproc returns long Sproc_Status;
in string zkItemNumber;
inout string zkItemDesc;
inout currency zkCostPrice;
inout currency zkSellingPrice;
inout integer zkItemStatus;
local long l_ReturnCode, timedelay;
local integer i, n_loopcount;
call sproc "Profile", Sproc_Status, zkItemNumber, zkItemDesc, zkCostPrice, zkSellingPrice,zkItemStatus;
if i <= n_loopcount then
timedelay = Timer_Sleep(200);
set Sproc_Status to l_ReturnCode;
3.Push button codes (to retrieve information from sql database)
local long Sproc_Status;
zkItemNumber = zkItemNumber of window zkInvWin;
zkItemDesc = zkItemDesc of window zkInvWin;
zkCostPrice =zkCostPrice of window zkInvWin;
zkSellingPrice = zkSellingPrice of window zkInvWin;
zkItemStatus = zkItemStatus of window zkInvWin;
if empty(zkItemNumber) then
warning "No name has been entered.";
call Profile, Sproc_Status,zkItemNumber,zkItemDesc, zkCostPrice,zkSellingPrice,zkItemStatus;
In reply to KayKay:
call sproc "profile", Sproc_Status, zkItemNumber, zkItemDesc, zkCostPrice, zkSellingPrice,zkItemStatus;
You called profile with capital P.
similarly on button code it should be call profile.
I had used Profile with Capital P in stored procedure,prototype procedure and push buttons codes.
Again in Dex environment I create a procedure Called Profile and i pasted thnull,null,null,null,null,null]}; WibiyaLoads=35; __utma=17rors or warnings but the fields on the form cannot display data from sql server database.
They are still blank.
if i want to display the data on the forms text fields when a push button is clicked.Or i fill data on the scroll window when a push button is clicked,what do i need to do on codes inside a push button.
Now after you have make same name of your sql procedure,dexterity prototype and button code where you are calling stored procedure (sp). Also do the following.
1. You are not returning any value in your stored procedure to dexterity although you created output parameters.
correct it like that
alter PROCEDURE [dbo].[Profile] @zkItemNumber char(10)=null , @zkItemDesc char(30) output, @zkCostPrice numeric(19,5) output, @zkSellingPrice numeric(19,5) output, @zkStatus smallint output AS SELECT @zkItemDesc=zkItemDesc, @zkCostPrice=zkCostPrice, @zkSellingPrice=zkSellingPrice,@zkStatus=zkStatus FROM zkInv WHERE zkItemNumber = @zkItemNumber
GRANT EXECUTE ON Profile TO DYNGRP
2. In a form where you are calling this sp
local long Sproc_Status;local string t1,t2;local 'Line Item Sequence' s1,s2;local integer i;call profile, Sproc_Status,t1,t2,s1,s2,i;
now set forms field with these output values
zkItemDesc of window zkInvWin=t2;
zkCostPrice of window zkInvWin=s1;
zkSellingPrice of window zkInvWin=s2;
zkItemStatus of window zkInvWin=i;
it depends from where you are reading data, from table or from stored procedure.
Scrolling window is totally different thing than a fields filling on form. :)
i want to read the data from sql database using the stored procedure i showed so that the data can be displayed on the dex form.
Let me try this and i will get back to you.
Also,is it possible to add data back to sql database from dex using an insert stored procedure.
Have you ever used Analytical accounting.Do you use srss aa reports.
waiting, let me know if your issue got solved or not.
yes it's possible to add data back to database from dex, what exactly you are looking for AA. let me know in detail, you can also mail me.
Thanks a lot for your solid support in dex and now my form fields are now populated with data.
In dex environment there is a scroll window that looks like a grid view in .dot net environment where you can drag it from tool box and mount it on form.
Now my question is is it possible to populate this scroll window using the same stored procedure such that when I click a a push button or I Load the form the scroll window is filled(populated with the data).
Secondly stored procedures of (update,delete,insert,create),can they be invoked through dex application the way we do in select stored procedure or its only select query stored procedure works.
glad to know that.
1. You don't need to use procedure to fill the scrolling window.if your table is create from dex you can use dex code to fill the window.
2. We can do all from SP, select,insert,delete and Update.
but doing thing efficiently means to do in a way which should be simple and effective.
talk to you later,
Since you have said that insert,update and delete stored procedures can work the same way select statement stored procedure works,can i use a delete stored procedure instead of dex delete codes.Also can i use update stored procedure instead of dex "Save record" codes.And also the same case to insert.
Thanks very much.