Insert while reading data

Question

I have the procedure below which gives mysql error 1064.

apt is 5 bytes varchar, amount is double, paytype as smallint (can be either 1 or 2 for the moment), paydate as date , explanation is 50 bytes varchar in the table definition

The values that I observed when I debugged 4 bytes string data for apt, amount is 40, paytype is 1, date 01-02-2014, explanation is about 20 bytes of string.

Why it gives error 1064.

Because I'am reading data ?

As you can see I use 2 different connection with conn I read data and with conn1 I insert.

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

score 3 · Accepted Answer · edited May 23 '17 at 12:03

3

Error 1064 is:

#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...

The command:

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

...is missing the INTO keyword (which is standard, but some databases let you omit it) and an ending )

You want:

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

edited May 23 '17 at 12:03

Community

1
1

answered Feb 05 '14 at 17:03

Mike Christensen

88,082
50
208
326

NOt INTO but parathere at the end thanks :)))) Because almmost all my INSERT are without INTO but it's working except this where there is one parathesis less :) – Ismail Gunes Feb 05 '14 at 19:18
Glad you got it working! I'd suggest using `INSERT INTO` because [it's more standard](http://stackoverflow.com/questions/233919/insert-vs-insert-into) – Mike Christensen Feb 05 '14 at 19:34

Insert while reading data

1 Answers1