Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 15th, 2008, 07:42 PM
Authorized User
 
Join Date: Oct 2007
Location: reno, nevada, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT VARIABLE Number of columns

Hi all,

I am developing a web application(using asp.net + C#) where I have a checkboxlist of columns in a table(on SQL server). Lets suppose there is a table customer_info with columns: name, address,phone, website etc.
So, my checkboxlist would list all the columns in the table. User can select one or more columns to display. Suppose user chooses only name and phone number to be displayed then While displaying the records, I must only display the requested columns to the user. The choice can vary each time. How do I do this using stored procedure.

I have collected the selected columns by the user as a comma -separated variable: Eg: @columns would get value like 'name, address' for above example.

But when I use the query
SELECT @columns FROM customer_info

it displays something like this

name address
name address
name address
... and so on. It doesn't display the actual value of the columns.

Any help regarding this problem will be greatly appreciated.

Thanks!!!
__________________
durgesh
 
Old July 4th, 2008, 04:43 AM
Authorized User
 
Join Date: Nov 2006
Location: Bangalore, Karnataka, India.
Posts: 31
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via ICQ to debasisdas Send a message via AIM to debasisdas
Default

taht is becauase the query framed by you at runtime selects the text wrongly provided by you not the actual data from the desired columns. You need to reframe your queery properly at run time.

Regards
Debasis
 
Old September 17th, 2008, 02:44 PM
Registered User
 
Join Date: Sep 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
Any solution to this problem.


Thanks,

 
Old September 17th, 2008, 03:58 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You can't pass the list as a parameter.

You need to, instead, create an "ad hoc" SQL query that uses the list of values from the checkboxes. That is, create a C# String that *is* the SQL query.

String sql = "SELECT " + listOfCheckBoxValues + " FROM customerInfo";

and then execute that string query.
 
Old September 21st, 2008, 01:12 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

drani:

In a PL/SQL Procedure I did the following using a Drop-Down LOV (List Of Values with the Multi-Select feature) and a two-dimensional Array for holding the selected values. There are Buttons for Clearing or Resetting the List and Finish-Button. When the Finish-button is clicked the "Building the SELECT-Statement Procedure", begins: Loop through the array list building the SELECT-string adding a "," between each value in the array-List; At end of the Array-List add a blank-space followed by the FROM and the WHERE clauses.

Hopes this helps.

========================
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Load VARIABLE Number of columns cosmokramer Oracle 1 November 2nd, 2008 05:51 PM
Concatenating Columns in SELECT Problem kevorkian SQL Server 2000 6 June 13th, 2008 02:50 PM
Dynamic number of columns returned from sp dreadjr BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 1 July 5th, 2006 03:42 PM
Select a value across 3 different columns jfergy Classic ASP Databases 0 January 23rd, 2006 02:46 AM
vb script + excel - select multiple columns mohit Excel VBA 1 January 21st, 2005 06:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.