Cmd executenonquery ошибка

asked Apr 6, 2013 at 16:02

the problem with your current code is that you have not set the Connection property of the SqlCommand object. Try this,

SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;

and you must also parameterized the values set on the name

String Name = Request.QueryString["Name"];
cmd.CommandText = "UPDATE navaznost_ukolu SET finish=@finish where Name=@name";
cmd.Parameters.Add(new SqlParameter("@finish", finish));
cmd.Parameters.Add(new SqlParameter("@name", Name));

FULL CODE

string finish = DropDownListFi.SelectedValue;
String Name = Request.QueryString["Name"];
string connStr = @"DataSource=dbedu.cs.vsb.cz\SQLDB;
                   Persist Security Info=True;
                   User ID=*****;
                   Password=*******";
string sqlStatement = @"UPDATE navaznost_ukolu 
                        SET    finish = @finish 
                        WHERE  Name = @Name";

using (SqlConnection conn = new SqlConnection(connStr))
{
    using(SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = sqlStatement;
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.Add(new SqlParameter("@finish", finish));
        cmd.Parameters.Add(new SqlParameter("@name", Name));

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch(SqlException e)
        {
            // do something with the exception
            // do not hide it
            // e.Message.ToString()
        }
    }
}

For proper coding

• use using statement for propr object disposal
• use try-catch block to properly handle objects

answered Apr 6, 2013 at 16:03

Tim SchmelterTim Schmelter

451k74 gold badges686 silver badges939 bronze badges

asked Jun 3, 2013 at 18:29

You haven’t set the connection to your command:

cmd.Connection = cn;

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.

answered Jun 3, 2013 at 18:39

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

answered Jun 3, 2013 at 18:33