c# - 读数据时插入

Question

我有下面的程序，它给出了 mysql 错误 1064。

apt 是 5 bytes varchar，amount 是 double，paytype 是 smallint(暂时可以是 1 或 2)，paydate 是 date，表定义中的解释是 50 bytes varchar

我调试apt的4字节字符串数据时观察到的值，amount是40，paytype是1，date 01-02-2014，解释是大约20字节的字符串。

为什么报错1064.

因为我正在读取数据？

如您所见，我使用了 2 个不同的连接，分别是读取数据的 conn 和插入的 conn1。

    private void CreateGlobalDebt()
    {
        MySqlConnection conn = new MySqlConnection(Pvar.cs);
        MySqlConnection conn1 = new MySqlConnection(Pvar.cs);
        conn.Open();
        conn1.Open();
        MySqlCommand cmd = new MySqlCommand("SELECT apt FROM building ORDER BY apt", conn);
        MySqlDataReader reader = cmd.ExecuteReader();
        MySqlTransaction trans = conn1.BeginTransaction();
        while(reader.Read())
        {
            string apt = reader["apt"].ToString();
            MySqlCommand cmd1 = new MySqlCommand("INSERT parameters (apt,amount,paytype,paydate,explanation) VALUES (@apt,@amount,@paytype,@paydate,@explanation",conn1);
            cmd1.Parameters.Add("@apt", MySqlDbType.VarChar,5).Value = apt;
            cmd1.Parameters.Add("@amount", MySqlDbType.Double).Value = Convert.ToDouble(_textBox[0].Text);
            cmd1.Parameters.Add("@paytype", MySqlDbType.Int16).Value = PayType;
            cmd1.Parameters.Add("@paydate", MySqlDbType.Date).Value = Convert.ToDateTime(_textBox[2].Text);
            cmd1.Parameters.Add("@explanation", MySqlDbType.VarChar, 50).Value = _textBox[3].Text.ToString();
            try
            {
                cmd1.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                trans.Rollback();
                conn.Close();
                conn1.Close();
                MessageBox.Show(e.Number.ToString() + " -> " + e.Message.ToString());
                return;
            }
            cmd1.Parameters.Clear();
        }
        trans.Commit();
        conn.Close();
        conn1.Close();
    }

Answer 1

错误 1064 是:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use...

命令:

INSERT parameters (apt,amount,paytype,paydate,explanation) VALUES (@apt,@amount,@paytype,@paydate,@explanation

...缺少 INTO 关键字( which is standard ，但某些数据库允许您省略它)和结尾 )

你想要:

MySqlCommand cmd1 = new MySqlCommand("INSERT INTO parameters (apt,amount,paytype,paydate,explanation) VALUES (@apt,@amount,@paytype,@paydate,@explanation)",conn1);