troubleshooting Question

How can I make my C# Parameterised query work properly using IDbCommand?

Avatar of chrisbray
chrisbrayFlag for United Kingdom of Great Britain and Northern Ireland asked on
C#SQL
5 Comments1 Solution1525 ViewsLast Modified:
Hi Guys,

I have set up an UPDATE function to simply reset the value of a specified toggle field. The hard coded version of the SQL statement works fine, but the parameterized version is not updated with the parameter values. However, when I do exactly the same thing to obtain data via an ExecuteScalar() for example, it works perfectly.

Code:
        /// <summary>
        /// Set a single Boolean value in a specified table field, e.g. a Toggle Field
        /// </summary>
        /// <param name="tableName">A string representing the name of the table to update</param>
        /// <param name="idField">A string representing the name of the ID field</param>
        /// <param name="id">An Int64 value representing the ID of the record to update</param>
        /// <param name="toggleField">A string representing the name of the field to update</param>
        /// <param name="newValue">The new boolean value to assign to the field</param>
        /// <returns></returns>
        public bool SetBooleanValue(string tableName, string idField, Int64 id, string toggleField, bool newValue)
        {
            IDbCommand command = providerFactory.CreateCommand();
            command.Connection = connection;

            // command.CommandText = "UPDATE Currencies SET Inactive = true WHERE CurrencyId = 174"; <-- this works

            command.CommandText = "UPDATE @Table SET @ToggleField = @NewValue WHERE @IDField = @ID";// <-- this doesn't!!

            IDbDataParameter parameter;

            parameter = command.CreateParameter();
            parameter.ParameterName = "@Table";
            parameter.Value = tableName;
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = "@ToggleField";
            parameter.Value = toggleField;
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = "@NewValue";
            parameter.Value = newValue;
            parameter.DbType = DbType.Boolean;
            command.Parameters.Add(parameter);
            parameter = command.CreateParameter();

            parameter.ParameterName = "@IDField";
            parameter.Value = idField;
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = "@ID";
            parameter.Value = id;
            parameter.DbType = DbType.Int64;
            command.Parameters.Add(parameter);

            bool result;

            connection.Open();

            result = (command.ExecuteNonQuery() == 1);

            connection.Close();

            return result;
 
        }


When the code is run, I get an error 'Table @Table does not exist'.
_________________
Chris Bray
Vertical Software
http://www.verticalsoftware.co.uk
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros