filter grid based on sql query by session variable issue

[sremias]

Hello!

I am having difficulty configuring the FILTER on a grid to use a session variable. I have watched the videos and followed the directions (I believe) correctly.

I have a session variable used throughout the application for CompanyId. So my goal is to filter each grid on CompanyId. When my grid is based on a sql query instead of a table or view I am getting an error when I try to run it in LIVE PREVIEW on localhost.

400 Bad Request
Unsafe Session variable syntax. Session variables cannot be created or assigned from either the Query String or POST body.

Can someone tell me what I am missing?

1) I created an argument in DEFINE ARGUMENTS called CompanyID, based it off of a session variable companyid, set default value to -1 (data type is Character, same as the session var)

2) Built the query in the SQL genie as follows:
SELECT *, 0 AS admincharge FROM manual WHERE company_id = :CompanyID AND section_id > 0

When I execute the query in the genie, it runs as expected and prompts me for CompanyID (currently set to the default -1) but I can change it to whatever test data I am using and see the desired results correctly. But once I run the module in LIVE PREVIEW, I get the error mentioned above.

It seems pretty cut and dry but I can't get it to work nor can I figure out how to troubleshoot. Any help is appreciated. I am using v11. Thanks so much.

[Steve Wood]

Re: filter grid based on sql query by session variable issue

In Define Arguments perhaps you named "session.companyid" and it should be a Session Variable just named "companyid".

[CharlesParker]

Re: filter grid based on sql query by session variable issue

I would suggest that for a local test you just name the session variable (company_id) to a known value. This will work every time. I also filter my tables on a company_id (session var) and since your testing locally and not necessarily logging in the variable of companyID is never assigned a value. This will also eliminate the necessity of an argument as well.
SO just say, company_id = 1234 (or whatever a known value is) in your where clause.

The table will filter on that, once you publish the application you can set the session variable to the argument which in my best guess is defined by a value related to a user login.

[sremias]

Re: filter grid based on sql query by session variable issue

Steve Wood, nope it appears to be set correctly. but great thought. I've attached a copy of the argument screen.

Charles Parker, I do set to the default value in OnGridInitialize if there is no value in the session variable so I thought that would cover me for testing. However, I assumed that setting the Default Value property in the Argument configuration would've taken care of that. Are you thinking that my error will go away once I publish to the web? Unfortunately, the boss handles all that and I'd like to ensure it works before published if possible.

Thanks for your responses guys. Appreciate it.

[sremias]

Re: filter grid based on sql query by session variable issue

forgot to attach the Argument config. haven't had my coffee yet, sorry!

args.jpg

Once again, here is the SQL using that arg:

SELECT *, 0 AS admincharge
FROM manual
WHERE company_id = :CompanyID AND section_id > 0

[Steve Wood]

Re: filter grid based on sql query by session variable issue

Can you upload the component here for review. I tried several alternatives and only got your error if I added "session." to the variable name and made it a Page Variable.

[sremias]

Re: filter grid based on sql query by session variable issue

Can you upload the component here for review. I tried several alternatives and only got your error if I added "session." to the variable name and made it a Page Variable.

Let me know if the component is attached. I'm still a newbie to this forum. I'm not sure it worked. Thanks so much for your willingness to help. I'm sure it's something simple!!!!

[Steve Wood]

Re: filter grid based on sql query by session variable issue

Remove the Argument completely and change your query to:

SELECT *, 0 AS admincharge
FROM manual
WHERE company_id = session.companyid and section_id > 0

Then add this to the onGridInitialize to keep it from giving an error on LivePreview:

dim session.company_id as c

----

Also you are setting your character-based session.company_id to a numeric (-1) in your onGridExecute

-----------

And also, here is an easier-to-read way to lay out your sql query:

sql = <<%txt%
INSERT INTO training_procedure_revision_employee (procrev_id,emp_id,job_type)
SELECT :procrevid, employee_id, job_position FROM train1
INNER JOIN training_matrix ON job_position = training_matrix.job_type
INNER JOIN training_matrix_procedures ON training_matrix_id = matrix_id
INNER JOIN manual ON procedure_section_id = section_id
WHERE section_id = :sectionid
%txt%

Yours is like this:
sql = "Insert into training_procedure_revision_employee (procrev_id,emp_id,job_type) "\
"Select :procrevid, employee_id, job_position from train1 inner join training_matrix on job_position = training_matrix.job_type inner join "\
"training_matrix_procedures on training_matrix_id = matrix_id inner join manual on procedure_section_id = section_id "\
"where section_id = :sectionid"

[sremias]

Re: filter grid based on sql query by session variable issue

Remove the Argument completely and change your query to:

SELECT *, 0 AS admincharge
FROM manual
WHERE company_id = session.companyid and section_id > 0

Then add this to the onGridInitialize to keep it from giving an error on LivePreview:

dim session.company_id as c

----

Also you are setting your character-based session.company_id to a numeric (-1) in your onGridExecute

-----------

And also, here is an easier-to-read way to lay out your sql query:

sql = <<%txt%
INSERT INTO training_procedure_revision_employee (procrev_id,emp_id,job_type)
SELECT :procrevid, employee_id, job_position FROM train1
INNER JOIN training_matrix ON job_position = training_matrix.job_type
INNER JOIN training_matrix_procedures ON training_matrix_id = matrix_id
INNER JOIN manual ON procedure_section_id = section_id
WHERE section_id = :sectionid
%txt%

Yours is like this:
sql = "Insert into training_procedure_revision_employee (procrev_id,emp_id,job_type) "\
"Select :procrevid, employee_id, job_position from train1 inner join training_matrix on job_position = training_matrix.job_type inner join "\
"training_matrix_procedures on training_matrix_id = matrix_id inner join manual on procedure_section_id = section_id "\
"where section_id = :sectionid"

Steve Wood, you are BRILLIANT. The DIM and assigning the value as a char worked (DUH)! I honestly didn't think to DIM the session variable because I'm thinking the value was already set at login and didn't need done again. Setting the value as numeric instead of char though, my sheer stupidity!

Regarding the INSERT, that's helpful information as well. I didn't code any of this initially, just making enhancements so that wasn't my doing but every little thing that is pointed out to me, helps me learn so I truly appreciate the advice.

Thanks a million!

[Steve Wood]

Re: filter grid based on sql query by session variable issue

You only need to dim the session variable in the component for testing in Live Preview because it wont have gone through your login process where it would normally be set.