1. 

   ajdjackson, Member

   Hi

   Me again sorry :)

   I'm trying to do some inline editing and wish to use select list but wish to filter this list based on a previous selection.

   This is where I've got to:

   CREATE PROCEDURE sp_DBR_Plots(inSiteID tinyint(3), inPhaseID tinyint(3))

   BEGIN

   select 'dbr.pager',20; select 'dbr.parameters.show';

   select 'dbr.embed_object','createx';

   select 'dbr.button', 'Enter New Plot'; select 'dbr.report', 'sp_DBR_New_Plot','createx','inSiteID=1'; select inSiteID;

   select 'dbr.editable', 'Phase', 'sp_DBR_plot_phase_edit', 'inPL_ID=ID', 'inSiteID=Site','type=select','select=select t.tblJobAnalysis_ID, t.tblJobAnalysis_JobAnalysis from hilmark.tbljobanalysis_j t where t.tblJobAnalysis_SiteID = [Site]'; select 'dbr.editable', 'Plot Type', 'sp_DBR_plot_type_edit', 'inPL_ID=ID','inPL_Type=Plot_Type'; select 'dbr.editable', 'size', 'sp_DBR_plot_size_edit', 'inPL_ID=ID','inPL_Size=size'; select 'dbr.editable', 'garage', 'sp_DBR_plot_garage_edit', 'inPL_ID=ID','inPL_Garage=garage'; select 'dbr.editable', 'sunroom', 'sp_DBR_plot_sunroom_edit', 'inPL_ID=ID','inPL_Sunroom=sunroom'; select 'dbr.editable', 'BDate', 'sp_DBR_plot_bookdate_edit', 'inPL_ID=ID','type=datepicker'; select 'dbr.editable', 'SP', 'sp_DBR_plot_expsp_edit', 'inPL_ID=ID','inPL_ExpSP=ExpSP'; select 'dbr.editable', 'HDate', 'sp_DBR_plot_hodate_edit', 'inPL_ID=ID','inPL_HOdate=Plot_ExpHOD'; select 'dbr.editable', 'Cust', 'sp_DBR_cust_edit', 'inPL_ID=ID','inPL_Cust=Customers'; select 'dbr.editable', 'Cont', 'sp_DBR_plot_contract_edit', 'inPL_ID=ID','inPL_Cont=Contracted'; select 'dbr.editable', 'ConVar', 'sp_DBR_Edit_Var_Construct', 'inPL_ID=ID','inPL_VarCon=ConVar'; select 'dbr.editable', 'CusVar', 'sp_DBR_Edit_Var_Customer', 'inPL_ID=ID','inPL_VarCus=CusVar'; select 'dbr.editable', 'FCosts', 'sp_DBR_Edit_Shell_Costs', 'inPL_ID=ID','inPL_FCosts=FCosts'; select 'dbr.editable', 'FCosts', 'sp_DBR_Edit_Site_Wide', 'inPL_ID=ID','inPL_SWide=SWide';

   select 'dbr.hidecolumn','PhaseID','Site'; select 'dbr.colstyle', 'size', '%.0f'; select 'dbr.colstyle', 'SP', '%.0f'; select 'dbr.colstyle', 'ConVar', '%.0f'; select 'dbr.colstyle', 'CusVar', '%.0f'; select 'dbr.colstyle', 'FCosts', '%.0f'; select 'dbr.colstyle', 'SWide', '%.0f';

   select t.tblContractXidJobNo as 'PlotID[ID]', u.Site_ID as 'Site[Site]', w.tblJobAnalysis_ID as 'PhaseID[PhaseID]', w.tblJobAnalysis_JobAnalysis as 'Phase[Phase]', t.Plot_Type as 'Plot Type', t.Plot_Size as 'Size, ft2[size]', t.Plot_Garage as 'Garage, Yes/No[garage]', t.Plot_SunRoom as 'SunRoom, Yes/No[sunroom]', t.Plot_BookDate as 'Booked Date, dd/mm/yyyy[BDate]', t.Plot_ExpSP as 'ExpSP[SP]', t.Plot_ExpHOD as 'Exp. HOD, dd/mm/yyyy[HDate]', t.Plot_Customers as 'Customers[Cust]', t.plot_Contract as 'Contracted, Yes/No[Cont]', t.Var_Construct as 'Construction Var[ConVar]', t.Var_Customer as 'Customer Var[CusVar]', t.Tender_FitOut as 'FitOut Costs[FCosts]', t.Site_Wide_Charge as 'SiteWide[SWide]' from hilmark.tblsitesplotsjake t inner join hilmark.tblsitesjake u on t.Site_ID = u.Site_ID LEFT OUTER JOIN hilmark.tbljobanalysis_j w on w.tblJobAnalysis_ID = t.Plot_Phase

   where t.Site_ID = InSiteID and w.tblJobAnalysis_ID = inPhaseID

   order by t.tblContractXidJobNo;

   My issue is in the following line of code: select 'dbr.editable', 'Phase', 'sp_DBR_plot_phase_edit', 'inPL_ID=ID', 'inSiteID=Site','type=select','select=select t.tblJobAnalysis_ID, t.tblJobAnalysis_JobAnalysis from hilmark.tbljobanalysis_j t where t.tblJobAnalysis_SiteID = [Site]';

   I can't seem to find a way to pass the SiteID to the where clause. I've tried using inSiteID, Site and [Site] but all give a MySQL error usually invalid column.

   I've also tried declaring a variable and setting to inSiteID but again this didn't work.

   Any thoughts on how I do this?

   Thanks

   Jake

   PS Using the Code tag seems to screw up the post?
2. 

   ajdjackson, Member

   Hi

   I've managed to sort it by using the following: select 'dbr.editable', 'Phase', 'sp_DBR_plot_phase_edit', 'inPL_ID=ID', 'inSiteID=Site','type=select','select=sp_ADBR_Select_Phase_2 [Site]';
   although I'm not sure why the first method I tried doesn't work.

   Cheers

   Jake
3. 

   myDBR Team, Key Master

   myDBR can reliably parse the stored procedure case. When using dynamic SQL there is a risk of an error in parsing, therefore the parameter substitution in dynamic SQL is not supported.

   --
   myDBR Team
4. 

   harpef, Member

   I'm getting the same problem, but the fix that Jake found doesn't work for me either. Here's my editable line...

   select 'dbr.editable', 'Assumption', 'sp_DBR_LHQ_2_0_update_assumption_id', 'inID=ID', 'type=select', 'select=sp_ADBR_select_assumptions_with_params[phaseID]';

   select r.id AS 'ID', r.project_id AS 'phaseID', etc.

   ...and here's my function:

   DROP PROCEDURE IF EXISTS sp_ADBR_select_assumptions_with_params
   $$
   CREATE PROCEDURE sp_ADBR_select_assumptions_with_params(inPhase int)
   BEGIN

   select id,name from groupofficecom.lbw_phase_assumptions where project_id = inPhase;

   END
   $$

   The frustrating thing is, the select dropdown works when I don't parameterise my sp_ADBR_select_assumptions_with_params function.

   Any suggestions?
5. 

   myDBR Team, Key Master

   Make sure you have a space between the procedure name and the parameter.

   So instead of:

   'select=sp_ADBR_select_assumptions_with_params[phaseID]'

   use

   'select=sp_ADBR_select_assumptions_with_params [phaseID]'

   --
   myDBR Team
6. 

   harpef, Member

   Hmm, sure I tried that yesterday, but done it today and it works. Go figure. Many thanks for fast response.

Reply

You must log in to post.