Question how to update database row by row while doing a calculation for each row

[rowlandsfc]

i have an application that takes data from a table and displays it into a datagridview. the table is for accounts and includes an accrued column. i have a button that i want to use that when pressed will calculate the daily accrued amount and add it into the accrued column for each account. so i need to pull a couple of values from the table or datagridview, do the calculations and then update the table and i need to do this for each row.

this is the code im usig to fill the datagridview

private void showAccounts()
        {
            using (SQLiteCommand cmd = connAccount.CreateCommand())
            {
                try
                {
                    connAccount = new SQLiteConnection(dbConnection.source);

                    cmd.CommandText = @"SELECT accid, account.custid, customer.title || ' ' || customer.firstname || ' ' || customer.lastname AS Name, product.isaname AS ProductName, balance, accrued, active, created FROM account INNER JOIN customer ON customer.custid = account.custid INNER JOIN product ON product.prodid = account.prodid";


                    da_Accounts = new SQLiteDataAdapter(cmd.CommandText, connAccount);
                    dt_Accounts = new DataTable();
                    da_Accounts.Fill(dt_Accounts);
                    dgv_Account.DataSource = dt_Accounts;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

this is the code for the initial code for connecting to the databse

private void Account_Load(object sender, EventArgs e)
        {
            // connects to the database
            try
            {
                connAccount = new SQLiteConnection();
                connAccount.ConnectionString = dbConnection.source;
                connAccount.Open();

                if (connAccount.State == System.Data.ConnectionState.Open)
                    pbConnection.BackgroundImage = Properties.Resources.successful;
                lblConnection.Text = " - Connected - ";

            }
            catch (Exception ex)
            {
                connAccount.ConnectionString = dbConnection.source;
                connAccount.Close();

                if (connAccount.State == System.Data.ConnectionState.Closed)
                    pbConnection.BackgroundImage = Properties.Resources.unsuccessful;
                lblConnection.Text = " - Not Connected - ";

                MessageBox.Show(ex.Message);


            }
            showAccounts();

        }

this is what ive tried

 private void dailyUpdate()
        {
            
            foreach (DataGridViewRow row in dgv_Account.Rows)
            {
                string prodid = row.Cells["productName"].Value.ToString();
                accid = row.Cells["accid"].Value.ToString();
                string balance = row.Cells["balance"].Value.ToString();
                

                if (prodid == "Easy ISA Issue 1")
                {
                    interestRate = 0.03 / 365;
                }
                if (prodid == "Easy ISA Issue 2")
                {
                    interestRate = 0.07 / 365;
                }
                if (prodid == "Easy ISA Issue 3")
                {
                    interestRate = 0.03 / 365;
                }
                if (prodid == "Easy ISA Issue 4")
                {
                    interestRate = 0.05 / 365;
                }
                if (prodid == "Fool's Gold Account")
                {
                    interestRate = 0.05 / 365;
                }
                if (prodid == "Fleeced While You Watch Issue 1")
                {
                    interestRate = 0.005 / 365;
                }
                if (prodid == "Fleeced While You Watch Issue 2")
                {
                    interestRate = 0.005 / 365;
                }
                if (prodid == "Hardly Worth the Effort")
                {
                    interestRate = 0.02 / 365;
                }
                if (prodid == "Only for the bankers")
                {
                    interestRate = 0.08 / 365;
                }
                accrued = (interestRate * double.Parse(balance)).ToString(); ;
                updateAccrued();

            }
        }

        private void updateAccrued()
        {
            // updates the  information in the database
            using (SQLiteCommand cmd = connAccount.CreateCommand())
            {
                try
                {
                    cmd.CommandText = "UPDATE account SET accrued = @setAccrued WHERE accid = @setAccid";
                    cmd.Parameters.AddWithValue("setAccrued", accrued);
                    cmd.Parameters.AddWithValue("setAccid", accid);

                    cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                
            }
        }

i get 2 errors with this
1. it says the databseis notopen for each row this pops up and onceyou finish going through the pop up messages i then get the 2nd error
2.System.NullReferenceException: 'Object reference not set to an instance of an object.' this occurs when trying to get the value from the datagridview and store it.

any help and quidance would be appreciated

 

[jmcilhinney]

Just use the same data adapter to retrieve data into a DataTable and save the changes back to the database. Do whatever you want with the DataTable in between.

 

[rowlandsfc]

Just use the same data adapter to retrieve data into a DataTable and save the changes back to the database. Do whatever you want with the DataTable in between.

ok i will take a look into that thanks