1. 

   thewah, Member

   Here's the call...

   select 'dbr.editable','Lead Goal','sp_DBR_update_lead_goal_1','inLeadGoal=3','inUserID=22';

   Here's the query...

   select
   'Fantasy 1' as 'Scenario Name',
   cs.deal_count as 'Sold Deal Goal',
   cs.lead_count as 'Lead Goal',
   .....18 lines snipped....
   cs.user_id as 'UserID[user]'
   from
   ...rest of query clipped.....

   The stored proc is very simple...

   CREATE PROCEDURE sp_DBR_update_lead_goal_1(IN inLeadGoal int, in inUserID varchar(20))
   BEGIN
   update dw_auto.comm_summary
   set lead_count = inLeadGoal
   where pay_period_id = 998
   and user_id = inUserID
   ;
   END

   So for some reason I can update the table when I take off the UserID limit, but can't just update the correct record. The UserID information never seems to make it through the call.
2. 

   myDBR Team, Key Master

   The code snippet looks ok.

   To see what is going on, keep the inUserID parameter and see what is the parameter that is being passed to the update procedure. That could give you some indication what is wrong.

   You could refer to the columns with a column reference rather than the column number to make sure you've got the correct column. Instead of using 'inUserID=22' you can use 'inUserID=user'.

   --
   myDBR
3. 

   thewah, Member

   "To see what is going on, keep the inUserID parameter and see what is the parameter that is being passed to the update procedure. That could give you some indication what is wrong."

   How do I do this in a simple manner? Originally I was hiding the column...but then I stopped doing that and it seems to be pulling the right data in the query, but I can't see what is actually being passed to the stored proc.

   Does it keep a log of the actual sql passed to the server?
4. 

   thewah, Member

   Oh...and I've tried referring to the column number, the named version and the [bracket name] (both with and without brackets), nothing seems to be working.

   When I test the stored proc with actual values (instead of variables) it works fine...it's a pretty simple update.
5. 

   myDBR Team, Key Master

   How do I do this in a simple manner?

   Create a table where you insert the value.

   --
   myDBR Team
6. 

   thewah, Member

   So I'm inserting into a temp table.

   The two values it is inserting are the original field value, and the one I'm changing it to. The UserID never gets passed.

   For example:

   Current Entry is 15. I click to edit, change it to 20.

   The stored proc is passing over 15 as a number and 20 as a string.

   The UserID never figures into it.
7. 

   myDBR Team, Key Master

   Looking closer at your report code reveals the problem.

   The editing report (sp_DBR_update_lead_goal_1 in your case), should have parameters set in order where the key values are followed by the edited value. So your editing procedure should be like:

   CREATE PROCEDURE sp_DBR_update_lead_goal_1(
   inUserID varchar(20),
   inLeadGoal int
   )
   ...

   When the editing report is called from the actual report, you only need to pass the key value, myDBR automatically passes the edited value:

   select 'dbr.editable', 'Lead Goal', 'sp_DBR_update_lead_goal_1', 'inUserID=user';

   --
   myDBR Team
8. 

   thewah, Member

   That works. Thanks for the support.

   -RPN

Reply

You must log in to post.