ExecuteNonQuery not working in C#

问题:

I am building a database using Visual Studio 2008 c# and when I'm a trying to insert a new record into my database it appears that ExecuteNonQuery has not initialized. I copy my code, hope anyone can help me in this because I am new.

 private void button1_Click(object sender, EventArgs e)
 {
     SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Usuario\Documents\Visual Studio 2010\Projects\Nova\Nova\Database1.mdf;Integrated Security=True;User Instance=True");
     SqlCommand cmd = new SqlCommand();
     cn.Open();
     cmd.CommandText = "insert into Database1.mdf(Codigo,Nombre,Cantidad,Tipo) values('"+comboBox1.Text+"','"+textBox3.Text+"','"+textBox1.Text+"','"+comboBox2.Text+"')";
     cmd.ExecuteNonQuery();
     cmd.Clone();
     cn.Close();
     MessageBox.Show("Acabas de agregar un producto");
 }

回答1:

You haven't set the connection to your command:

cmd.Connection = cn;

回答2:

You have numerous problems in your code:

• First: The insert into statement requires a target datatable not the name of the MDF file
• Second: Employ the using statement to close and dispose the connections
• Third: Parametrized query to avoid parsing problems and sql injections

• Fourth: You need to associate the connection to the command (Easily done at the SqlCommand constructor)

  using(SqlConnection cn = new SqlConnection(.......))
  using(SqlCommand cmd = new SqlCommand("insert into table_name(Codigo,Nombre,Cantidad,Tipo)" + 
                            "values (@cod, @nom,@can,@tipo)", con))
  {
      cn.Open();
      cmd.Parameters.AddWithValue("@cod", comboBox1.Text);
      cmd.Parameters.AddWithValue("@nom", textBox3.Text);
      cmd.Parameters.AddWithValue("@can", textBox1.Text);
      cmd.Parameters.AddWithValue("@tipo", comboBox2.Text);
      cmd.ExecuteNonQuery();
      MessageBox.Show("Acabas de agregar un producto");
  }
  

EDIT The information provided by the link added by @RemusRusanu below is very important. The use of AddWithValue, whilst handy, could hinder the performance of your query. The correct approach should be the usage of a proper defined SqlParameter with both explicit datatype and parameter size. As an example

SqlParameter p = new SqlParameter("@cod", SqlDbType.NVarChar, 255).Value = comboBox1.Text;
cmd.Parameters.Add(p);

But, of course, this requires that you check the exact datatype and size of your columns.

回答3:

You did not initialize your SqlCommand with a connection. Also, you should really enclose everything here with using. And consider using parametarized commands to avoid SQL Injection.

   private void button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Usuario\Documents\Visual Studio 2010\Projects\Nova\Nova\Database1.mdf;Integrated Security=True;User Instance=True"))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "insert into databaseTableName (Codigo,Nombre,Cantidad,Tipo) values (@Codigo, @Nombre, @Cantidad, @Tipo)";
                cmd.Parameters.AddWithValue("@Codigo", comboBox1.Text);
                cmd.Parameters.AddWithValue("@Nombre", textBox3.Text);
                cmd.Parameters.AddWithValue("@Cantidad", textBox1.Text);
                cmd.Parameters.AddWithValue("@Tipo", comboBox2.Text);
                cmd.Connection = cn; //this was where the error originated in the first place.
                cn.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Acabas de agregar un producto");
            }
        }
    }