Using select scoped value in insert statements?

Note: I'm building a practice project where my trainer has forbid me to parameterize. I am aware of the security risks, but the site will not be deployed. I'm using a select scope_identity method to grab an aut开发者_如何学Co-incremented value from the SubmissionId column of my table Submissions.

I want to insert that value into two other tables; I've got newSubID declared as a var and I use it in the insert statements, but I get the error message

The name "newSubID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

What am I missing here?

Here's my code:

protected void BtnSubmit_Click(object sender, EventArgs e)
{
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    String subQuery = "INSERT INTO Submission (Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments) VALUES ('" + TbCoverage.Text + "','" + TbCurrentCoverage.Text + "','" + TbPrimEx.Text + "','" + TbRetention.Text + "','" + TbEffectiveDate.Text + "','" + TbCommission.Text + "','" + TbPremium.Text + "','" + TbComments.Text + "')"
        + "SELECT CAST (SCOPE_IDENTITY() AS int)";

    using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
        sqlConn.Open();
        SqlCommand subCmd = new SqlCommand(subQuery, sqlConn);

        using (subCmd)
        {
            subCmd.ExecuteNonQuery();
            var newSubID = (Int32)subCmd.ExecuteScalar();

            String custQuery = "INSERT INTO Customer (CustId, CustName, SicNaic, CustAdd, CustCity, CustState, CustZip, SubId) VALUES ('" + TbCustId.Text + "', '" + TbCustName.Text + "', '" + RblSicNaic.SelectedItem + "', '" + TbCustAddress.Text + "', '" + TbCustCity.Text + "', '" + DdlCustState.SelectedItem + "', '" + TbCustZip.Text + "', newSubID)";
            String broQuery = "INSERT INTO Broker (BroId, BroName, BroAdd, BroCity, BroState, BroZip, EntityType, SubId) VALUES ('" + TbBroId.Text + "', '" + TbBroName.Text + "', '" + TbBroAddress.Text + "', '" + TbBroCity.Text + "', '" + DdlBroState.SelectedItem + "', '" + TbBroZip.Text + "', '" + DdlEntity.SelectedItem + "', newSubID)";
            SqlCommand custCmd = new SqlCommand(custQuery, sqlConn);
            SqlCommand broCmd = new SqlCommand(broQuery, sqlConn);
            using (custCmd)
            using (broCmd)
            {
                custCmd.ExecuteNonQuery();
                broCmd.ExecuteNonQuery();
                Response.Redirect("~/View.aspx?ProductId=" + newSubID);
            }

This is called up on the next page like so (I have left the errors as they are in the interest of helping whomever may need to see the problem and solutions, which are listed in answers below):

string x = Request.QueryString["SubmissionId"];
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
string editCustQuery = "SELECT CustName, SicNaic, CustCity, CustAdd, CustState, CustZip FROM Customer WHERE SubId =" + x;
using (SqlConnection editConn = new SqlConnection(connectionString))
{
    editConn.Open();

    using (SqlCommand CustCommand = new SqlCommand(editCustQuery, editConn))
    {
        SqlDataReader dr = CustCommand.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();
    }

---

It's because you're not concatenating the newSubID into the custQuery / btoQuery SQL statements, but instead your using the literal text "newSubID" in the statement which is invalid here as it will assume "newSubID" is a column name.

i.e.

String custQuery = "INSERT INTO Customer (CustId, CustName, SicNaic, CustAdd, CustCity,
    CustState, CustZip, SubId) 
VALUES ('" + TbCustId.Text + "', '" + TbCustName.Text + "', '" + RblSicNaic.SelectedItem +   
    "', '" + TbCustAddress.Text + "', '" + TbCustCity.Text + "', '" + 
    DdlCustState.SelectedItem + "', '" + TbCustZip.Text + "'," + 
    newSubID.toString() + ")";

Of course, I'm only giving an answer that uses dynamic SQL like this because of your disclaimer and is not what I'd do in real life!

---

1. Answer of AdaTheDev is correct.

2. I think you have another issue. If you do ExecuteNonQuery and then ExecuteScalar with the same command, you'll insert twice. Use an out-parameter for your scope_id and call only exenonquery or call just exescalar.

           //subCmd.ExecuteNonQuery();
           var newSubID = (Int32)subCmd.ExecuteScalar();