Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

stored procedure seems to execute twice

Hi Guys,

I have a problem with my stored proc, it seems to be executing twice, even though the function is only called once from anywhere in my app.

I have two stored procedures that talk to say TABLE 1 and TABLE 2.

TABLE1 works find, no problems at all.
TABLE2 seems to execute twice and every record is duplicated

The only difference between the two tables is that my Primary Key in TABLE 2 i have set to bigint with isIdentity set to true - so bascially, its auto incrementing.

Apart from that there is no difference, so how could it execute twice?

Thanks.

PS. Stored procedures are CLR procedures
0
chudmarek
Asked:
chudmarek
  • 6
  • 4
1 Solution
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
How the procedures are being called ? where from ?
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Can you post the code from your app where this SP is being called?
If the implementation is in a method then check if the same method is being
called multiple times for one request. For example you may be calling that
methods in Page_Load (if is ASP.net app) and then again in some event handler.
0
 
chudmarekAuthor Commented:
Hi Guys

This is the code snippets attached.

I'm fairly noob to sql 2008 btw.

Thanks for the help.


//THIS CALLS THE STORED PROCEDURE
 
try
                {
                    conn.ConnectionString = ConnectionString;
                    SqlCommand cmd = new SqlCommand("addForm", conn);
                    SqlParameter arrayParameter = new SqlParameter();
                    arrayParameter.ParameterName = "@stringvar";
                    arrayParameter.Value = venueDetails;
                    cmd.Parameters.Add(arrayParameter);
                    cmd.CommandType = CommandType.StoredProcedure;
 
                    conn.Open();
                    int rowsaffected = cmd.ExecuteNonQuery();
                    
                    conn.Close();
                }
                catch (Exception Ex)
                {
                    if (conn != null)
                        conn.Dispose();
                    string ErrorMessage = "A error occurred while trying to connect to the server.";
                    ErrorMessage += Environment.NewLine;
                    ErrorMessage += Environment.NewLine;
                    ErrorMessage += Ex.Message;
                    MessageBox.Show(this, ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
 
// THIS IS THE STORED PROCEDURE
 
        public static void addForm(string venueAdd)
        {
            using (SqlConnection conn = new SqlConnection("context connection = true"))
            {
                string[] venuesBroken = SplitText(venueAdd, "\r\n");
                string[] IDBroken = null;
                conn.Open();
                SqlCommand cmd;
                int intRowsAffetced = 0;
 
                for (int a = 0; a < venuesBroken.Length; a++)
                {
                    if (venuesBroken[a] == "")
                    {
                    }
                    else
                    {
                        IDBroken = venuesBroken[a].Split(',');
                       // try
                       // {
                            
                            cmd = new SqlCommand("Insert INTO Form (Column1,Column2,Column3,Column4) VALUES  ('" + IDBroken[0] + "', '" + IDBroken[1] + "', '" + IDBroken[2]
                                                + "', '" + IDBroken[3] + "')", conn);
                                                
                            SqlDataReader rdr = cmd.ExecuteReader();
                            SqlContext.Pipe.Send(rdr);
                            rdr.Close();
                            intRowsAffetced = intRowsAffetced + (int)cmd.ExecuteNonQuery();
                       // }
                       // catch
                       // {
                       // }
 
                    }
                }                
 
            }
 
        }

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
where is your addForm(string venueAdd) getting called from  ?
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Do you got StoredProc addFrom (can see the code), also can you confirms where addForm(string venueAdd)  getting called from ?
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Ok..seems all good.  
How this stored proc is getting triggred from you app (on button click ...or other event .. which might getting triggered twice)
0
 
chudmarekAuthor Commented:
Hi Guys,

stored procedure is getting triggered from a button click, and that is the only place in the entire app that this SP is getting called.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's normal, you have:

SqlDataReader rdr = cmd.ExecuteReader();
and
intRowsAffetced = intRowsAffetced + (int)cmd.ExecuteNonQuery();

so, you run the procedure indeed twice!!!
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Oops didn't see that. :-)
0
 
chudmarekAuthor Commented:
Oh btw i have even put a message box  before and after the executequery() to see if it executes more than once, and nada. Execution only happens once.
0
 
chudmarekAuthor Commented:
Hi, Thanks so much for that, what an oversight!!....
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now