C#, MySQL - fatal error encountered during command execution- Checked other solutions, something I am Missing

Question

I have looked at the other questions with this title and I think the problem is something local with my code that I am missing.

The function that this button preforms is to calculate the points/rewards that a person earns based on the transaction total. For example, $10 = 1 point, 19=1 point, 20=2. 10 Points = 1 Rewards points, which is equal to a ten dollar credit.

My Code receives the title error message. I will include the entire function for completeness.

    private void button1_Click(object sender, EventArgs e)
    {
        try{
            string cs = @"server=localhost;userid=root;password=root;database=dockingbay94";
            MySqlConnection conn;
            //MySqlDataReader rdr = null;
            using (conn = new MySqlConnection(cs));

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            string input = textBox2.Text;
            MySqlCommand myCommand2 = conn.CreateCommand();
            myCommand2.CommandText = "SELECT Points FROM members WHERE id = @input";
            MySqlDataAdapter MyAdapter2 = new MySqlDataAdapter();
            MyAdapter2.SelectCommand = myCommand2;


            double transaction = Convert.ToDouble(textBox3.Text);
            double tmp_transaction = Math.Floor(transaction);
            string transaction_date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            double pointsbefore = (tmp_transaction / 10.0);
            int currentpoints = Convert.ToInt32(pointsbefore);
            int rewards = 0;
            int oldpoints = 0;
            string temp = "";
            pointsbefore = Math.Floor(pointsbefore);
            int new_points;
            double tmp_rewards = 0.0;
            double tmp_points;
            int new_rewards;

            oldpoints = (int)myCommand2.ExecuteScalar();

            new_points = currentpoints + oldpoints;
            tmp_points = new_points / 10;

            int tmp_rewards2 = 0;
            if (new_points > 10)
            {
                tmp_rewards = Math.Floor(tmp_points);
                tmp_rewards2 = Convert.ToInt32(tmp_rewards);
            }
            else if (new_points == 10)
            {
                tmp_rewards2 = 1;
            }
            else
            {
                tmp_rewards2 = 0;
            }

            new_rewards = rewards + tmp_rewards2;
            int points_left = 0;
            if (new_points > 10)
            {
                for (int i = 10; i < new_points; i++)
                {
                    points_left++;
                }
            }
            else if (new_points == 10)
            {
                points_left = 0;
            }
            else if (new_points < 10)
            {
                for (int i = 0; i < new_points; i++)
                {
                    points_left++;
                }
            }




        string query = "UPDATE members Set Points=@Points, rewards_collected=@Rewards, transaction_total=@Transaction, transaction_date=@TransactionDate" + "WHERE id = @input;";

        MySqlCommand cmdDataBase = new MySqlCommand(query, conn);
        cmdDataBase.Parameters.Add("@input", SqlDbType.Int).Value = Convert.ToInt32(textBox2.Text);
        cmdDataBase.Parameters.AddWithValue("@Points", new_points);
        cmdDataBase.Parameters.AddWithValue("@Rewards", new_rewards);
        cmdDataBase.Parameters.AddWithValue("@Transaction", textBox3.Text);
        cmdDataBase.Parameters.AddWithValue("@TransationDate", transaction_date);


        MySqlDataReader myReader2;
        myReader2 = cmdDataBase.ExecuteReader();
        MessageBox.Show("Data Updated");

    if(conn.State == ConnectionState.Open){
            conn.Close();
        }


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

I am not sure where the error could be. Probably not sending the right value.

Thanks

Answer 1

This line is wrong

using (conn = new MySqlConnection(cs));

Remove the semicolon and include everything that needs the MySqlConnection variable inside a {} block

using (MySqlConnection conn = new MySqlConnection(cs))
{
    // No need to test if the connection is not open....
    conn.Open();
    .........

    // Not needed (at least from your code above
    // MySqlDataAdapter MyAdapter2 = new MySqlDataAdapter();
    // MyAdapter2.SelectCommand = myCommand2;

    ... calcs follow here

    // Attention here, if the query returns null (no input match) this line will throw
    oldpoints = (int)myCommand2.ExecuteScalar();

    .... other calcs here


    MySqlCommand cmdDataBase = new MySqlCommand(query, conn);
    cmdDataBase.Parameters.Add("@input", SqlDbType.Int).Value = Convert.ToInt32(textBox2.Text);
    cmdDataBase.Parameters.AddWithValue("@Points", new_points);
    cmdDataBase.Parameters.AddWithValue("@Rewards", new_rewards);
    cmdDataBase.Parameters.AddWithValue("@Transaction", textBox3.Text);
    cmdDataBase.Parameters.AddWithValue("@TransationDate", transaction_date);

    // Use ExecuteNonQuery for INSERT/UPDATE/DELETE and other DDL calla
    cmdDataBase.ExecuteNonQuery();

    // Not needed
    // MySqlDataReader myReader2;
    // myReader2 = cmdDataBase.ExecuteReader();

    // Not needed, the using block will close and dispose the connection
    if(conn.State == ConnectionState.Open)
        conn.Close();

}

There is also another error in the final query. Missing a space between @TransactionDate parameter and the WHERE clause. In cases where a long SQL command text is needed I find very useful the verbatim string line character continuation @

string query = @"UPDATE members Set Points=@Points, rewards_collected=@Rewards, 
                        transaction_total=@Transaction, transaction_date=@TransactionDate
                        WHERE id = @input;";