C# MySQL encountering fatal error when inserting data into my table

Question

I have a method called 'AddUser' which is suppose to insert data into my table but whenever I execute it a MySqlException is thrown.

Exception message:

Fatal error encountered during command execution.

My columns:

Column          Type         Null   Default           Comments
Identity Number int(11)      No                       auto_increment
Full Name       varchar(32)  No          
Birthday        date         No          
Sex             varchar(6)   No          
City            varchar(32)  No          
Region          varchar(32)  No          
Zip Code        varchar(10)  No          
Country         varchar(32)  No          
Phone Number    varchar(15)  No          
Email Address   varchar(255) No          
Ticket Number   varchar(8)   No          
Registration    timestamp    No     CURRENT_TIMESTAMP

My code:

private MySqlConnection mySqlConnection;

private async void OpenConnection()
{
    if (mySqlConnection == null)
    {
        mySqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString);
    }
    await mySqlConnection.OpenAsync();
}

private async void CloseConnection()
{
    if (mySqlConnection != null)
    {
        await mySqlConnection.CloseAsync();
    }
}

private async Task<bool> AddUser(
    string fullName,
    string birthday,
    string sex,
    string city,
    string region,
    string zipCode,
    string country,
    string phoneNumber,
    string emailAddress)
{
    try
    {
        OpenConnection();
        MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.CommandText = string.Format("INSERT INTO Users (Identity_Number, Full_Name, Birthday, Sex, City, Region, Zip_Code, Country, Phone_Number, Email_Address, Ticket_Number, Registration_Time) VALUES (@Identity_Number, @Full_Name, @Birthday, @Sex, @City, @Region, @Zip_Code, @Country, @Phone_Number, @Email_Address, @Ticket_Number, @Registration_Time);");
        mySqlCommand.Parameters.Add("@Full_Name", MySqlDbType.VarChar).Value = fullName;
        mySqlCommand.Parameters.Add("@Birthday", MySqlDbType.Date).Value = birthday;
        mySqlCommand.Parameters.Add("@Sex", MySqlDbType.VarChar).Value = sex;
        mySqlCommand.Parameters.Add("@City", MySqlDbType.VarChar).Value = city;
        mySqlCommand.Parameters.Add("@Region", MySqlDbType.VarChar).Value = region;
        mySqlCommand.Parameters.Add("@Zip_Code", MySqlDbType.VarChar).Value = zipCode;
        mySqlCommand.Parameters.Add("@Country", MySqlDbType.VarChar).Value = country;
        mySqlCommand.Parameters.Add("@Phone_Number", MySqlDbType.VarChar).Value = phoneNumber;
        mySqlCommand.Parameters.Add("@Email_Address", MySqlDbType.VarChar).Value = emailAddress;
        mySqlCommand.Parameters.Add("@Ticket_Number", MySqlDbType.VarChar).Value = Guid.NewGuid().ToString().GetHashCode().ToString("X");

        await mySqlCommand.ExecuteNonQueryAsync();



CloseConnection();
            mySqlCommand.Dispose();
            return true;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

Would someone be able to inspect my code and let me know what I'm doing wrong? And also maybe how I can improve my code? Thanks in advance.

you dont need Identity_Number in the query - MySQL will generate it, you also dont supply a value for it so there are more params than values — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Feb 4, 2016 at 19:15
Registration is called Registration_Time in the query, and both identity_number and Registration_time are used as parameters in the query, but parameters with those names are never added. I think you may want to just leave them up altogether and use the default values. — Joachim Isaksson
– Joachim Isaksson, Commented Feb 4, 2016 at 19:18
When doing Inserts into a Table that has an auto increment attribute set on the column, you exclude the inserting of that column. if you need to know what the value of the identity field is, then use the SELECT SCOPE_IDENTITY() into @SomeReturnValue — MethodMan
– MethodMan, Commented Feb 4, 2016 at 19:21
@Steve yes I am aware of it but the OP can do the equivalent that's my point.. — MethodMan
– MethodMan, Commented Feb 4, 2016 at 19:25
Look at the InnerException, it will contain the real MySQL exception — Gusman
– Gusman, Commented Feb 4, 2016 at 19:30

Tayab · Accepted Answer · 2016-02-05 00:25:18Z

1

Thank you @Plutonix for your answer. I removed the underscore and escaped the column names containing spaces in them with back ticks as you suggested. The code works now.

            mySqlCommand.CommandText = string.Format("INSERT INTO Users (`Full Name`, Birthday, Sex, City, Region, `Zip Code`, Country, `Phone Number`, `Email Address`, `Ticket Number`) VALUES (@Full_Name, @Birthday, @Sex, @City, @Region, @Zip_Code, @Country, @Phone_Number, @Email_Address, @Ticket_Number);");

answered Feb 5, 2016 at 0:25

Tayab

3112 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

C# MySQL encountering fatal error when inserting data into my table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related