Updating database from ASP.Net getting error "Connection property has not been initialized"

Question

I am trying to retrieve a value from my database, increment it by 1, and then update the database with this new value.

My code so far is

protected void Button1_Click(object sender, EventArgs e)
{
        string content = Request.QueryString["ContentID"];
        string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["dbmb17adtConnectionString"].ConnectionString;

        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();

        SqlCommand cmd = new SqlCommand("Select likeCount from tbl_Post where tbl_Post.Id="+Convert.ToInt16(content) , conn);

        SqlDataReader dr = cmd.ExecuteReader();

        dr.Read();
        int oldVal = Convert.ToInt16(dr["likeCount"]);
        int newVal = oldVal + 1;

        SqlCommand insert1 = new SqlCommand("update tbl_Post set 
        likeCount="+newVal+ "where tbl_Post.Id=" + content);

        insert1.ExecuteNonQuery();

        conn.Close();
}

I am getting an error on the line insert1.ExecuteNonQuery

ExecuteNonQuery: Connection property has not been initialized.

There is another problem with your code, there is no space between `likeCount=` and `where` in second command, so this is not work. — Aria, Jan 07 '18 at 14:14
The second command has no Connection property set. You can add it to the constructor as you do in the first command. But a part from this trivial error. please take a look to what is Sql Injection and start ASAP to use parameterized queries. By the way the second query has a syntax error — Steve, Jan 07 '18 at 14:14
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) — marc_s, Jan 07 '18 at 14:36

Steve · Answer 1 · 2018-01-07T14:27:47.340

The reason of your error is the missing connection in the second command. You can add it to the SqlCommand constructor as you do in the first command, also you have a missing space in the query text for the second command.

These errors and a more serious error called Sql Injection could be avoided if you use parameters like explained in the code below

Least but probably most important is the fact that you don't need two commands to increment the likeCount field. You can write a single command

protected void Button1_Click(object sender, EventArgs e)
{
    string content = Request.QueryString["ContentID"];
    string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["dbmb17adtConnectionString"].ConnectionString;

    string updateText = @"update tbl_Post 
                          set likeCount=likeCount + 1 
                          where tbl_Post.Id=@id";

    using(SqlConnection conn = new SqlConnection(connStr))
    using(SqlCommand cmd = new SqlCommand(updateText, conn);
    {
        conn.Open();
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt16(content);
        cmd.ExecuteNonQuery();
    }
}

Notice also the presence of the using statement around the disposable objects like connection and commands. This allows you to close and dispose these objects also in case of exceptions.

Updating database from ASP.Net getting error "Connection property has not been initialized"

1 Answers1