Insert query on SQL server from C# ASP.NET webapp run twice

Question

0.00/5 (No votes)

See more:

i have an asp.net web application, i have a problem in it that if a page (not all pages) has a button call a c# function that insert a record on a table in sql server 2012 database, sometimes the query runs twice although i execute it one time only.

this problem happens only when i deploy the application on a server and doesn't happen every time as i deployed this app on 4 servers 2 of them has this problem and not always happen and 2 of them works fine, although one of the servers that works fine has 60 GB database (the largest)

the code that use for insert

public static bool InsertToDB(SqlConnection Conn, string InsertStr)
        {
            SqlConnection LocalConnection = new SqlConnection();
            try
            {

                LocalConnection = new SqlConnection("user id=xxx;" +
                                            "password=" + pass + ";server=" + Server + " ;Trusted_Connection=no;" +
                                            "database=" + DataBaseName + "; " +
                                            "connection timeout=1000; MultipleActiveResultSets=True;max pool size=500");
                LocalConnection.Open();

                //DateTime startDate = DateTime.Now;
                SqlDataAdapter MySqlDataAdapter = new SqlDataAdapter();
                SqlCommand MySqlCommand = new SqlCommand();
                SqlCommand MySqlCommandWrite = new SqlCommand();
                MySqlCommandWrite.CommandText = InsertStr;
                MySqlCommandWrite.CommandTimeout = 999999999;
                MySqlCommandWrite.Connection = LocalConnection;
                MySqlCommandWrite.ExecuteNonQuery();
                LocalConnection.Close();
                LocalConnection.Dispose();
                SqlConnection.ClearPool(LocalConnection);
                return true;
            }
            catch (Exception ex)
            {
                LocalConnection.Close();
                LocalConnection.Dispose();
                SqlConnection.ClearPool(LocalConnection);
            }
        }

the code behind the button

protected void BTN_Adddeposit_Click(object sender, EventArgs e)
        {
            double temp;
            int temp2;
            long temp3;
            try
            {


                int DivisionCode = 0;
                int SectorCode = Convert.ToInt32(HttpContext.Current.Session["SectorCode"]);
                int GenDivisionCode = Convert.ToInt32(HttpContext.Current.Session["GenDivisionCode"]);
                CLSDeposit OBJDeposit = new CLSDeposit(DivisionCode,SectorCode,GenDivisionCode);
                OBJDeposit.ClientID = ClientID;
                OBJDeposit.active = 0;
                OBJDeposit.DepositType = 1;
                OBJDeposit.DivisionCode = DivisionCode;
                OBJDeposit.DepositDate=Convert.ToDateTime(TXT_DepositDate.Text);
                OBJDeposit.DepositID=Convert.ToInt32(TXT_DepositID.Text);
                OBJDeposit.DepositValue=Convert.ToDouble(TXT_DepositValue.Text);
                OBJDeposit.InstallmentValue = OBJDeposit.DepositValue;
                OBJDeposit.InvoiceNo = Convert.ToInt64(TXT_DepositFatoora.Text).ToString();
                OBJDeposit.NumOfParts = 1;
                OBJDeposit.RearrangeDate = new DateTime(1900, 1, 1);
                OBJDeposit.IsPaid=0;
                OBJDeposit.Reason = TXT_Notes.Text;
                OBJDeposit.DebitBeforeChange = OBJDeposit.DepositValue;
                OBJDeposit.UserID = Convert.ToInt32(HttpContext.Current.Session["LoginUserID"]);
                bool OperationResult = OBJDeposit.InsertDeposit();

            }
            catch (Exception err)
            {
                //general exception
                ScriptManager.RegisterStartupScript(this, GetType(), "myFunction", "swal('خطأ في إتمام العملية','" + err.Message + "', 'error')", true);
            }
        }

the code that insert

public bool InsertDeposit()
        {
            string InsertDepositCommand = "INSERT INTO [dbo].[Deposits]([ser],[ClientID],[DDate],[DepitRemain],[Depit],[paid],[Paiddate],[UserID],[DivisionCode],[GenDivisionCode],[SectorCode],[DebitBeforeChange],Reason,installment,installmentVal,FormNo,DepType,RearrangeDate,active,RegisterDate) VALUES";
            InsertDepositCommand+="("+this.DepositID+","+this.ClientID+",convert(DATETIME,'" + DepositDateString + "',103)"+","+this.DepositValue+","+this.DepositRemain+","+this.IsPaid;
            InsertDepositCommand += ",convert(DATETIME,'" + PaidDateString + "',103)," + UserID + "," + this.DivisionCode + "," + this.GenDivisionCode + "," + this.SectorCode + "," + DebitBeforeChange + ",'" + Reason + "'," + NumOfParts + "," + InstallmentValue + "," + InvoiceNo + "," + DepositType + ",convert(DATETIME,'" + DepositDateString + "',103)," + active + ",convert(DATETIME,'" + RegiterDateString + "',103))";
            bool OperationResult = SQLDataBase.InsertToDB(SQLDataBase.LocalConnection, InsertDepositCommand);
            return OperationResult;
        }

What I have tried:

i tried to search to find any solution for this or at least any reason but i can't as it is not a persistent problem and doesn't have a persistent condition to happen.

Posted 26-Apr-17 21:46pm

amir tarek

Updated 26-Apr-17 22:07pm

v2

Add a Solution

Comments

Richard Deeming 27-Apr-17 11:01am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-04-26T22:04:00

Without your code, or any access to your data, there isn't really anything we can do to help - you say "I execute it one time only" but we can't even see that one time!

Start by logging activity: every time you insert or update a record, log all the details to a file, and review the file after the problem has occurred. You may get information which helps you work out why it inserted twice.
If that doesn't help, add a trigger to SQL which also logs INSERT operations, and compare the two logs. If the SQL INSERT log doesn't agree with the app log, then there is a "hidden insert" somewhere in your app - either because you didn't log it, or because you have an update-to-DB going on behind the scenes perhaps from a bound table or similar.

Get what information you can, and take a good hard look at it - we can't do anything constructive from this distance!