Using parameters in sql query to determine which column to use

Question

I am trying to pull data from my table based on the button a user clicks, so if they click the 1940's button it will pull all products from that decade but I cant get the query to work. It has to do with the @decade parameter because that is where I am getting the user input from but it doesnt like it when I am trying to choose a column using that parameter

ImageButton decadeBtn = (ImageButton)sender;
var decade = decadeBtn.CommandArgument;

yearHead.InnerText = decade.ToString();
string cmd="";
DataSet ds;
if (typeOfArchive == "On Hand")
{
    cmd = @"Select * From ARCHIVE_DECADE_TBL WHERE DECADE_@decade=@decade AND PRODUCT_LINE=@Line AND LOCATION is not null;";
}
else if(typeOfArchive == "All Other"){
    cmd = @"Select * From ARCHIVE_DECADE_TBL WHERE DECADE_@decade=@decade AND PRODUCT_LINE=@Line AND LOCATION is null";
}

using (OleDbConnection dbConn = new OleDbConnection(connectionString))
using (OleDbDataAdapter dbCmdDecade = new OleDbDataAdapter(cmd, dbConn))
{
    dbConn.Open();
    dbCmdDecade.SelectCommand.Parameters.Add("@decade", OleDbType.Integer).Value = decade;
    dbCmdDecade.SelectCommand.Parameters.Add("@line", OleDbType.VarChar).Value = productLine;
    ds = new DataSet();
    dbCmdDecade.Fill(ds, "products");
}

That's really a creative way to use a parameter, but sadly, no you can't use a parameter in that way — Steve, May 23 '16 at 18:23
@Steve my mistake, thanks. Still new to this so I'm stepping through it slowly but getting there — Ben Cavenagh, May 23 '16 at 18:34

score 0 · Answer 1 · answered May 23 '16 at 18:29

So ARCHIVE_DECADE_TBL has columns that are named something like DECADE_1990 with a value of 1990, DECADE_2000 with a value of 2000, etc?

It really should be designed to just be called "DECADE" with the value being 1990/2000/etc, but if that's not possible, you'll have to build your query dynamically. I don't believe those parameters will work to set the column name. They can set a value to check for, but not the column names.

You'll have to build the query out manually in c#, so something like:

cmd = @"Select * From ARCHIVE_DECADE_TBL WHERE DECADE_" + decade + @" = @decade AND PRODUCT_LINE=@Line AND LOCATION is not null;";

Now, if I misunderstood and your column is actually named DECADE_@decade, then I think you'll just need to change your variable so it's not @decade, so something like @mydecade. The conflict there will confuse it.

Sooooo like...

cmd = @"Select * From ARCHIVE_DECADE_TBL WHERE DECADE_@decade=@mydecade AND PRODUCT_LINE=@Line AND LOCATION is not null;";

And then down below:

dbCmdDecade.SelectCommand.Parameters.Add("@mydecade", OleDbType.Integer).Value = decade;

That probably shouldn't have an @ in the column name though. :)

score 0 · Accepted Answer · edited May 23 '17 at 12:31

No you can't use a parameter in that way. As a rule, you cannot use a parameter to define a column name or a table name (or concatenating it to form a column name). A parameter could only be used to define a value used in the query. (or with a stored procedure to create an SQL Text inside the sp to be executed but that is another more complex story),

However, assuming that you are not allowing your users to type directly the decade value (Sql Injection vulnerability), then it is pretty simple to create a string with the column name desidered and use it in your query.

Add a method that just concatenate together you decade string with your prefix for the DECADE column

private string GetDecadeColumn(string decade)
{
    return "DECADE_" + decade;
}

and in you query

if (typeOfArchive == "On Hand")
{
    cmd = @"Select * From ARCHIVE_DECADE_TBL WHERE " + 
            GetDecadeColumn(decade) +          
            " AND PRODUCT_LINE=@Line AND LOCATION is not null;";
}
else if(typeOfArchive == "All Other"){
    cmd = @"Select * From ARCHIVE_DECADE_TBL WHERE " + 
           GetDecadeColumn(decade) + 
           " AND PRODUCT_LINE=@Line AND LOCATION is null";
}

using your example, OP could try something like this to one line it, of course I am assuming there is only 2 typeOfArchive values cmd = @"Select * From ARCHIVE_DECADE_TBL WHERE " + GetDecadeColumn(decade) + " AND PRODUCT_LINE=@Line AND LOCATION is " + (typeOfArchive == "On Hand") ? "not" : string.Empty + " null;"; — Alex W, May 24 '16 at 02:34
Agree, however I found the observation in the zfrank's answer correct. It makes sense to have just a DECADE column and check the values using something like a BETWEEN clause to extract the records belonging to a particular decade. At that point, with two parameters (low_decade, high_decade) and an index on the DECADE column the query will be very simple — Steve, May 24 '16 at 07:11

Using parameters in sql query to determine which column to use

2 Answers2