C#、MySQL - 命令执行期间遇到致命错误 - 检查了其他解决方案，我缺少的东西 [英] C#, MySQL - fatal error encountered during command execution- Checked other solutions, something I am Missing

问题描述

我已经查看了具有此标题的其他问题，我认为问题出在我缺少的代码的本地问题.

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.

此按钮执行的功能是根据交易总额计算一个人获得的积分/奖励.例如，$10 = 1 点，19=1 点，20=2.10 积分 = 1 奖励积分，相当于 10 美元积分.

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.

谢谢

推荐答案

这行错了

using (conn = new MySqlConnection(cs));

删除分号并将需要 MySqlConnection 变量的所有内容包含在 {} 块中

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();

}

最终查询中还有一个错误.@TransactionDate 参数和 WHERE 子句之间缺少空格.在需要长 SQL 命令文本的情况下，我发现逐字字符串行字符延续 @

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;";

这篇关于C#、MySQL - 命令执行期间遇到致命错误 - 检查了其他解决方案，我缺少的东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！