none
Reflect database changes in listview RRS feed

  • Question

  • Hello

    I've been working on a checkbook application. The Main screen (frmMainScreen) contains a listview that shows the current record of all transactions. From the mainscreen, the user clicks an Add button and a new form (frmNewTransaction) displays. This allows the user to add information for a new transaction to be added to the database. When the user clicks the add button the data is inserted into the database. My problem is that when the New Transaction form is closed, the data in the listview should be updated immediately to show the newly added transaction, but this doesn't occur immediately. I have to click the Add button again and close the form and the listview will update.

     

    This is my methodology:

     

    On Main Screen Load, connect to database and fill the dataAdapter with the datatable.

    Display the data in the listview.

     

    Add button Click Event:

    Clear the DataTable

    Show New Transaction in dialog

    Fill dataAdapter with Datable again

    Call the ShowCurrentRecord function

     

     

    This seems to work, only once I Add a new transaction, I have to Load the New Transaction form again and Close it before the listview is refreshed.

    This is driving me crazy, someone please help!!! Thanks!

    Here is the code:

     

    New Transaction Button Click

    private void btnNewTransaction_Click(object sender, EventArgs e)

    {

    dtAccount.Clear();

    frmNewTransaction AddTransaction = new frmNewTransaction();

    AddTransaction.MdiParent = this.MdiParent;

    AddTransaction.ShowDialog();

    m_daDataAdapter.Fill(dtAccount);

    this.ShowCurrentRecord();

    }

     

    ShowCurrentRecord Function

    private void ShowCurrentRecord()

    {

    if (dtAccount.Rows.Count == 0)

    {

    listView1.Clear();

    listView1.Items.Clear();

    }

    listView1.Clear();

    listView1.Items.Clear();

    listView1.Columns.Add("Date", 150, HorizontalAlignment.Center);

    listView1.Columns.Add("Payee", 150, HorizontalAlignment.Center);

    listView1.Columns.Add("Amount", 150, HorizontalAlignment.Center);

    listView1.Columns.Add("Notes", 150, HorizontalAlignment.Center);

    listView1.Columns.Add("Balance", 150, HorizontalAlignment.Center);

    for (m_rowPosition = 0; m_rowPosition <= dtAccount.Rows.Count-1; m_rowPosition++)

    {

    ListViewItem item = new ListViewItem();

    item.UseItemStyleForSubItems = true;

    item.Text = dtAccount.Rows[m_rowPosition]["Date&Time"].ToString();

    item.SubItems.Add(dtAccount.Rows[m_rowPosition]["Payee"].ToString());

    item.SubItems.Add(dtAccount.Rows[m_rowPosition]["Amount"].ToString());

    item.SubItems.Add(dtAccount.Rows[m_rowPosition]["Notes"].ToString());

    item.SubItems.Add(dtAccount.Rows[m_rowPosition]["Balance"].ToString());

    //Alternate the color of the rows in the Listview

    if (m_rowPosition % 2 == 0)

    item.BackColor = Color.LightBlue;

    else

    item.BackColor = Color.LightGray;

    listView1.Items.Add(item);

    }

    }

    Thursday, May 29, 2008 3:46 AM

Answers

  • I finally found out what the problem is!! It was in the New Transaction Form code.

    I was closing the connection at the wrong time. The way I had it, I was calling m_cnADONetConnection.Close() in the FormClosing action. This caused the form to have to be opened again and closed before the next statement that updates the listview (after showing the form) would execute. I don't really see why that is but I just closed the connection right after I updated the dataAdapter in the form. This is the code:

    ...
                  dtAccount.Rows.Add(drNewRow);

                    m_daDataAdapter.InsertCommand = m_cnADONetConnection.CreateCommand();
                    m_daDataAdapter.InsertCommand.CommandText = "insert into Transactions ([Date&Time], [Payee], [Amount], [Notes], [Balance]) values (?,?,?,?,?)";
                    m_daDataAdapter.InsertCommand.Parameters.Add("Date&Time", OleDbType.Char, 0, "Date&Time");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Payee", OleDbType.Char, 0, "Payee");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Amount", OleDbType.Char, 0, "Amount");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Notes", OleDbType.Char, 0, "Notes");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Balance", OleDbType.Char, 0, "Balance");

                    m_daDataAdapter.UpdateCommand = m_cnADONetConnection.CreateCommand();
                    m_daDataAdapter.UpdateCommand.CommandText = "update Transactions" +
                        "set Date&Time = ? " +
                        "set Payee = ? " +
                        "set Amount = ? " +
                        "set Notes = ? " +
                        "set Balance =  ? ";

                    m_daDataAdapter.Update(dtAccount);
                    m_cnADONetConnection.Close();


                    this.Close();
                    this.Dispose();


    Thursday, May 29, 2008 8:13 PM

