CODEWITHSUNDEEP
c#sql.netsql-serveralter-table
FeuFeuAngel
FeuFeuAngel

Reputation: 23

C# Sql Client: ALTER Table doesn't work

I have a code that is used to add some lines to SQL but it won't work. Select and Delete are working but not the ALTER TABLE command.

If I copy and paste just my console output into Microsoft Management Sql Query, it works. (tmp1 gets filled with some Name, tmp2 gets filled for Example CHAR(50))

Edit: I dont get any Error, in the logs of my SQL server i dont see any command called "Alter" to be excuted.

 string tmp1, tmp2;
 tmp1 = addfrm.getTableName();
 tmp2 = addfrm.getType();

 string constring = @"Data Source=" + adr + ";Initial Catalog=" + dat + ";User ID=" + user + ";Password=" + pwd;
 try
 {
     using (SqlConnection con = new SqlConnection(constring))
     {
         string tmp = @"ALTER TABLE " + tbl + " ADD " + tmp1 + " " + tmp2;
         Console.WriteLine("Mein Befehl lautet: " + tmp);

         using (SqlCommand cmd = new SqlCommand(tmp, con))
         {
             cmd.CommandType = CommandType.Text;
             using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
             {
             }
         }
     }
 }

 catch (SqlException) 
 { 
     MessageBox.Show("Fehler"); 
 }

Upvotes: 0

Views: 721

Answers (3)

FeuFeuAngel
FeuFeuAngel

Reputation: 23

i just avoided it now with Fill. Its bit messy but ok. Thanks to Pawel who said it. 

                            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                        }

Upvotes: 0

Abhishek K. Upadhyay
Abhishek K. Upadhyay

Reputation: 1032

You are not setting the values properly. Try this it should work.

string tmp1, tmp2;
                tmp1 = addfrm.getTableName();
                tmp2 = addfrm.getType();

                string constring = @"Data Source=" + adr + ";Initial Catalog=" + dat + ";User ID=" + user + ";Password=" + pwd;
                try
                {
                    using (SqlConnection con = new SqlConnection(constring))
                    {
                        string tmp = @"UPDATE TABLE " + tbl + " SET Col1 = '" + temp1 +"',Col2='" + tmp2 +"'";

                        Console.WriteLine("Mein Befehl lautet: " + tmp);

                        using (SqlCommand cmd = new SqlCommand(tmp, con))
                        {
                            cmd.CommandType = CommandType.Text;
                            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                            {
    cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
                catch (SqlException) { MessageBox.Show("Fehler"); }

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

You don't send SQL query to database. Use ExecuteNonQuery on SqlCommand. Instead of:

using (SqlCommand cmd = new SqlCommand(tmp, con))
{
    cmd.CommandType = CommandType.Text;
    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
    {

    }
}

use

using (SqlCommand cmd = new SqlCommand(tmp, con))
{
    cmd.ExecuteNonQuery();
}

Adapter will not execute query until adapter.Fill is called.

Upvotes: 1

Related Questions