CODEWITHSUNDEEP
c#mysqlexception
Tayab
Tayab

Reputation: 311

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

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.

Upvotes: 0

Views: 410

Answers (1)

Tayab
Tayab

Reputation: 311

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

Upvotes: 1

Related Questions