Dex

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?. thank you

  • Dear Kay,

    Can you post your sql procedure name and your prototype code here?

    almas

  • In reply to almas:

    Kay,

    Is the name of the store procedure and name of the dex procedure which call stored procedure using 'call sproc' are same.

    Arunprasath.

  • In reply to almas:

    Dear Almas,

    Thanks for reply as i have been waiting for this.

    Below are my prototype procedure and sql stored procedure.

    1.stored procedure "profile"

     

    USE [LVCT]

    GO

    /****** Object:  StoredProcedure [dbo].[Profile]    Script Date: 07/30/2012 11:36:41 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    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 zkItemNumber,zkItemDesc, zkCostPrice, zkSellingPrice,zkStatus

    FROM zkInv

    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;

     

    try

     

    call sproc "Profile", Sproc_Status, zkItemNumber, zkItemDesc, zkCostPrice, zkSellingPrice,zkItemStatus;

     

    catch [EXCEPTION_CLASS_DB_DEADLOCK]

     

    if i <= n_loopcount then

     

    timedelay = Timer_Sleep(200);

     

    increment i;

     

    restart try;

     

    else

     

    exit try;

     

    end if;

     

    else

     

    exit try;

     

    end try;

     

    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.";

     

    else

     

    call Profile, Sproc_Status,zkItemNumber,zkItemDesc, zkCostPrice,zkSellingPrice,zkItemStatus;

     

    end if;

     

     

     

    THANKS,

    KAY

     

     

     

     

     

  • In reply to KayKay:

    call sproc "profile", Sproc_Status, zkItemNumber, zkItemDesc, zkCostPrice, zkSellingPrice,zkItemStatus;

     

    You called profile with capital PWink

    similarly on button code it should be call profile.

     

    Thanks,

    almas

  • In reply to almas:

    Almas,

    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.

    Thanks.

  • In reply to KayKay:

    Almas,

    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.

    Thanks.

  • In reply to KayKay:

    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 

     GO

    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;

     

  • In reply to KayKay:

    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. :)

  • In reply to almas:

    Hi Almas,

    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.

    Thanks.

  • In reply to almas:

     

    almas,

    Have you ever used Analytical accounting.Do you use srss aa reports.

    Thanks.

     

  • In reply to KayKay:

    waiting, let me know if your issue got solved or not.

  • In reply to KayKay:

    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.

    almas

  • In reply to almas:

    hi almas,

    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.

    Thanks.

  • In reply to KayKay:

    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,

    almas Geeked

     

  • In reply to almas:

    Almas,

    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.

Related
Recommended