5 Replies - 1877 Views - Last Post: 03 August 2014 - 07:07 PM Rate Topic: -----

#1 innermb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 02-August 14

Run SQL Query For Each Item In Listbox

Posted 02 August 2014 - 12:36 PM

I have a C# WPF program that takes a SQL query and populates a column from an Oracle database into a listbox on the form. I would like to be able to compare all items populated in the listbox, and run a SQL query for each item, populating those values into a different listbox.

So far, I have this code:

string sql2 = "select unique apps.fnd_application_all_view.application_name, fnd_responsibility_vl.responsibility_name, fnd_form_functions_tl.user_function_name, fnd_form_functions.function_name, fnd_form_functions_tl.description, fnd_form_functions.type FROM fnd_compiled_menu_functions, fnd_form_functions, fnd_form_functions_tl, fnd_responsibility, fnd responsibility_vl, apps.fnd_application_all_view WHERE fnd_compiled_menu_functions.function_id = fnd_form_functions.function_id AND fnd_responsibility.menu_id = fnd_compiled_menu_functions_menu_id AND fnd_responsibility_vl.responsibility_id = fnd_responsibility.responsibility_id AND fnd_form_functions.function_id = fnd_form_functions_tl.function_id AND apps.fnd_application_all_view.application_id(+) = fnd_responsibility.application_id AND fnd_responsibility.end_date is NULL AND fnd_responsibility_vl.end_date is NULL AND source_lang = 'US' AND fnd_responsibility_vl.responsibility_name = '" + lstResponsibilities.Items + "'";



                using (OracleDataAdapter da = new OracleDataAdapter(sql2, conn))
                {

                    lstResponsibilities.DisplayMember = "Responsibility_Name";
                    lstResponsibilities.ValueMember = "Responsibility_Name";
                    DataTable dt = new DataTable("Function_Name");
                    foreach (DataRow row in dt.Rows)
                    {
                        object[] objCollection = new object[lstResponsibilities.Items.Count];
                        lstResponsibilities.Items.CopyTo(objCollection, 0);
                        lstFunctions.Items(objCollection);
                    } 



I would like to be able to basically take the "Responsibility_Name" value from the first listbox (lstResponsibilities), which is already populated, and use those values to in the "sql2" query to find the "Function_Name" and populate "Function_Name" in the second listbox (lstFunctions).

Any assistance would be greatly appreciated.

Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Run SQL Query For Each Item In Listbox

#2 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 5830
  • View blog
  • Posts: 19,870
  • Joined: 05-May 12

Re: Run SQL Query For Each Item In Listbox

Posted 02 August 2014 - 01:04 PM

Instead of pinging the SQL server for each possible value, why no use the SQL "IN" SELECT condition? See documentation.
Was This Post Helpful? 0
  • +
  • -

#3 innermb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 02-August 14

Re: Run SQL Query For Each Item In Listbox

Posted 02 August 2014 - 05:05 PM

View PostSkydiver, on 02 August 2014 - 01:04 PM, said:

Instead of pinging the SQL server for each possible value, why no use the SQL "IN" SELECT condition? See documentation.


Thank you for the advice. However, how am I able to query the database for the value of every single Item within the listbox?

Thanks again.
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 5830
  • View blog
  • Posts: 19,870
  • Joined: 05-May 12

Re: Run SQL Query For Each Item In Listbox

Posted 02 August 2014 - 08:45 PM

Build a query using the IN select condition instead of doing straight equality. Use the contents of the listbox for building that query.

But in all actuality, you shouldn't be treating the listbox like a variable. The listbox is only UI that should reflect the contents of a collection. It is that collection which you should be using to build the query.
Was This Post Helpful? 0
  • +
  • -

#5 innermb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 02-August 14

Re: Run SQL Query For Each Item In Listbox

Posted 03 August 2014 - 11:33 AM

View PostSkydiver, on 02 August 2014 - 08:45 PM, said:

Build a query using the IN select condition instead of doing straight equality. Use the contents of the listbox for building that query.

But in all actuality, you shouldn't be treating the listbox like a variable. The listbox is only UI that should reflect the contents of a collection. It is that collection which you should be using to build the query.


Thanks again for the tips. I really don't know the first place to start in order to translate the query I have currently into something using the IN clause, though.

I'd be eternally grateful if you could get it started!
Was This Post Helpful? 0
  • +
  • -

#6 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 5830
  • View blog
  • Posts: 19,870
  • Joined: 05-May 12

Re: Run SQL Query For Each Item In Listbox

Posted 03 August 2014 - 07:07 PM

Wait... You composed that complicated query on line one of your code in your original post, but you don't know how to change it to use IN? There's a definite disparity in skill here...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1