All replies

  •  

    Seems that there's something wrong in your AddTransaction form. Would you please share the code of OK button in AddTransaction?
    Thursday, May 29, 2008 7:19 AM
  • Hello,
    Thanks for the quick response. Here is the Add Transaction form code:

            private void btnAdd_Click(object sender, EventArgs e)
            {
               

                //Force user to select the Transaction Type
                if (grpTypeOfTransaction.Text == "")
                    MessageBox.Show("Please specify the type of transaction.", "Specify Transaction Type", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                  
               
                //Force user to enter a Transaction Amount and a Payee
                else if (txtAmount.Text == "" || txtPayee.Text == "")
                    MessageBox.Show("Please enter a correct amount and payee to add new transaction",
                        "Enter Amount and Payee", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                else
                {
                   
                    DataRow drNewRow = dtAccount.NewRow();
                    drNewRow["Date&Time"] = dateTimePicker1.Text;
                    drNewRow["Payee"] = txtPayee.Text;
                    drNewRow["Amount"] = txtAmount.Text;
                    drNewRow["Notes"] = rtbNotes.Text;

                    //Set the row position to the last position of the database for calculating the Balance
                    if (dtAccount.Rows.Count == 0)
                        m_rowPosition = 0;
                    else
                        m_rowPosition = dtAccount.Rows.Count - 1;

                    //Get current balance float and Transaction amount float
                    string Balance;
                    if (dtAccount.Rows.Count == 0)
                        Balance = "0";
                    else
                        Balance = (dtAccount.Rows[m_rowPosition]["Balance"].ToString());

                    if (Balance == "")
                        Balance = "0.00";
                    float CurrentBalance = Convert.ToSingle(Balance);
                    float Amount = Convert.ToSingle(txtAmount.Text.ToString());

                    //Determine how to calculate the balance and add it to the new row
                    switch (grpTypeOfTransaction.Text)
                    {
                        case "Deposit":
                            drNewRow["Balance"] = DepositToBalance(CurrentBalance, Amount);
                            break;
                        case "Transfer To":
                            drNewRow["Balance"] = DepositToBalance(CurrentBalance, Amount);
                            break;
                        case "ATM":
                            drNewRow["Balance"] = WithdrawFromBalance(CurrentBalance, Amount);
                            break;
                        case "Check":
                            drNewRow["Balance"] = WithdrawFromBalance(CurrentBalance, Amount);
                            break;
                        case "Check Card":
                            drNewRow["Balance"] = WithdrawFromBalance(CurrentBalance, Amount);
                            break;
                        case "Electronic Debit":
                            drNewRow["Balance"] = WithdrawFromBalance(CurrentBalance, Amount);
                            break;
                        case "Transfer From":
                            drNewRow["Balance"] = WithdrawFromBalance(CurrentBalance, Amount);
                            break;
                        default:
                            MessageBox.Show("Please specify the type of transaction.", "Specify Transaction Type", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            break;

                    }
                    dtAccount.Rows.Add(drNewRow);

                    m_daDataAdapter.InsertCommand = m_cnADONetConnection.CreateCommand();
                    m_daDataAdapter.InsertCommand.CommandText = "insert into Transactions ([Date&Time], [Payee], [Amount], [Notes], [Balance]) values (?,?,?,?,?)";
                    m_daDataAdapter.InsertCommand.Parameters.Add("Date&Time", OleDbType.Char, 0, "Date&Time");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Payee", OleDbType.Char, 0, "Payee");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Amount", OleDbType.Char, 0, "Amount");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Notes", OleDbType.Char, 0, "Notes");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Balance", OleDbType.Char, 0, "Balance");

                    m_daDataAdapter.UpdateCommand = m_cnADONetConnection.CreateCommand();
                    m_daDataAdapter.UpdateCommand.CommandText = "update Transactions" +
                        "set Date&Time = ? " +
                        "set Payee = ? " +
                        "set Amount = ? " +
                        "set Notes = ? " +
                        "set Balance =  ? ";

                    m_daDataAdapter.Update(dtAccount);
                    m_daDataAdapter.Update(dtAccount);


                    this.Close();
                    this.Dispose();
                }
            }

            private void frmNewTransaction_FormClosing(object sender, FormClosingEventArgs e)
            {
                m_cnADONetConnection.Close();
                m_cnADONetConnection.Dispose();

            }

    Thursday, May 29, 2008 2:26 PM
  • I finally found out what the problem is!! It was in the New Transaction Form code.

    I was closing the connection at the wrong time. The way I had it, I was calling m_cnADONetConnection.Close() in the FormClosing action. This caused the form to have to be opened again and closed before the next statement that updates the listview (after showing the form) would execute. I don't really see why that is but I just closed the connection right after I updated the dataAdapter in the form. This is the code:

    ...
                  dtAccount.Rows.Add(drNewRow);

                    m_daDataAdapter.InsertCommand = m_cnADONetConnection.CreateCommand();
                    m_daDataAdapter.InsertCommand.CommandText = "insert into Transactions ([Date&Time], [Payee], [Amount], [Notes], [Balance]) values (?,?,?,?,?)";
                    m_daDataAdapter.InsertCommand.Parameters.Add("Date&Time", OleDbType.Char, 0, "Date&Time");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Payee", OleDbType.Char, 0, "Payee");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Amount", OleDbType.Char, 0, "Amount");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Notes", OleDbType.Char, 0, "Notes");
                    m_daDataAdapter.InsertCommand.Parameters.Add("Balance", OleDbType.Char, 0, "Balance");

                    m_daDataAdapter.UpdateCommand = m_cnADONetConnection.CreateCommand();
                    m_daDataAdapter.UpdateCommand.CommandText = "update Transactions" +
                        "set Date&Time = ? " +
                        "set Payee = ? " +
                        "set Amount = ? " +
                        "set Notes = ? " +
                        "set Balance =  ? ";

                    m_daDataAdapter.Update(dtAccount);
                    m_cnADONetConnection.Close();


                    this.Close();
                    this.Dispose();


    Thursday, May 29, 2008 8:13 PM
  •  

    Congratulations!
    Friday, May 30, 2008 7:10 AM