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

You should always include the error message in your question instead of assuming we know what the issue is. — mason
– mason, Commented Nov 29, 2016 at 21:54

Darren Kopp · Accepted Answer · 2016-11-29 20:45:56Z

4

Open the connection before attempting to execute the reader.

con.Open();

answered Nov 29, 2016 at 20:45

Darren Kopp

77.8k9 gold badges81 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Graham · Accepted Answer · 2017-09-22 02:23:41Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related