C# SQL Command parameter not working

Question

I've done some trouble shooting by taking out the parameters and replacing them with text, and the only parameter that is not working is @seat and I can't figure out why.

allSeats is an array of custom controls. I've tried replacing the parameter contents with an actual string ie. "A1" and that still doesn't work. If I remove the @seat parameter and replace it with A1, it works, but I need to be able to set the column name dynamically.

myConnection.Open();

SqlCommand myCommand = new SqlCommand("UPDATE Events SET @seat = @truefalse WHERE Name = @name", myConnection);

SqlParameter param = new SqlParameter();
SqlParameter param2 = new SqlParameter();
SqlParameter param3 = new SqlParameter();
param.ParameterName = "@seat";
param2.ParameterName = "@truefalse";
param2.DbType = System.Data.DbType.Boolean;
param3.ParameterName = "@name";
param.Value = allSeats[i].Name;
param2.Value = allSeats[i].taken;
param3.Value = name;
myCommand.Parameters.Add(param);
myCommand.Parameters.Add(param2);
myCommand.Parameters.Add(param3);
myCommand.ExecuteNonQuery();

Any help is appreciated. If I need to post more relevant code please let me know and I shall add it.

Is @seat supposed to be a dynamic column name? If so, you will need to use dynamic SQL to make this work. — shree.pat18
– shree.pat18, Commented Dec 4, 2014 at 8:08
Is seat a completely dynamic column name, or will it be one of a fixed number of options? — Paul Michaels
– Paul Michaels, Commented Dec 4, 2014 at 8:11

Soner Gönül · Accepted Answer · 2014-12-04 08:20:13Z

3

In your

SET @seat = @truefalse

part, you try to parameterize your column name. You can't do that. You only can parameterize your values, not column name or table names.

You can use dynamic SQL for such a case but it is not recommended. Read

As a recommendation, use a white list such a case. I hope, there can only be a fixed set of possible correct values for the column name. Of course, this requires strong validation in your inputs part.

edited Dec 4, 2014 at 8:20

answered Dec 4, 2014 at 8:08

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

2 Comments

Jacob Over a year ago

Oh, I wasn't aware you couldn't use parameters for column names. I've just gone ahead and concatenated allSeats[i].Name into the command string instead. I knew I could do that the whole time I was just intent on getting this to work, Lol. Thanks a lot for your help! :)

Soner Gönül Over a year ago

@user3217010 Your are quite welcome. Yes, parameters only for values. If you really want to get your column or table names, always use a white list with a strong recommendation.

Peter Williams · Accepted Answer · 2014-12-04 08:14:54Z

1

Agree with Soner. Change the string before you create the command

string cmdStr = string.Format("UPDATE Events SET {0} = @truefalse WHERE Name = @name",  allSeats[i].Name)

Then only use 2 parameters.

 SqlCommand myCommand = new SqlCommand(cmdStr, myConnection);
                SqlParameter param = new SqlParameter();
                SqlParameter param2 = new SqlParameter();

etc.

answered Dec 4, 2014 at 8:14

Peter Williams

113 bronze badges

Comments

Soner Gönül · Accepted Answer · 2014-12-04 08:30:13Z

0

cmd.parameter.addwithvalue("@param1", value1);

cmd.parameter.addwithvalue("@param2", value2);

use like this.

edited Dec 4, 2014 at 8:30

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

answered Dec 4, 2014 at 8:27

Durgpal Singh

12k4 gold badges40 silver badges50 bronze badges

1 Comment

Durgpal Singh Over a year ago

my mistake. i got your question now

martijn · Accepted Answer · 2014-12-04 09:26:16Z

As Soner has mentioned, columns cannot be parameterized. This means you will either have to create dynamic queries, or create all the parameterized once at the startup, one query per column name.

this can be done in the following example:

private static Dictionary<string, SqlCommand>  parameterizedCommands = new Dictionary<string,SqlCommand>();

public static void CreateparameterizedCommandsy(string[] colums)
{
    parameterizedCommands = new Dictionary<string,SqlCommand>();
    foreach (string colname in colums)
    {
        parameterizedCommands.Add(colname, CreateCommandForColumn(colname));
    }
}

public static SqlCommand CreateCommandForColumn(string columnName)
{
    SqlCommand myCommand = new SqlCommand(string.Format("UPDATE Events SET {0} = @truefalse WHERE Name = @name",columnName));
    // the following statement creates the parameter in one go. Bit = boolean
    myCommand.Parameters.Add("@truefalse", SqlDbType.Bit);
    myCommand.Parameters.Add("@name", SqlDbType.Text);
    return myCommand;
}


public int ExccuteColumnUpdate(string columnName, bool setToValue, string name, SqlConnection connection)
{
    connection.Open();
    try
    {
        SqlCommand command;
        if (parameterizedCommands.TryGetValue(columnName, out command))
        {
            command.Connection = connection;
            command.Parameters["@truefalse"].Value = setToValue;
            command.Parameters["@name"].Value = name;
            return command.ExecuteNonQuery();
        }
    }
    finally
    {
        connection.Close();
    }
    return 0;
}

Collectives™ on Stack Overflow

C# SQL Command parameter not working

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Related