Click here to Skip to main content
Rate this: bad
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
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
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);
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
Rate this: bad
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.
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 690
1 Maciej Los 280
2 Suvendu Shekhar Giri 229
3 Richard Deeming 195
4 Sascha Lefévre 164
0 Sergey Alexandrovich Kryukov 9,678
1 OriginalGriff 9,135
2 Peter Leow 5,082
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 2,606

Advertise | Privacy | Mobile
Web02 | 2.8.150327.1 | Last Updated 15 Feb 2013
Copyright © CodeProject, 1999-2015
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