Store Procedure to display list of Data

  • 16 February 2022
  • 6 replies
  • 53 views

Hello All,

I have created below store procedure and its available as smartObject in K2:

 

create procedure dbo.GetDept
@Department nvarchar(100)
as
select *
from [dbo].[desktop]
where Department = @Department

 

1. I want to create a drop down that will be list of all departments.

2. when user select any department from the drop down

3. this will display all items in the selected department on the k2 page.

 

I appreciate any help on this.

 

 

 

 


6 replies

Userlevel 5
Badge +13
Where are you getting stuck? Do you have a SmartObject tied to the Departments table so you can load a dropdown with all of them? Then, I assume when you select a department from the dropdown you would call populate a list box with the results of your GetDept stored procedure? How many fields come back from your stored procedure, if it is too many to concatenate in a list box you may need a list view. If you can provide more details I can help.

@tbyrne777 


Thanks for your response.


stored proc works ok in MSSQL: exec dbo.GetDept @Department = 'IT Desk'


it lists all data related to IT department.


In K2, I want a case when u select the drop down(IT Desk or any other department); the matching results are displayed.



I created item-view(searchDesk) on the table:desktop.


Made department as dropdown on the item-view


use table:desktop as datasource



 


I want a case when u select the drop down(IT Desk or any other department); the matching results from the stored proc are displayed.



Hope I am clear.  Thanks


 


 

Userlevel 5
Badge +13
Try this - create a list view bound to your GetDept function. At the top, create the department dropdown like you did in your item view. Then, on the Changed event of your department, execute the "List" method of your list view. When you configure this List method, it should be smart enough to say it needs a Department parameter - drag in your department dropdown and you should be all set.

Please let me know if that is making sense

Hello,


I am not sure how I can place dropdown inside the list view.


So I created a list view with my stored proc as you suggested.


I create an item view with 1row and place the dropdown list(desktop table as datasource)



 


when  I test the form, got below error:



Rules on the form:



-



 


 

Userlevel 5
Badge +13
You should be able to drag a dropdown control to the top of the list view and it will appear above the grid at runtime.
Configure that "List" method (your last screenshot) to pass in the department dropdown.

@tbyrne777 


Thanks a lot. I finally did it; it works OK-

Reply