Run a query with variable attribute names

Question

I am trying do something like this example:

adp.SelectCommand.CommandText = "select * from table1 where @1=@2 and @3=@4";
adp.SelectCommand.Parameters.AddWithValue("@1", textBox1.Text);
adp.SelectCommand.Parameters.AddWithValue("@2", textBox2.Text);

I think it's pretty clear what I would like to do. I want both field name (Database) and value to be taken from a text box within a win form. But the results aren't good. Can you please tell me what I'm doing wrong? There are no errors or anything.

Thanks in advance.

Are you also adding Parameters @3 and @4? Also, you should realize that there's not enough information on what your problem might be. — ANeves
– ANeves, Commented Apr 8, 2014 at 13:32
yes . try it your self .it seems the field name can not be a variable . — user3511118
– user3511118, Commented Apr 8, 2014 at 13:35

pid · Accepted Answer · 2014-04-08 13:50:11Z

1

Only values are considered to be parameters in queries. The attributes you reference are never parameters. This is a basic rule of SQL and many other query language implementations.

But you can code around this constraint as shown below.

Mind that the actual attribute names should NEVER come from the outside (user or database) but be hard-coded for security reasons. Not doing so might expose you to SQL injection and similar attacks.

To summarize:

values are parameters
attributes are hard-coded

This is the source code you should adapt to your problem:

QueryCase querycase;
string querytext, attributeName1, attributeName2;

querycase = QueryCase.Alpha; // switch between possible queries

// somewhere later in your code...

switch (querycase)
{
    case QueryCase.Alpha:
        attributeName1 = "attribute1";
        attributeName2 = "attribute2";
    case QueryCase.Beta:
        attributeName1 = "attribute3";
        attributeName2 = "attribute4";
    default:
        throw new NotImplementedException(string.Format("unrecognized query case (was {0})", (int)querycase));
}

querytext = string.Format("select * from table1 where {0}=@1 and {1}=@2", attributeName1, attributeName2);

adp.SelectCommand.CommandText = querytext;

Here, querycase is a hard-coded enum so that you cannot set arbitrary attribute names:

enum QueryCase
{
    Alpha,
    Beta
}

edited Apr 8, 2014 at 13:50

answered Apr 8, 2014 at 13:36

pid

11.6k6 gold badges37 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3511118 Over a year ago

thank you , i will see how would that work . im not worried about security problems ATM , im using access for gods sakes :)

pid Over a year ago

If I'd post something that is not secure and opens you up to SQL injection you and the SO community would have all reasons to downvote me, and believe me, I would downvote myself, too! :)

user4093171 · Accepted Answer · 2014-09-30 01:46:41Z

0

There is no result when you compare 2 parameters, your code must be:

adp.SelectCommand.CommandText = "select * from table1 where ColumnName1=@1 and ColumnName2=@2";
adp.SelectCommand.Parameters.AddWithValue("@1", textBox1.Text);
adp.SelectCommand.Parameters.AddWithValue("@2", textBox2.Text);

answered Sep 30, 2014 at 1:46

user4093171

Collectives™ on Stack Overflow

Run a query with variable attribute names

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related