Query string to call up row data from SCOPE_IDENTITY

I've got a submission page for users to input data. Then I redirect to a view page so they can view their input. Sessions are out, since this would be a high volume site, but I used the scope identity method at the end of my submission command. How do I query the scope ID value so that the row data is displayed in my view page?

Code is below: it's a training project, and I was expressly forbidden to parameterize in the interest of simplicity. But yes, I know paramaterizing is the way to go.

protected void Button1_Click(object sender, EventArgs e)
{
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    String thisQuery = "INSERT INTO ProductInstance (CustId, CustName, SicNaic, CustAdd, CustCity, CustState, CustZip, BroId, BroName, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments) VALUES ('" + TextBox19.Text + "', '" + TextBox1.Text + "', '" + RadioButtonList1.SelectedItem + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + DropDownList1.SelectedItem + "', '" + TextBox4.Text + "', '" + TextBox18.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "', '" + DropDownList2.SelectedItem + "', '" + TextBox8.Text + "', '" + DropDownList3.SelectedItem + "','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox11.Text + "','" + TextBox12.Text + "','" + TextBox20.Text + "','" + TextBox14.Text + "','" + TextBox15.Text + "','" + TextBox16.Text + "'); SELECT SCOPE_IDENTITY() AS [lastInsertedProductId]";

    using (SqlConnection sqlConn = new SqlConnection(co开发者_如何学PythonnnectionString))
    {
        sqlConn.Open();

        using (SqlCommand command = new SqlCommand(thisQuery, sqlConn))
        {
            int lastInsertedProductId = Convert.ToInt32(command.ExecuteScalar());
        }
    }
    Response.Redirect("~/View.aspx");

and then the view page code is here:

protected void Page_Load(object sender, EventArgs e)
{
    string x = Request.QueryString["ProductId"];
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    string editQuery = "SELECT CustId, CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroId, 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();
            Label6.Text = dr.GetInt32(0).ToString();

Try moving lastInsertedProductId up in scope and

Response.Redirect("~/View.aspx?ProductId" + lastInsertedProductId );

But seriously, look into parameterized statements and http://bobby-tables.com/

You would make a stored procedure which does the work and in there you would get the SCOPE_IDENTITY.

Right after your insert.