data reader error with scope_identity query?

I get the famous "invalid attempt to read when no data is present" error message when I try to use SqlDataReader to read my scope_identity pk for a query to bring up the row data on the next page. This is my first time using either method, so any tips would be helpful. My code:

insert command ... ; SELECT SCOPE_IDENTITY() AS [lastInsertedProductId]";

    using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
        sqlConn.Open();
        SqlCommand command = new SqlCommand(thisQuery, sqlConn);
        int lastInsertedProductId = Convert.ToInt32(command.ExecuteScalar());
        using (command)
        {
            command.ExecuteNonQuery();
            using (SqlDataReader dr = command.ExecuteReader())
            {
                dr.Read();
                lastInsertedProductId = Convert.ToInt32(dr["lastInsertedProductId"]);
                Response.Redirect("~/View.aspx?开发者_Python百科ProductId" + lastInsertedProductId);
            }
        }
    }
}

Have edited the code, but now I get an "incorrect syntax near '=' on my view page, where I show no actual syntax error

using (SqlConnection editConn = new SqlConnection(connectionString))
    {
        editConn.Open();

        using (SqlCommand command = new SqlCommand(editQuery, editConn))
        {

            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            Label6.Text = dr.GetInt32(0).ToString();

You have to use Scope Identity to part of your insert statement. like...

INSERT INTO table (ColumnName) VALUES ();SELECT SCOPE_IDENTITY();

as in your code.

SqlCommand command = new SqlCommand(thisQuery, sqlConn);

thisQuery variable should have this at end your insert query SELECT SCOPE_IDENTITY();

Edit: I was just checking on the internet and found a way similar to what you are doing, but it will be like...

string idQuery = "Select @@Identity"; 

You can achieve your task using this and I don't know if there is any limitation using this approach. But this will work I think.

Here is URL from where I got this http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

I want thank everyone who posted helpful answers and suggestions; even though I ended up using a different way, help is ALWAYS appreciated. Concerning that different way, I decided to post the code I ended up using, in case someone comes along who needs it (although without parameterized queries, I hope no one ever needs it for more than homework, and even then...). So with the caveat that the following code works but commits the cardinal sin so often decried here, here is the functional solution to my question:

 string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    String thisQuery = "INSERT INTO ProductInstance (CustId, BroId, CustName, SicNaic, CustAdd, CustCity, CustState, CustZip, BroName, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments) VALUES ('" + TbCustId.Text + "', '" + TbBroId.Text + "', '" + TbCustName.Text + "', '" + RblSicNaic.SelectedItem + "', '" + TbCustAddress.Text + "', '" + TbCustCity.Text + "', '" + DdlCustState.SelectedItem + "', '" + TbCustZip.Text + "', '" + TbBroName.Text + "', '" + TbBroAddress.Text + "', '" + TbBroCity.Text + "', '" + DdlBroState.Text + "', '" + TbBroZip.Text + "', '" + DdlEntity.SelectedItem + "', '" + TbCoverage.Text + "','" + TbCurrentCoverage.Text + "','" + TbPrimEx.Text + "','" + TbRetention.Text + "','" + TbEffectiveDate.Text + "','" + TbCommission.Text + "','" + TbPremium.Text + "','" + TbComments.Text + "')";

    string idQuery = "SELECT SCOPE_IDENTITY() AS LastInsertedProductId";
    using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
        sqlConn.Open();
        SqlCommand command = new SqlCommand(thisQuery, sqlConn);

        SqlCommand idCmd = new SqlCommand(idQuery, sqlConn);
        using (command)
        {

            command.ExecuteNonQuery();               
           command.CommandText=idQuery;
           SqlDataReader dr = command.ExecuteReader();
             dr.Read();
                int lastInsertedProductId = Convert.ToInt32(dr[0]);
                Response.Redirect("~/View.aspx?ProductId=" + lastInsertedProductId);

        }
    }
}
protected void CalEffectDate_SelectionChanged(object sender, EventArgs e)
{
    TbEffectiveDate.Text = CalEffectDate.SelectedDate.ToShortDateString();
}    

} You'll note that I was missing an "=" symbol after ?ProductId in my redirect. Devil in the details, no?

and the view page code:

 protected void Page_Load(object sender, EventArgs e)
{
    string x = Request.QueryString["ProductId"];
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    string editQuery = "SELECT CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments FROM ProductInstance WHERE ProductId =" + x;

    using (SqlConnection editConn = new SqlConnection(connectionString))
    {
        editConn.Open();

        using (SqlCommand command = new SqlCommand(editQuery, editConn))
        {

            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            LblCustName.Text = dr.GetString(0);
            LblSicNaic.Text = dr.GetString(1);
            LblCustCity.Text = dr.GetString(2);
            LblCustAddress.Text = dr.GetString(3);
            LblCustState.Text = dr.GetString(4);
            LblCustZip.Text = dr.GetInt32(5).ToString();
            LblBroName.Text = dr.GetString(6);
            LblBroAddress.Text = dr.GetString(7);
            LblBroCity.Text = dr.GetString(8);
            LblBroState.Text = dr.GetString(9);
            LblBroZip.Text = dr.GetInt32(10).ToString();
            LblEntity.Text = dr.GetString(11);
            LblCoverage.Text = dr.GetInt32(12).ToString();
            LblCurrentCoverage.Text = dr.GetInt32(13).ToString();
            LblPrimEx.Text = dr.GetInt32(14).ToString();
            LblRetention.Text = dr.GetInt32(15).ToString();
            LblEffectDate.Text = dr.GetDateTime(16).ToString();
            LblCommission.Text = dr.GetInt32(17).ToString();
            LblPremium.Text = dr.GetInt32(18).ToString();
            LblComments.Text = dr.GetString(19);
            HyperLink1.NavigateUrl = "~/ViewEdit.aspx?ProductId=" + x;
        }

    }
}

}