fatal error encountered during execution... during update

Question

This code is placed in the button. and when i click it to update the data, a messagebox error appears saying "fatal error encountered during command execution".

Your answers would be a great help. Thank you

 MySqlConnection connection = new MySqlConnection(MyConnectionString);
        MySqlCommand cmd;
        try
        {
            connection.Open();
            cmd = connection.CreateCommand();
            cmd.CommandText = "UPDATE student_offense SET TYPE=@TYPE,DATE_HAPPENED=@DH,DESCRIPTION=@DESC,SANCTION=@SANC" + 
                "Where STUDENT_NO = @STUDENT_NO And DESCRIPTION=@DESC And SANCTION=@SANC And DATE_HAPPENED=@DH";

            cmd.Parameters.AddWithValue("@TYPE", offense_combo.Text);
            cmd.Parameters.AddWithValue("@DH", date_hapen.Text);
            cmd.Parameters.AddWithValue("@DESC", description_txt.Text);
            cmd.Parameters.AddWithValue("@SANC", sanction_txt.Text);
            cmd.Parameters.AddWithValue("@STUDENT_NO", studentNo_txt.Text);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            MessageBox.Show("updated");


            //refresh
            cmd.CommandText = "SELECT student_info.Student_no,student_info.Lastname,student_info.Firstname,student_offense.Type,student_offense.Description,student_offense.Date_Happened,student_offense.Sanction,student_offense.Date_Recorded from student_info,student_offense where student_info.student_no = student_offense.student_no";
            MySqlDataAdapter sda = new MySqlDataAdapter();
            sda.SelectCommand = cmd;
            dbdataset = new DataTable();
            sda.Fill(dbdataset);
            bSource = new BindingSource();

            bSource.DataSource = dbdataset;
            dataGridView1.DataSource = bSource;
            sda.Update(dbdataset);
            bSource.DataSource = dbdataset;
            dataGridView1.DataSource = bSource;

            student_no_valid.Visible = false;
            stud_no_error.Visible = true;

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

        listBox1.Items.Clear();
        description_txt.Text = "";
        studentNo_txt.Text = "";
        offense_combo.Text = "";
        current_date();
        sanction_txt.Text = "";

On which line exactly? – Soner Gönül Mar 16 '14 at 17:59 — Soner Gönül, Mar 16 '14 at 17:59
only a messagebox error.. it won't specify which line – davz_11 Mar 16 '14 at 18:04 — davz_11, Mar 16 '14 at 18:04

Sudhakar Tillapudi · Accepted Answer · 2014-03-16T18:16:25.487

You are missing space between Parameter @SANC and Where .

Try This:

cmd.CommandText = "UPDATE student_offense SET TYPE=@TYPE,DATE_HAPPENED=@DH,
     DESCRIPTION=@DESC,SANCTION=@SANC" + " Where STUDENT_NO = @STUDENT_NO And 
                 DESCRIPTION=@DESC And SANCTION=@SANC And DATE_HAPPENED=@DH";

Suggestion : if your DATE_HAPPENED column type is Date in your table,then You need to send the proper Date format.

Try This: Assuming user enters Date in dd-MM-yyyy format.

DateTime dt = DateTime.ParseExact(date_hapen.Text,"dd-MM-yyyy",
                                  CutureInfo.InvariantCulture);

Now while assigning the DATE_HAPPENED value provide the following format

cmd.Parameters.AddWithValue("@DH",dt.ToString("yyyy-MM-dd"));

score 0 · Answer 2 · answered Mar 16 '14 at 18:09

Probably this happened at cmd.ExecuteNonQuery(); or in the subsequent query. You can verify this by single-stepping through after a breakpoint. There is probably an error in the SQL. You can find this by looking at the internal error, or by trying the query on MySQL Workbench. Check to see that all of the parameters match table columns, and all the data types match.

Incidentally, there is no need to assign SANCTION and DATE_HAPPENED in the update statement since you required them to be equal in the WHERE.

fatal error encountered during execution... during update

2 Answers2

Linked