CODEWITHSUNDEEP
c#.netsql-servert-sqlwindows-forms-designer
Somdip Dey
Somdip Dey

Reputation: 3386

SQL Command not working in C# Windows Form Application

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.

Upvotes: 1

Views: 1530

Answers (1)

Somdip Dey
Somdip Dey

Reputation: 3386

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

Upvotes: 2

Related Questions