How to use a IsPostBack for Update command with timing issue

Question

I have asp.net detailsview which inserts data into payment allocation table, however I want to run a update command that update data into s_transaction_enquiry table, which is done in C#. When I run the code, the update command runs first before the insert command so not data is put into the s_transaction_enquiry table.

I have created the update command to run when user click's on the insert button in the details view. The insert command is linked to the detailview's Sql Data source.

I have been told I can use "IsPostBack" property in the page_load but sure how to do this, is there anyone who could help me??

      protected void Page_Load(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {
            string conn = "";
            string sqlCOmmand = "UPDATE s_transaction_enquiry, payment_allocation SET s_transaction_enquiry.payment_amount = payment_allocation.payment_amount, s_transaction_enquiry.payment_ref = payment_allocation.payment_ref, s_transaction_enquiry.payment_received_date = payment_allocation.payment_received_date WHERE payment_allocation.s_invoice_numer = s_transaction_enquiry.s_invoice_number AND payment_allocation.account_number = s_transaction_enquiry.account_number";
            conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
            UpdateRow(conn, sqlCOmmand);
        }


    }

    public void UpdateRow(string connectionString, string insertSQL)
    {
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {

            OleDbCommand command = new OleDbCommand(insertSQL);


            command.Connection = connection;

            try
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }
    }

Insert statement from datasource:

  <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" DeleteCommand="DELETE FROM [payment_allocation] WHERE [payment_ref] = ?" ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" SelectCommand="SELECT * FROM [payment_allocation]" 
             InsertCommand="INSERT INTO [payment_allocation] ([payment_ref], [account_number], [account_ref], [allocate_date], [payment_amount], [payment_received_date], [s_invoice_numer]) VALUES (?, ?, ?, ?, ?, ?, ?)">

Button which invokes the insert statement:

  <asp:Button ID="Button1" runat="server" CausesValidation="True" CommandName="Insert" Text="Create"/>

Answer 1

you should close your connection after every action. you have opened a connection on 'page_load' but didnt close it, and since page_load executes on every postback as well, which means every time you click your button, you attempt to open a connection again without closing it.

also notice that page_load executes before the button_click event handler therefore every time you click your button you open the connection without closing, and the trying to open it again in the click handler.

OleDbConnection is a disposable object, which means it implements the .dispose() function, which also means that it can be used in a using() statement .

the using() statement creates a new object and disposes of it after. you have a pretty good explanation on openning/closing/disposing of OleDbConnection in c# here on microsoft website: OleDbConnection Class

basically in order to adapt it to your code, you would want to do something like this, first put your database handling in a separate function for convenience and call it from page load::

    protected void Page_Load(object sender, EventArgs e)
    {
        string conn = "";
        string sqlCOmmand= "inset blablabla into blablabla";
        conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();


        InsertRow(conn,sqlCOmmand);


    }


//taken from microsoft website
public void InsertRow(string connectionString, string insertSQL)
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        // The insertSQL string contains a SQL statement that 
        // inserts a new row in the source table.
        OleDbCommand command = new OleDbCommand(insertSQL);

        // Set the Connection to the new OleDbConnection.
        command.Connection = connection;

        // Open the connection and execute the insert command. 
        try
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the 
        // code exits the using block.
    }
}

alright, after this part is clear, off to the next one:

i assume you want to insert to the database only when your page loads right? and not after every button click. so you need to direct asp.net to only execute your code if its a first load and not a postback. you would want to change your page_load function to look like that:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string conn = "";
            string sqlCOmmand = "inset blablabla into blablabla";
            conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
            InsertRow(conn, sqlCOmmand);
        }
    }

as for your click handler, you should create another function UpdateRow and do the same with a using() statement and put it in your click handler