C# MySqlCommand Insert shows fatal error command

Question

I have a table (Horario) with the following types:

id (int, auto increment)
codMedico (int)
dia (Date)
horarioInicio (Time)
horarioFim (Time)
status_id (int)

I can't see any problem on the code below, but it just shows me "fatal error" on mysqlexception (thrown at ExecuteNonQuerry() method. I am receiving two Times in a textfield (11:10) and (12:10), and the Date from a Datepicker.

Code below:

public bool agendaMedico_Novo(String codMedico, DateTime dia, String hrInicio, String hrFim, int status_id)
{
    if (bdConn.State == ConnectionState.Open)
    {               
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = bdConn;

        cmd.CommandText = "INSERT INTO horario (id, codMedico, dia, horarioInicio, horarioFim, status_id) VALUES (1, ?codMedico, ?dia, ?horarioInicio, ?horarioFim, ?status_id)";
        // cmd.CommandText = "INSERT INTO horario (codMedico, dia, horarioInicio, horarioFim, status_id) VALUES ("+codMedico+", "+dia.ToDate()+", "+"11:12"+ ", "+ "12:13"+ ", 1"+")";                                       
        try 
        {

            cmd.Parameters.Add("?codMedico", MySqlDbType.Int16).Value = Int16.Parse(codMedico);

        }

        catch (Exception e)
        {
            MessageBox.Show("Cod invalido!");

        }
        try
        {
            MessageBox.Show(dia.ToDate().ToString());
            cmd.Parameters.Add("?dia", MySqlDbType.Date).Value = dia.ToDate();
        }

        catch (Exception e)
        {
            MessageBox.Show("Dia invalido!");

        }
        try
        {
            cmd.Parameters.Add("?horarioInicio", MySqlDbType.Time).Value = DateTime.Parse(hrInicio);

        }
        catch (Exception e)
        {
            MessageBox.Show("Horario Invalido");

        }                  
        try
        {
            cmd.Parameters.Add("?horarioFim", MySqlDbType.Time).Value = DateTime.Parse(hrFim);

        }
        catch (Exception e)
        {
            MessageBox.Show("Horario Invalido");

        }
        try
        {
            cmd.Parameters.Add("?status_id", MySqlDbType.Int16).Value = status_id;
        }

        catch (Exception e)
        {
            MessageBox.Show("dasasdasd");
        }

        MessageBox.Show(cmd.CommandText);

        //try
        //{

        try
        {
            cmd.ExecuteNonQuery();
        }

        catch (MySqlException m)
        {
            MessageBox.Show(m.StackTrace);
            MessageBox.Show(m.Message);
        }

        return true;
    }
    else
    {
        MessageBox.Show("Erro de banco de dados. Tente novamente mais tarde");
        return false;
    }
    //return 0;
}

I don't use MySQL but I would guess that you would have to use a `TimeSpan` parameter value for a `Time` column rather than a `DateTime`. — jmcilhinney, Aug 21 '15 at 01:57

score 0 · Accepted Answer · answered Aug 21 '15 at 02:00

I think it's because you're trying to insert a value (1) into your id auto increment column:

cmd.CommandText = "INSERT INTO horario (id, codMedico, dia, horarioInicio, horarioFim, status_id) VALUES (1, ?codMedico, ?dia, ?horarioInicio, ?horarioFim, ?status_id)";

Try this for your CommandText instead:

cmd.CommandText = "INSERT INTO horario (codMedico, dia, horarioInicio, horarioFim, status_id) VALUES (?codMedico, ?dia, ?horarioInicio, ?horarioFim, ?status_id)";

The DB will generate it's own value for id so you don't need to!

score 0 · Answer 2 · answered Aug 21 '15 at 02:04

Maybe there are some invalid Date/Time values caused the exception. Try to use:

For Date:

cmd.Parameters.AddWithValue("@param", dia.ToDate().ToString("yyyy-MM-dd"));

For Time:

cmd.Parameters.AddWithValue("@param", DateTime.Parse(hrFim).ToString("hh:mm:ss"));

score 0 · Answer 3 · answered Aug 21 '15 at 02:10

0

If ID is your primary key, you shouldn't need to manually add values to it, i'm assuming because it's auto increment that it is a primary key, the exact same method without attempting to add 'ID' in your statement

answered Aug 21 '15 at 02:10

fraser jordan

114
1
9

C# MySqlCommand Insert shows fatal error command

3 Answers3