Help get this topic noticed by sharing it on Twitter, Facebook, or email.
I’m cool

Solution: How to pass arbitrary parameter into SQL Command

I asked this question about one year ago (click here to see it) , was still looking for a less hacky solution.

In my case I needed to pass an arbitrary value into my SQL query, for purpose of deriving columns/data in the grid.

Even though COT talk about session variables and what-not here:
http://www.codeontime.com/learn/sql-b...
When you try to apply them, they are only applicable in very narrow cases of programming. Beats my why they made @session vars so hard to use..

But I found a relatively easy way that works:
1. In ControllerAction "Select" for your grid/form, set a session variable, in my case the session variable is named "UserName":


2. In your field that you want to use the passed in value, set it as SQL computed with an SQL formula. In the formula, use the COT sql syntax to reference the session variable:


2. Now the secret sauce! In the filter expression of the view, create a dummy filter that references your session variable - this causes COT framework to append the session variable as a parameter correctly, and pass the value through. Make sure the filter criteria is always true so as not to filter your records inadvertantly:


And the working result, in this case I just returned "param= @paramValue " from my function to demonstrate it working:


Hope this helps someone else who struggled with this! :-)

Pat.
9 people like
this idea
+1
Reply
  • I’m happy
    thanks patrick... this will solve some of my problem
    • you're welcome charles. :)
    • public partial class SharedBusinessRules : MyBusinessApp.Data.BusinessRules
      {
      public SharedBusinessRules()
      {
      }

      [AccessControl("", "TypeBusinessID", "[TypeBusiness] = @TypeBusinessID")]
      public void FilterByBusinessType()
      {
      if (!UserIsInRole("BusinessOwner"))
      RestrictAccess("@TypeBusinessID", TypeBusinessID);
      }
      }
      }

      In my case, I'd like to pass TypeBusinessID as a value which I've gathered somehow from a Sesssion Variable. In my case, a User might be assigned to fulfill different roles in different stores for an specific type of business.

      He/She might be authorized to work iin the PetShop as cashier, in the Retail Store as the inventory person, in the drugstore as sales person

      So after login I need to check my taskassignment table so the person, after he/she had logged on in via ASP.Net membership way (which wouldn't allow me to add an extra column for gathering "BusinessType") the person chooses his/her role and will have to provide a second login password (not the ASP.Net one but my own secondary validating screen) and from that grid he had chosen I need to assign that value to a Session Variable TypeBusinessID so he can accomplish the kind of tasks he should perform in that type of business

      Is this too silly?

      QUESTION: I need to know how to pass the value from the selected gridrow to my session variable?

      I ask this because in some of the COT learning material examples provides ways of assigning "fixed values" only, like State="OR", Employee="Davolio", Region="LA", ProductName="CHAI" ... and so on ... not dynamic values
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Hi Patrick
    Thanks for the many solutions you shared here with us, if you please can help me in a problem that COT is not prepared to solve now, which is, i used a custom editor for a date field, jquery datePicker control, instead of the Ajax tool kit , it works well in Ceate and Edit forms, but it doesn't in the advanced search bar, have you ever encounter this?

    Thanks again Patrick

    AMSH
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated