SQL Command not working in C# Windows Form Application

Question

I am trying to add a new column in a table in the database through my Windows Form Application (built using C#).

I have created a method CreateFieldNameInTableData(string FieldName, string TableName), which passes the name of the variable 'FieldName', which is the name to the column to be added to the table, 'TableName', which can be specified by the user.

The code for this method which is trying to add the column to the table is as follows:

private void CreateFieldNameInTableData(string FieldName, string AssetTypeCode)
        {
            SqlConnection conn = new SqlConnection(SQLConnectionString);
            SqlCommand comm = null;
            try
            {
                try
                {
                    comm = conn.CreateCommand();
                    comm.CommandText = "ALTER TABLE [AS_" + TableName + "_DATA] ADD " + FieldName + " VARCHAR(30)";
                    comm.CommandType = CommandType.Text;
                    comm.CommandTimeout = 30;   //30 seconds
                    conn.Open();
                }
                catch (SqlException err)
                {
                    MessageBox.Show("SqlException Error : " + err.Message.ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception Error : " + ex.Message.ToString());
            }
            finally
            {
                conn.Close();
            }
        }

When I am using the same SQL script in Server Management Studio, it is successfully creating the column in the table AS_TableName_DATA, but when I am trying to do the same using C#, it is not throwing any error but after inspection, the table do not have the new column that is meant to be created.

Any help...what's going wrong?

P.S. I have also checked the SQLConnectionString, which is also connecting to the right database.

You are not executing your command. Add `cmd.ExecuteNonQuery();` at the end of `try` block — Habib, Oct 28 '14 at 14:14
Also Use `QUOTENAME` in your query See: this post: http://stackoverflow.com/questions/20719449/c-sharp-alter-table-and-add-a-column-programmatically-asp-net-sql-server — Habib, Oct 28 '14 at 14:15
@FilipDeVos, Yeah Parametrize term is wrong from DML, What I meant was to use QUOTENAME or [SqlCommandBuilder.QuoteIdentifie](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.quoteidentifier(v=vs.110).aspx) method — Habib, Oct 28 '14 at 14:21
Right, `comm.ExecuteNonQuery` needed. Also, you can refactor/simplify your code by (A) having just one `try` block, but 2 subsequent `catch`es. One for `SqlException` and another for `Exception`. Like for `ThrowTest3` here - http://msdn.microsoft.com/en-us/library/0yd65esw.aspx. You will have less nested code. Another (B) - 30 is default timeout, you can skip that line `comm.CommandTimeout = 30`. — TarasB, Oct 28 '14 at 14:23
Thank You Taras, your suggestion worked...was missing a comm.ExecuteNonQuery ....... :) — Somdip Dey, Oct 28 '14 at 14:50

score 2 · Accepted Answer · answered Oct 05 '16 at 10:24

As suggested by @TarasB, comm.ExecuteNonQuery() was missing and the SQL query was not executed by c# SQLClient. Looking back at this error feels like a 'school boy error'.

The final working code is as follows:

         try
            {
                comm = conn.CreateCommand();
                comm.CommandText = "ALTER TABLE [AS_" + TableName + "_DATA] ADD [" + FieldName + "] VARCHAR(30)";
                comm.CommandType = CommandType.Text;
                comm.CommandTimeout = 120;   //120 seconds
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch (SqlException err)
            {
                MessageBox.Show("SqlException Error : " + err.Message.ToString());
            }

SQL Command not working in C# Windows Form Application

1 Answers1