SqlDataReader not executing correctly in asp.net page using C#

Question

I've hit the database in another section of my code using SqlDataAdapter but I just need a reader to return one row for me and this code isn't working right. Can someone see where I'm making a mistake?

I'm just trying to assign one label a value from the first column of my row returned. I can't get either alert to pop when its ran either below.

private void loadProcInfo(string procid)
{
    try
    {
        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString);
        SqlCommand query = new SqlCommand("SELECT * FROM dbo.Book1 WHERE ID ='" + procid +"'", con);

        //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('"+query+"');", true);

        using (SqlDataReader procinfoload = query.ExecuteReader())
        {                    
            if (procinfoload.Read())
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('IT WORKED');", true);
                Id.Text = procinfoload.GetValue(0).ToString();
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('not success!');", true);
            }
        }

        con.Close();
    }
    catch (Exception ex)
    {
        ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + ex + "');", true);
        //MessageBox.Show(ex.Message);
    }
}

Answer 1

The main issue is the code does not open the connection before you use the command. There are other issues though.

• You do not parameterize your queries, this exposes your code to possible sql injection attacks.
• You do not wrap your connection in a using block, if an exception is thrown the connection would stay open until garbage collection occurs. This is bad practice.

Code:

private void loadProcInfo(string procid)
{
    try
    {
        using(SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString))
        using(SqlCommand query = new SqlCommand("SELECT * FROM dbo.Book1 WHERE ID = @bookId", con))
        {
            // added parameter
            query.Parameters.Add(new SqlParameter("@bookId", SqlDbType.Int){Value = procid});
            con.Open(); // missing
            //ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('"+query+"');", true);
            using (SqlDataReader procinfoload = query.ExecuteReader())
            {                    
                if (procinfoload.Read())
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('IT WORKED');", true);
                    Id.Text = procinfoload.GetValue(0).ToString();
                }
                else
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('not success!');", true);
                }
            }
        }
    }
    catch (Exception ex)
    {
        ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + ex + "');", true);
        //MessageBox.Show(ex.Message);
    }
}

Answer 2

Open the connection before attempting to execute the reader.

con.Open();