Get New Values from DataReader Insert

[Evie]

Good evening!

I have a function to create a new item and allow the user to copy the associated items of an existing item.

I have inserted the new item with a datareader and I have retrieved the associated items of the existing.

What I am needing is the new id created by the Insert of the new item.

My code looks like total crap, but here is the whole thing (I have the associated insert commented out because I don't have the new id):

Any advice on refactoring or a better way to do it or whatever is much appreciated and welcome.

protected void dtlBillingDigits_ItemInserting(Object sender, DetailsViewInsertEventArgs e)
    {

        SqlConnection conn = null;
        SqlConnection connNew = null;
        SqlConnection connCopy = null;
        SqlConnection connNewAgentBilling = null;
        SqlDataReader rdr = null;
        SqlDataReader rdrNew = null;
        SqlDataReader rdrCopy = null;
        SqlDataReader rdrNewAgentBilling = null;                
        String name = Request.ServerVariables["AUTH_USER"];

        // create and open a connection object
		conn = new SqlConnection(GetConnectionStringByName("Univlicval"));
		conn.Open();

        //First check to see if the number already exists
        try
        {          
            // 1. create a command object identifying
            // the stored procedure
            SqlCommand cmd = new SqlCommand("sp_Univlicval_checkBillingDigit", conn);

            // 2. set the command object so it knows
            // to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            // will be passed to the stored procedure
            TextBox txtBD = (TextBox)dtlBillingDigits.FindControl("txtBilling");
            String billing = txtBD.Text;


            cmd.Parameters.Add(new SqlParameter("@billing", billing)); 
            
            // execute the command
            rdr = cmd.ExecuteReader();

            Boolean result = false;
            while (rdr.Read())
            {
                result = true;
            }

            if (result) //Entry already exists
            {
                Response.Write("<script>alert('" + billing + " Already Exists')</script>");
            }
            else //create new entry
            {
                // create and open a connection object
                connNew = new SqlConnection(GetConnectionStringByName("Univlicval"));
                connNew.Open();

                try
                {

                    // 1. create a command object identifying
                    // the stored procedure
                    SqlCommand cmdNew = new SqlCommand("sp_Univlicval_newBillingDigit", connNew);

                    // 2. set the command object so it knows
                    // to execute a stored procedure
                    cmdNew.CommandType = CommandType.StoredProcedure;

                    // 3. add parameter to command, which
                    // will be passed to the stored procedure
                    TextBox txtBD2 = (TextBox)dtlBillingDigits.FindControl("txtBilling");
                    String billing2 = txtBD2.Text;

                    TextBox txtDesc = (TextBox)dtlBillingDigits.FindControl("txtDescription");
                    String desc = txtDesc.Text;

                    String action = "Billing digit " + billing2 + " " + desc + " added to the database.";

                    //Response.Write("<script>alert('Billing :" + billing2 + "')</script>");
                    //Response.Write("<script>alert('Description :" + desc + "')</script>");

                    cmdNew.Parameters.Add(new SqlParameter("@billing", billing2));
                    cmdNew.Parameters.Add(new SqlParameter("@description", desc));
                    cmdNew.Parameters.Add(new SqlParameter("@name", name));
                    cmdNew.Parameters.Add(new SqlParameter("@action", action));

                    // execute the command
                    rdrNew = cmdNew.ExecuteReader();
                    Response.Write("<script>alert('New ID :" + rdrNew + "')</script>");
                    while (rdrNew.Read())
                    {
                        Response.Write("<script>alert('New ID :" + rdrNew + "')</script>");
                    }
                }
                finally
                {
                    if (connNew != null)
                    {
                        connNew.Close();
                    }
                    if (rdrNew != null)
                    {
                        rdrNew.Close();
                    }
                }

                //check to see if they want a current schema copied
                Button a = sender as Button;
                CheckBox b = (CheckBox)dtlBillingDigits.FindControl("chkCopyExisting");

                if (b != null)   
                {
                    DropDownList c = (DropDownList)b.Parent.FindControl("ddlBillingDigits");
                    if (b.Checked)  //copy existing billng digit's agent associations
                    {
                        Response.Write("<script>alert('Checked')</script>");

                        <...snip commented out associated items insert>
                    }
                }
            }
            
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
            if (rdr != null)
            {
                rdr.Close();
            }
        }
    }

[ssystems]

After you execute a non query clear the parameters. Then select the @@IDENTITY. Execute it as scalar