Database not updating in certain cases.

Question

Hi, this question is related to my other previous question what-is-proper-way-to-update-the-viewui-after-data.html

After going through tests I've noticed that when I add a new item to my billdata database using the + button and then save in my tabitem titled Report and then I go on to the tabitem Forwarding, select those newly added item/items in the datagrid and hit the Export2DOCX button, even though the related views/UI's are updated but the data is not written to the actual databases (I've applied the solution of @Hui Liu-MSFT of my previous question mentioned above).

So, what I had to do is to the restart the app and then do Export2DOCX on these newly added items to get it done properly. Do note that if I edit any previously existing item in Report tab by simply modifying some data and hitting the save button, there seems to be no problem, only when I add a completely new entry to the database and then go on to the tabitem Forwarding and perform Export2DOCX while selecting those newly saved items this problem occurs.

Then after restarting the app

How to fix this?

The sample project for testing can be found here and also in the previous question

Answer 1

Hi,
change your code for inserting bill:

        internal static int InsertBill(Bills bill)  
        {  
            try  
            {  
                const string query = "INSERT INTO billdata(Party,BillNo, BillDt,Amt,DueDt,PaidOn,Remarks) VALUES(@Party, @BillNo,@BillDt,@Amt,@DueDt,@PaidOn,@Remarks) returning id;";  
  
                var args = new Dictionary<string, object>  
                {  
                    {"@Party", bill.Party},  
                    {"@BillNo", bill.BillNo},  
                    {"@BillDt", bill.BillDt},  
                    {"@Amt", bill.Amt2},  
                    {"@DueDt", bill.DueDt},  
                    {"@PaidOn", bill.PaidOn},  
                    {"@Remarks", bill.Remarks},  
                };  
        var id = ExecuteWrite(query, args);  
        bill.AId = id;  
        return id;  
            }  
            catch (Exception ex)  
            {  
                throw ex;  
            }  
  
        }  

Another version:

    internal static SQLiteConnection GetConnection() { return new SQLiteConnection("Data Source=test.db");}  
  
    internal static int InsertBill(Bills bill)  
    {  
      int numberOfRowsAffected;  
      using (var con = GetConnection())  
      {  
        con.Open();  
        using (var cmd = new SQLiteCommand("INSERT INTO billdata(Party,BillNo, BillDt,Amt,DueDt,PaidOn,Remarks) VALUES(@Party, @BillNo,@BillDt,@Amt,@DueDt,@PaidOn,@Remarks);", con))  
        {  
          cmd.Parameters.AddWithValue("@Party", bill.Party);  
          cmd.Parameters.AddWithValue("@BillNo", bill.BillNo);  
          cmd.Parameters.AddWithValue("@BillDt", bill.BillDt);  
          cmd.Parameters.AddWithValue("@Amt", bill.Amt2);  
          cmd.Parameters.AddWithValue("@DueDt", bill.DueDt);  
          cmd.Parameters.AddWithValue("@PaidOn", bill.PaidOn);  
          cmd.Parameters.AddWithValue("@Remarks", bill.Remarks);  
          numberOfRowsAffected = cmd.ExecuteNonQuery();  
        }  
        if (numberOfRowsAffected == 1) using (var cmd = new SQLiteCommand("SELECT last_insert_rowid();", con))  
            bill.Id = (int)(Int64)cmd.ExecuteScalar();  
      }  
      return numberOfRowsAffected;  
    }  

Answer 2

Hi,
the project on GitHub is incomplete. A test database is required for testing. DLL's are also missing.

The project lacks a debug/trace to track the program flow. Return codes (affected rows) are also not evaluated.

The CreateDoc method is very confusing. There are a variety of nested and consecutive if statements, e.g. on line 825:

if (billsGrid.SelectedItems.Count > 0 && billsGrid.SelectedItems.Count > 8)

If billsGrid.SelectedItems.Count is equal to 0, the else branch is executed, which then executes:

var selectedBills = billsGrid.SelectedItems

After that, there is no check of selectedBills for null.

In order to get an overview of the program flow here, the principle of a state machine should be used, ideally with a switch statement.