none
Access not updating until restarting my application RRS feed

  • Question

  • Hello-

    I have been tasked with writing a rather large DB app for work. Outside of some small projects for school I am not very familiar with OleDb functions etc.

    Essentially, the problem I am having is that my application will insert/delete/use all data properly, however any new records I insert are not available for use until after the application has restarted. Once the application has restarted all new records that had been added are available.

    Any help would be much appreciated as I have 13 tables of the database that are all doing the same thing to me.

    Tuesday, August 6, 2019 1:51 PM

All replies

  • Please post the code that is inserting the data and the code that is retrieving the data. Personally it sounds like a connection lifetime issue to me but the code should make this more clear.

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, August 6, 2019 3:31 PM
    Moderator
  • I realize some of this might be a little incomplete since it's copied out of the middle of the code for one of the forms, and is still a work in progress. Trying to nail down the data issue before getting too much farther.

    Thanks Again

    private void DelBtn_Click(object sender, EventArgs e)
            {
                try
                {
                    if (userTableViewer.SelectedCells.Count > 0)
                    {
                        int i = userTableViewer.CurrentCell.RowIndex;
                        if (userTableViewer.Rows.Count > 1 && i != userTableViewer.Rows.Count - 1)
                        {
                            cmd.CommandText = "DELETE FROM Users WHERE UserName ='" + userTableViewer.SelectedRows[0].Cells[0].Value.ToString() + "'";
                            loginConnection.Open();
                            cmd.Connection = loginConnection;
                            cmd.ExecuteNonQuery();
                            loginConnection.Close();
                            userTableViewer.Rows.RemoveAt(userTableViewer.SelectedRows[0].Index);
                            MessageBox.Show("Row Deleted");
    
                        }
                       
                    }
                }
                catch (Exception except)
                {
                    string exception = "Unable to delete entry. Please select the entire row.";
                    Console.WriteLine("Exception: " + except);
                    MessageBox.Show(exception, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
    
    
            }

            private void submitBtn_Click(object sender, EventArgs e)
            {
    
                try
                {
                    if (newUserBox.Text.Length > 0 && newPwdBox.Text.Length > 0 && repeatPwdBox.Text == newPwdBox.Text)
                    {
                        string userName = newUserBox.Text.ToString();
                        string passWord = newPwdBox.Text.ToString();
                        string insertQuery = "INSERT INTO Users(UserName,[Password])" + "VALUES(@userName, @passWord)";
    
                        cmd.CommandText = insertQuery;
                        cmd.Parameters.AddWithValue("@userName", userName);
                        cmd.Parameters.AddWithValue("@passWord", passWord);
                    }
                   
                }
    
                catch (OleDbException)
                {
                    MessageBox.Show("Connection Failed");
                }
                finally
                {
                    try
                    {
                        
    
                        loginConnection.Open();
    
                        cmd.Connection = loginConnection;
                        cmd.ExecuteNonQuery();
                         
                        loginConnection.Close();
                        MessageBox.Show("New User Added Successfully.");
    
                        
    
                    }
                    catch (OleDbException)
                    {
                        MessageBox.Show("Duplicate User");
                        loginConnection.Close();
                    }
                    
                }
    
    
            }


    Thursday, August 8, 2019 1:21 PM
  • While it doesn't really explain why Access isn't updating I can see that your object lifetimes are incorrect. You should almost never store connections, commands, readers, etc in fields. Their lifetimes are well defined. Failing to properly clean them up will result in excess connections to the data source which can (in the case of Access) cause issues.

    private void DelBtn_Click(object sender, EventArgs e)
    {
       try
       {
          if (userTableViewer.SelectedCells.Count > 0)
          {
             int i = userTableViewer.CurrentCell.RowIndex;
             if (userTableViewer.Rows.Count > 1 && i != userTableViewer.Rows.Count - 1)
             {
                using (var conn = GetLoginConnection())
                {
                   var cmd = conn.CreateCommand();
                   cmd.CommandText = "DELETE FROM Users WHERE UserName ='@name'";
                   cmd.Parameters.Add("@name",  userTableViewer.SelectedRows[0].Cells[0].Value.ToString());
                   
                   conn.Open();
                   cmd.ExecuteNonQuery();
                };                    
                       userTableViewer.Rows.RemoveAt(userTableViewer.SelectedRows[0].Index);
               MessageBox.Show("Row Deleted");
            }
         }
      }
      catch (Exception except)
      {
          string exception = "Unable to delete entry. Please select the entire row.";
          Console.WriteLine("Exception: " + except);
          MessageBox.Show(exception, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
      };
    }

    Couple of changes here.

    1) Connections are created, used and closed as soon as possible. This is very important for error handling, concurrency and resource management. The `GetLoginConnection` should create your underlying connection object and return it.

    2) Switched from directly putting text into a query (SQL injection attack) to using a parameter. Here I'm using the syntax `@` for SQL. You said you're using OleDb provider so the syntax should be the same.

    3) The command is created, used and destroyed in the same scope as the connection. It should only be reused if you are executing multiple commands in the same function.

    4) Get rid of any fields for connections and commands. Create them on demand.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, August 8, 2019 2:00 PM
    Moderator
  • Came back to answer my own question, turns out that the problem was in the way that I had configured my database app to use the output directory. After I changed these settings the program worked perfectly.

    Monday, February 17, 2020 7:57 PM