CODEWITHSUNDEEP
c#asp.netsqlsql-serversql-update
Mirek
Mirek

Reputation: 53

"executenonquery connection property has not been initialized"

SqlConnection cn = new SqlConnection(@"DataSource=dbedu.cs.vsb.cz\SQLDB;Persist Security Info=True;User ID=*****;Password=*******");

SqlCommand cmd = new SqlCommand();
string finish = DropDownListFi.SelectedValue;
cn.Open();
String Name = Request.QueryString["Name"];
cmd.CommandText = "UPDATE navaznost_ukolu SET finish=@finish where Name='" + Name + "'";
cmd.Parameters.Add(new SqlParameter("@finish", finish));
cmd.ExecuteNonQuery();
cmd.Clone();

The error message

Executenonquery connection property has not been initialized.

Upvotes: 3

Views: 29573

Answers (2)

John Woo
John Woo

Reputation: 263743

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

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460168

The error is self-explanatory, you have not assigned the connection to the command. You can use the constructor:

using(var cn = new SqlConnection(@"DataSource=dbedu.cs.vsb.cz\SQLDB;Persist Security Info=True;User ID=*****;Password=*******"))
using(var cmd = new SqlCommand(
    "UPDATE navaznost_ukolu SET finish=@finish where Name=@Name"
    , cn))
{
   string finish = DropDownListFi.SelectedValue;
   cn.Open();
   String Name = Request.QueryString["Name"];
   cmd.Parameters.AddWithValue("@finish", finish);
   cmd.Parameters.AddWithValue("@Name", Name);
   cmd.ExecuteNonQuery();
}

Note that i've also used a sql-parameter for the Name and using statements to ensure that anything implementing IDisposable gets disposed, even in case of an exception. This will also close the connection.

Upvotes: 2

Related Questions