www.webdeveloper.com
Results 1 to 2 of 2

Thread: Get New Values from DataReader Insert

  1. #1
    Join Date
    Sep 2003
    Posts
    94

    Get New Values from DataReader Insert

    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.

    Code:
    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();
                }
            }
        }

  2. #2
    Join Date
    Oct 2009
    Posts
    658
    After you execute a non query clear the parameters. Then select the @@IDENTITY. Execute it as scalar
    Good Luck

    Santos Systems

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles