CODEWITHSUNDEEP
c#mysql
davz_11
davz_11

Reputation: 145

fatal error encountered during execution... during update

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

Upvotes: 1

Views: 518

Answers (2)

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

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

Upvotes: 2

xpda
xpda

Reputation: 15813

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.

Upvotes: 0

Related Questions