C# - Parametrized Queries

Question

5.00/5 (1 vote)

See more:

VS2012

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:

SQL

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 6:26am

joshrduncan2012

Add a Solution

Comments

Richard C Bishop 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 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.)

Richard C Bishop 15-Feb-13 12:40pm

Got ya.

joshrduncan2012 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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2013-02-15T06:32:00

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):

C#

SqlCeCommand accessoriesCommand = new SqlCeCommand("SELECT DISTINCT " + subcategory  + " FROM accessories", conn);

Posted 15-Feb-13 6:32am

OriginalGriff

Comments

joshrduncan2012 15-Feb-13 12:34pm

Thanks Griff!

OriginalGriff 15-Feb-13 12:40pm

You're welcome!

Richard C Bishop 15-Feb-13 12:40pm

Nice job OG.

Monjurul Habib 17-Feb-13 6:24am

5+

PIEBALDconsult · Accepted Answer · 2013-02-15T07:39:00

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.

Posted 15-Feb-13 7:39am

PIEBALDconsult

Comments

joshrduncan2012 15-Feb-13 14:19pm

Another GREAT solution. Thanks PIEBAL.