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
– Habib, Commented Oct 28, 2014 at 14:14
Also Use QUOTENAME in your query See: this post: stackoverflow.com/questions/20719449/… — Habib
– Habib, Commented Oct 28, 2014 at 14:15
@FilipDeVos, Yeah Parametrize term is wrong from DML, What I meant was to use QUOTENAME or SqlCommandBuilder.QuoteIdentifie method — Habib
– Habib, Commented Oct 28, 2014 at 14:21
Right, comm.ExecuteNonQuery needed. Also, you can refactor/simplify your code by (A) having just one try block, but 2 subsequent catches. One for SqlException and another for Exception. Like for ThrowTest3 here - 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
– TarasB, Commented Oct 28, 2014 at 14:23

Somdip Dey · Accepted Answer · 2016-10-05 10:24:50Z

2

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

answered Oct 5, 2016 at 10:24

Somdip Dey

3,4066 gold badges34 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

SQL Command not working in C# Windows Form Application

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related