sqlcommand execute query not updating deleting and inserting

Question

Hello i always use SQlcommand for non query but now something wrong i dont know what i have 3 buttons with operations update insert and delete but i created unique method for all 3 operations, the problem is it doesn't insert delete or update:

private void operacao(String operacao) {
        String comando = "";
        con = new SqlConnection();
        WorksDataSet  dataset = new WorksDataSet();
        con.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Works.mdf;Integrated Security=True;User Instance=True;Asynchronous Processing=true";
        try
        {
            con.Open();

        }
        catch (SqlException cox) {
            MessageBox.Show(cox.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        switch (operacao) { 
            case "inserir":

                try
                {
                    comando = "Insert Into Estudante (Codigo,Nome,Apelido) values(" + txtID.Text + ",'" + txtNome.Text + "','" + txtapelido.Text + "')";
                    SqlCommand command = new SqlCommand(comando, con);
                    SqlDataAdapter sda=new SqlDataAdapter(command);
                    command.CommandType = CommandType.Text;
                    sda.Fill(dataset);
                    command.ExecuteNonQuery();
                    command.Dispose();
                    MessageBox.Show("Adicionado com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException sex) {
                    MessageBox.Show(sex.Message , this.Text,MessageBoxButtons.OK,MessageBoxIcon.Error );
                }

                break;

            case "apagar":
                comando = "delete from Estudante where Codigo=" + txtID;
                try
                {

                    SqlCommand command = new SqlCommand(comando, con);
                    command.BeginExecuteNonQuery();
                    MessageBox.Show("Removido com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException sex)
                {
                    MessageBox.Show(sex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                break;
            case "atualizar":
                comando = "update table Estudante set nome='" + txtNome + "'^ apelido='" + txtapelido + "'";
                try
                {

                    SqlCommand command = new SqlCommand(comando, con);
                    command.BeginExecuteNonQuery();
                    MessageBox.Show("Actualizado com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException sex)
                {
                    MessageBox.Show(sex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                break;
            default:
                break
                ;
        }
        con.Close();
    }

wow.. catch (SqlException sex) and MessageBox.Show(sex.Message... seems intersting.. :) — Dr. Rajesh Rolen, Jul 05 '12 at 12:45
@Oldemiro: is it throwing any exception or its not going into switch case as you are using string in switch cases. so please confirm..? — Dr. Rajesh Rolen, Jul 05 '12 at 12:48
have you checked your sql-statements direct? (for example in the sql management studio) — Tomtom, Jul 05 '12 at 12:50

Steve · Accepted Answer · 2012-07-05T13:09:28.577

You should use parametrized query. ALWAYS.....

this for the insert op.

comando = "Insert Into Estudante (Codigo,Nome,Apelido) values(@id, @nome, @apelido");
SqlCommand command = new SqlCommand(comando, con);                     
command.Parameters.AddWithValue("@id", txtID.Text);
command.Parameters.AddWithValue("@nome", txtNome.Text);
command.Parameters.AddWithValue("@apelido", txtapelido.Text);
command.CommandType = CommandType.Text;                     
command.ExecuteNonQuery();

No need to use a dataset or a dataadapter here. just the ExecuteNonQuery

this for the delete op.

comando = "delete from Estudante where Codigo=@id";
SqlCommand command = new SqlCommand(comando, con);
command.Parameters.AddWithValue("@id", txtID.Text);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

Notice that you should pass the Text property, not the whole TextBox

this for the update op

comando = "update table Estudante set nome=@nome, apelido=@apelido where codigo=@id";
SqlCommand command = new SqlCommand(comando, con);
command.Parameters.AddWithValue("@id", txtID.Text);
command.Parameters.AddWithValue("@nome", txtNome.Text);
command.Parameters.AddWithValue("@apelido", txtapelido.Text);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

Here also, use the Text property not the TextBox object

In this way you don't need to worry about quotes in your string params and you close the door to
Sql Injection Attacks

This is correct, but i don't think that this solves the problem :) — Tomtom, Jul 05 '12 at 12:51

score 1 · Answer 2 · edited Jul 05 '12 at 12:56

1

To execute insert/delete/update statement, you just need to create SqlCommand and SqlConnection object. DataSet and DataAdapter are useless.

To insert a row:

string cnstr=@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Works.mdf;Integrated Security=True;User Instance=True;Asynchronous Processing=true";

using(SqlConnection con = new SqlConnection(cnstr))
 {
  string sql = "Insert Into Estudante (Codigo,Nome,Apelido) values(@Codigo,@Nome,@Apelido)";
  using(SqlCommand command= new SqlCommand(sql,con))
   {
     command.Parameters.Add("@Codigo",SqlDbType.Int).Value=txtID.Text;
     command.Parameters.Add("@Nome",SqlDbType.VarChar,30).Value=txtNome.Text;
     command.Parameters.Add("@Apelido",SqlDbType.VarChar,30).Value=txtapelido.Text;
     con.Open();
     cmd.ExecuteNonQuery();
     con.Close();
    }
 }

edited Jul 05 '12 at 12:56

mmdemirbas

9,060
5
45
53

answered Jul 05 '12 at 12:51

KV Prajapati

93,659
19
148
186

nothing happen when i try to repeat a record it generates sqlException but when I visit Table with Explorer nothing has been modified; – Oldemiro Henry Williams Baloi Jul 05 '12 at 18:12
Select .mdf file from solution explorer + open the properties windows to set `Copy To Output Directory=Copy if newer` – KV Prajapati Jul 06 '12 at 01:55
try { comando = "Insert Into Estudante (Codigo,Nome,Apelido) values(@ID,@Nome,@Apelido)"; SqlCommand command = new SqlCommand(comando, con); command.Parameters.AddWithValue("@ID",txtID.Text); command.Parameters.AddWithValue("@Nome", txtNome.Text); command.Parameters.AddWithValue("@Apelido", txtapelido.Text); con.Open(); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); con.Close(); MessageBox.Show("Adicionado com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information); } – Oldemiro Henry Williams Baloi Jul 06 '12 at 18:09

score 0 · Answer 3 · answered Jul 06 '12 at 08:33

0

You are calling the the sqlDataAtapter's Fill method to fill the database which is unnecessary. remove that statement and see. That should work.

answered Jul 06 '12 at 08:33

24x7Programmer

474
5
20

sqlcommand execute query not updating deleting and inserting

3 Answers3

Linked