The sqlparameter is already contained by another sqlparametercollection -Error

No.of Views5458
Bookmarked0 times
Downloads 
Votes0
By  RRaveen   On  06 Jan 2011 21:01:53
Tag : ADO.NET , Miscellaneous
I have faced issues when I was debugging my project with SqlCommand and Parameters collection. Suddenly application throws error “the sqlparameter is already contained by another sqlparametercollection”.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

I have faced issues when I was debugging my project with SqlCommand and Parameters collection. Suddenly application throws error “the sqlparameter is already contained by another sqlparametercollection”. Finally I found solutions and here is that for you.

Why this error?

Let’s say you have created a SqlCommand and added few no of parameter in your command with query and then execute command with ExecuteNonQuery. Finally you have closed the connection as well. Even if you have closed the connection, SqlCommand object was there in memory, with parameters collection.

Problem Code

private void Save(string id, string name)
    {

        SqlConnection connection = new SqlConnection("connstring");
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_emp_save";
            SqlParameter[] paramscol = new SqlParameter[2];
            // set values to params.
            cmd.Parameters.AddRange(paramscol);
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            cmd.ExecuteNonQuery();

        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }
    }

In above code, we have closed connection, but if calls save method again .Net throw error the sqlparameter is already contained by another sqlparametercollection.Because command object still does not disposed have give best two solutions for this problem below.


1.    Clear parameters in command object
After call ExecuteNonQuery method, you have to call clear method as well.

cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

2.    Compose code within using block

private void Save(string id, string name)
    {

        try
        {
            using (SqlConnection connection = new SqlConnection("connstring"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_emp_save";
                    SqlParameter[] paramscol = new SqlParameter[2];
                    // set values to params.
                    cmd.Parameters.AddRange(paramscol);
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception)
        {

            throw;
        }

    }

I hope this is best solution to ignore unnecessary memory leak and connection leaks as well. Out of the using block, no more command or connection objects.
Hopes help and save your lots of times. Thank you.

 
Sign Up to vote for this article
 
About Author
 
RRaveen
Occupation-Software Engineer
Company-TGS
Member Type-Gold
Location-Singapore
Joined date-03 Jun 2009
Home Page-codegain.com
Blog Page-www.codegain.com
- B.Sc. degree in Computer Science. - 4+ years experience in Visual C#.net and VB.net - Obsessed in OOP style design and programming. - Designing and developing Network security tools. - Designing and developing a client/server application for sharing files among users in a way other than FTP protocol. - Designing and implementing GSM gateway applications and bulk messaging. - Windows Mobile and Symbian Programming - Having knowledge with ERP solutions
 
 
Other popularSectionarticles
    Comments
    By:dfgdfgDate Of Posted:8/29/2012 8:02:51 AM
    gddsfg
    sfdgfdgdfgfddgdf
    By:GoodDate Of Posted:2/24/2012 11:58:10 AM
    Great
    It is help me a lot.
    Leave a Reply
    Title:
    Display Name:
    Email:
    (not display in page for the security purphase)
    Website:
    Message:
    Please refresh your screen using Ctrl+F5
    If you can't read this number refresh your screen
    Please input the anti-spam code that you can read in the image.
    ^ Scroll to Top