Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi everyone!
 
I am running into an issue that I need some advice on. I am using parametrized queries to get information from chosen items in comboboxes to place in the queries for further down.
 
The idea that I have right now is:
 
Combobox 1 - get table name
Combobox 2 - get column name
 
Query to grab selected column name for the next combobox.
 
The query that I'm stuck on is:
 
SqlCeCommand accessoriesCommand = new SqlCeCommand("SELECT DISTINCT subcategory FROM accessories WHERE subcategory = @subcategory", conn);
 
The table in this query doesn't have a column called "subcategory". The selected item in the combobox 2 is supposed to be placed in the accessoriesCommand. Is this do-able? If so, can someone shed some light as to how to properly format this?
 
The error I'm getting is that the column doesn't exist.
 
Thanks everyone!
Posted 15-Feb-13 7:26am
Comments
richcb at 15-Feb-13 12:34pm
   
So wouldn't you want to have the actual name of the table column in your query and the selection from the combobox would be your variable that the Where clause checks for?
joshrduncan2012 at 15-Feb-13 12:38pm
   
My boss wants me to allow for dynamic column looping through from information_schema.columns incase our client wants to add a new subcategory. The subcategories are the columns in some of the tables. I really prefer parametrized queries, but I have to manually prevent sql injection (per Griff's answer below.)
richcb at 15-Feb-13 12:40pm
   
Got ya.
joshrduncan2012 at 15-Feb-13 12:41pm
   
Even though it drives me crazy that I can't use parametrized queries in this instance, I am ok with it. Manual SQL injection prevention is next on my radar to catch up on.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can't pass the name of the column as a parameter - you would have to concatenate strings to create the command (and be very careful to protect yourself from SQL injection attacks):
SqlCeCommand accessoriesCommand = new SqlCeCommand("SELECT DISTINCT " + subcategory  + " FROM accessories", conn);
  Permalink  
Comments
joshrduncan2012 at 15-Feb-13 12:34pm
   
Thanks Griff!
OriginalGriff at 15-Feb-13 12:40pm
   
You're welcome!
richcb at 15-Feb-13 12:40pm
   
Nice job OG.
Monjurul Habib at 17-Feb-13 6:24am
   
5+
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Might something like the following be useful?
 
SELECT 'SELECT DISTINCT '+[2]+' FROM '+[1] FROM __sysobjects WHERE [1]=@TableName AND [2]=@ColumnName
 
If the table and column exists you should get a result which you can then execute.
  Permalink  
Comments
joshrduncan2012 at 15-Feb-13 14:19pm
   
Another GREAT solution. Thanks PIEBAL.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 195
1 ProgramFOX 130
2 Maciej Los 105
3 Sergey Alexandrovich Kryukov 105
4 Afzaal Ahmad Zeeshan 82
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 15 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100