locked
Possible to do a select & update query at the same time? RRS feed

  • Question

  • User-228050481 posted

    Currently I run 2 separate queries to update + retrieve a tickent number. What happens if there're 2 users clicking on the same button at the same time? Could somehow two updates being run first and then both users get back the same ticket number?

    string mySql = "UPDATE TicketCounter SET TicketNumber = (TicketNumber + 1)";
    OleDbConnection myConnection = new OleDbConnection(accessConnStr);
    OleDbCommand myCmd = new OleDbCommand(mySql, myConnection);
    myCmd.Connection.Open();
    myCmd.ExecuteNonQuery();

    mySql = "SELECT TicketNumber FROM TicketCounter"
    myCmd.CommandText = mySql;
    OleDbDataReader myReader = myCmd.ExecuteReader();

     

     

    Monday, November 29, 2010 5:17 PM

Answers

  • User-821857111 posted

    There are two ways to manage what you want to prevent concurrency issues. One is to wrap both statements in one transaction. The other is to use an Autonumber field for your TicketNumber (by far the most sensible approach) and use @@IDENTITY to retrieve the new ticket number: http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

    However, since both statements are executed on the same connection, I doubt that the scenario you fear will ever happen.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 29, 2010 11:39 PM

All replies

  • User-821857111 posted

    There are two ways to manage what you want to prevent concurrency issues. One is to wrap both statements in one transaction. The other is to use an Autonumber field for your TicketNumber (by far the most sensible approach) and use @@IDENTITY to retrieve the new ticket number: http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

    However, since both statements are executed on the same connection, I doubt that the scenario you fear will ever happen.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 29, 2010 11:39 PM
  • User-62323503 posted

    OleDbConnection myConnection = new OleDbConnection(accessConnStr);
    OleDbCommand myCmd = new OleDbCommand(mySql, myConnection);

    mySql = "BEGIN UPDATE TicketCounter SET TicketNumber = (TicketNumber + 1); SELECT TicketNumber FROM TicketCounter; END;"
    myCmd.CommandText = mySql;
    OleDbDataReader myReader = myCmd.ExecuteReader();

    -----------------------------------------------------------------------------------

    Remember to click “Mark as Answer” on the post, if it helps you.

    Tuesday, November 30, 2010 1:22 AM
  • User-228050481 posted

    Multiple queries within same statement unfortunately doesn't work for MS Access 2007. I've opted to use the built-in OleDbTransaction instead. Don't know if it would make any difference.

                    // have both queries run within a transaction
                    myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                    myCmd.Transaction = myTrans;
                    myCmd.ExecuteNonQuery();
                    mySql = "SELECT TicketNumber FROM TicketCounter WHERE " + _FilterClause;
                    myCmd.CommandText = mySql;
                    OleDbDataReader myReader = myCmd.ExecuteReader();
                    myTrans.Commit();
                    // end of transaction

     

     

     

    Tuesday, November 30, 2010 12:22 PM
  • User-821857111 posted

    Don't know if it would make any difference.

    It gets round the fact that Access doesn't support batch statements.


    Tuesday, November 30, 2010 5:56 PM