CODEWITHSUNDEEP
c#sqlsql-server
Jacob
Jacob

Reputation: 728

C# SQL Command parameter not working

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.

Upvotes: 1

Views: 3683

Answers (4)

martijn
martijn

Reputation: 1469

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;
}

Upvotes: 0

Durgpal Singh
Durgpal Singh

Reputation: 11953

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

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

use like this.

Upvotes: 0

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98740

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.

Upvotes: 3

Peter Williams
Peter Williams

Reputation: 11

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.

Upvotes: 1

Related Questions