fatal error encountered during command execution in c#.net mysql

Question

I have tried the code below when I am going to click Save button I got the error of "fatal error encountered during command execution" I rechecked more than two times but unfortunately error not go away. please, anyone kindly fix this error.

private void button1_Click(object sender, EventArgs e)
        {
            string cid, lname, fname,street,city,state,phone,date,email,aco,actype,des,bal;

            cid = label14.Text;
            lname = textBox1.Text;
            fname = textBox2.Text;
            street = textBox3.Text;
            city = textBox4.Text;
            state = textBox5.Text;
            phone = textBox6.Text;
            date = dateTimePicker1.Text;
            email = textBox8.Text;


            aco = textBox7.Text;
            actype = comboBox1.Text;
            des = textBox10.Text;
            bal = textBox11.Text;

            con.Open();


            MySqlCommand cmd = con.CreateCommand();
            MySqlTransaction transaction;

            transaction = con.BeginTransaction();

            StringBuilder cmdText = new StringBuilder();
          cmdText.AppendLine("INSERT into customer (custid,lastname,firstname,street,city,state,phone,date,email) VALUES (@custid,@lastname,@firstname,@street,@city,@state,@phone,@date,@email)");
           cmdText.AppendLine("INSERT into account(accid,custid,acctype,description,balance) VALUES (@accid,@custoid,@acctype,@description,@balance)");



            cmd.CommandText = cmdText.ToString();
            cmd.Connection = con;
            cmd.Transaction = transaction;

            cmd.Parameters.AddWithValue("@custid", cid);
            cmd.Parameters.AddWithValue("@lastname", lname);
            cmd.Parameters.AddWithValue("@firstname", fname);
            cmd.Parameters.AddWithValue("@street", street);
            cmd.Parameters.AddWithValue("@city", city);
            cmd.Parameters.AddWithValue("@state", state);
            cmd.Parameters.AddWithValue("@phone", phone);
            cmd.Parameters.AddWithValue("@date", date);
            cmd.Parameters.AddWithValue("@email", email);

            cmd.Parameters.AddWithValue("@accid", aco);
            cmd.Parameters.AddWithValue("@cusotid", cid);
            cmd.Parameters.AddWithValue("@acctype", actype);
            cmd.Parameters.AddWithValue("@description", des);
            cmd.Parameters.AddWithValue("@balance", bal);







            try
            {
                cmd.ExecuteNonQuery();



                transaction.Commit();
                MessageBox.Show("Transaction Suceess");
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }

        }

Instead of MessageBox.Show(ex.Message) MessageBox.Show(ex.ToString()) or debug it and share the stack trace. — Ondrej Svejdar
– Ondrej Svejdar, Commented Jun 21, 2017 at 6:29

jegtugado · Accepted Answer · 2017-06-21 06:38:05Z

1

I have seen many developers encountering errors with their SQL because they are using AddWithValue on their SqlCommand. The issue with this is that the command doesn't know the data type of your sql command parameter.

You should use SqlParameterCollection.Add Method (String, SqlDbType, Int32) to specify the data type of the parameter. Refer to SqlDbType Enumeration for the SqlDbType enumeration.

Usage:

cmd.Parameters.Add("@custid", SqlDbType.Int).Value = cid;
cmd.Parameters.Add("@lastname", SqlDbType.Text).Value = lname;

P.S. I am assuming that there are no issues with your SQL connection string.

answered Jun 21, 2017 at 6:38

jegtugado

5,1511 gold badge14 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prabiga Duraisamy Over a year ago

cmd.Parameters.AddWithValue("@custid", MySqlDbType.Int32).Value = cid; cmd.Parameters.AddWithValue("@lastname", MySqlDbType.Text).Value = lname; cmd.Parameters.AddWithValue("@firstname",MySqlDbType.Text).Value = fname; but agian i got 1 error input string was not it current format

jegtugado Over a year ago

@PrabigaDuraisamy try SqlDbType.String on your @custid. I also suggest using separate SqlCommands for inserting to customer and account table.

jegtugado Over a year ago

@PrabigaDuraisamy can you check your parameter names? I noticed that in your insert statement you use @custoid but you are setting cusotid in your parameters. Keep those sql commands separated, it's for the better.

Collectives™ on Stack Overflow

fatal error encountered during command execution in c#.net mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related