CODEWITHSUNDEEP
c#sql
user2323240
user2323240

Reputation: 1677

get column from database using a string

My Code:

    SqlCommand command = new SqlCommand("SELECT min(Score) FROM MenAthletics WHERE [(@sportevent)] < (@result);", connect);
        command.Parameters.AddWithValue("@sportevent", sportEvent);
        command.Parameters.AddWithValue("@result", result);

the @result works fine (just a double variable) the @sportevent doesnt't work (error: invalid columnname) (sportEvent is a string)

how can I choose a column by giving in a string?

Upvotes: 0

Views: 175

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can parameterize values in SQL statements, but you cannot parameterize column or table names. You need to change the column name in the SQL string itself, for example, with string.Format:

SqlCommand command = new SqlCommand(
    string.Format("SELECT min(Score) FROM MenAthletics WHERE [{0}] < (@result);", sportEvent)
,   connect
);
command.Parameters.AddWithValue("@result", result);

Make sure that the column name does not come from user's input, otherwise you would open up your code to SQL injection attacks. In case the column name does come from user's input, you can validate the string against a list of available table columns, which could be made statically or by examining the structure of your table at runtime.

Upvotes: 4

dash
dash

Reputation: 91482

You can't use a column name as a parameter; you should instead consider constructing your query this way:

SqlCommand command = 
      new SqlCommand(
         String.Format(@"SELECT min(Score) 
                         FROM MenAthletics WHERE [{0}] < @result;",     
                         sportEvent), 
                      connect);
command.Parameters.AddWithValue("@result", result);

This kind of sql is called "dynamic sql" and can be an effective way of constructing queries on the fly.

However, there are pitfalls. As well as validating the user input, also make sure that the user you are connecting to the database with only has enough permissions to carry out the actions you want to do.

Another approach, which is less elegant, but can be placed directly into a stored procedure, is to use a CASE statement;

For example:

SELECT min(Score)
FROM MenAthletics 
WHERE 
    CASE 
      WHEN @sportEvent = 'SomeColumnName' THEN SomeColumnName
      WHEN @sportEvent = 'SomeColumnName2' THEN SomeColumnName2
    END < @result;

This gets very tedious to both create and maintain on large tables. The advantage is that the query is not dynamic.

Upvotes: 1

Freelancer
Freelancer

Reputation: 9074

This is because value in the sportEvent string which you are passing as a parameter is not matching with actual column existing in your database table.

Make sure that both of them matches and then only this error will go.

Otherwise dont pass table's column name as a parameter, directly write it in the query and let its column value be a parameter.

Hope it helps.

Upvotes: 0

aquaraga
aquaraga

Reputation: 4168

You could dynamically build the SQL query, instead of passing the column name as a parameter.

Upvotes: 1

Related Questions