How to write a Datetimepicker to the database

Question

Hello,

I have a form with the Datetimepicker - Short (only the date).

And in the database I put DataEntrada DATE

My problem is, I created an insertion method but I don’t know how to pass the Datetimepicker per parameter.

Method of entering records:

public void InserirRegistros(string nome, int codigo, int minimo, int maximo, int qtd, DateTime data)
{
    try
    {
        using (NpgsqlConnection conn = new NpgsqlConnection(ConnString))
        {
            conn.Open();

            string cmdInserir = String.Format("INSERT INTO ESTOQUE(codigo,codigo,minimo,maximo,quantidade,dataEntrada) " +
                                                            "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", nome, codigo, minimo, maximo, qtd, data);

            using (NpgsqlCommand cmd = new NpgsqlCommand(cmdInserir, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }catch(NpgsqlException ex)
    {
        throw ex;
    }catch(Exception ex)
    {
        throw ex;
    }
    finally
    {
        conn.Close();
    }
}

Event Click the button:

private void button1_Click(object sender, EventArgs e)
{
    DAL acesso = new DAL();

    try
    {
        acesso.InserirRegistros(txtNomeProduto.Text, Convert.ToInt32(txtCodigo.Text), Convert.ToInt32(txtMinimo.Text), Convert.ToInt32(txtMaximo.Text), Convert.ToInt32(txtEntrada.Text), Convert.ToDateTime(dtpData.DataBindings));
    }catch(Exception ex)
    {
        throw ex;
    }
    finally
    {
        MessageBox.Show("Cadastrado com sucesso!");
    }
}

Error:

System.InvalidCastException ocorrido
  HResult=0x80004002
  Message=Não é possível converter um objeto do tipo 'System.Windows.Forms.ControlBindingsCollection' no tipo 'System.IConvertible'.
  Source=ProjetoAlpha
  StackTrace:
   em ProjetoAlpha.frmProduto.button1_Click(Object sender, EventArgs e) em C:\Users\willian\source\repos\ProjetoAlpha\ProjetoAlpha\frmProduto.cs:linha 29
   em System.Windows.Forms.Control.OnClick(EventArgs e)
   em System.Windows.Forms.Button.OnClick(EventArgs e)
   em System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   em System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   em System.Windows.Forms.Control.WndProc(Message& m)
   em System.Windows.Forms.ButtonBase.WndProc(Message& m)
   em System.Windows.Forms.Button.WndProc(Message& m)
   em System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   em System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   em System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   em System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   em System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   em System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   em System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   em System.Windows.Forms.Application.Run(Form mainForm)
   em ProjetoAlpha.Program.Main() em C:\Users\willian\source\repos\ProjetoAlpha\ProjetoAlpha\Program.cs:linha 19

So far I could not, because the field in the table is DATE (2010-01-01) and I am going in the wrong format, how do I fix it? Do you need to change the type of the parameter to string?

Answer 1

Try this.
At the beginning of the query add : "set dateformat dmy", in my case, I use SQL SERVER.

string query = "set dateformat dmy INSERT...";

To get the value of the datetimepicker, try :

dateTimePicker.Value.ToString();

Answer 2

How to obtain the date selected in DateTimePicker is the following:

private void button1_Click(object sender, System.EventArgs e)
{
   var dataSelecionada = dateTimePicker1.Value;
}

This will return you an object of the type DateTimethen you can move on to your method.

Documentation

Answer 3

just change Convert.ToDateTime(dtpData.DataBindings) for dtpData.Value

Unless mistaken, if the field is of type Date and you inform a datetime in the parameters will not have problems.

But anyway, you can use other values:

dtpData.Value.Date Remains a DateTime on C#, but with the team down (01/01/2017 00:00:00)

Let’s get one detail right: Do not use string concatenation to mount the SQL command. See:

Your query is with 2 columns code, and only has 6 columns, but you inform 7 parameters in the format string.

 using (NpgsqlConnection conn = new NpgsqlConnection(ConnString))
 {
        conn.Open();

        string cmdInserir = "INSERT INTO ESTOQUE(codigo,nome,minimo,maximo,quantidade,dataEntrada) VALUES (:codigo,:nome,:minimo,:maximo,:quantidade,:dataEntrada);";

        using (NpgsqlCommand cmd = new NpgsqlCommand(cmdInserir, conn))
        {
            NpgsqlParameter[] param = new  NpgsqlParameter[6];
            param[0] = new NpgsqlParameter("codigo", codigo);
            param[1] = new NpgsqlParameter("nome", nome);
            param[2] = new NpgsqlParameter("minimo", minimo);
            param[3] = new NpgsqlParameter("maximo", maximo);
            param[4] = new NpgsqlParameter("quantidade", quantidade);
            param[5] = new NpgsqlParameter("dataEntrada", data);
            cmd.Parameters.AddRange(param);
            cmd.ExecuteNonQuery();
        }
}

Answer 4

just swap Convert.Todatetime(dtpData.Databindings) for dtpData.Value.Date

private void button1_Click(object sender, EventArgs e)
{
    DAL acesso = new DAL();

    try
    {
        acesso.InserirRegistros(txtNomeProduto.Text, Convert.ToInt32(txtCodigo.Text), Convert.ToInt32(txtMinimo.Text), Convert.ToInt32(txtMaximo.Text), Convert.ToInt32(txtEntrada.Text), dtpData.Value.Data);
    }catch(Exception ex)
    {
        throw ex;
    }
    finally
    {
        MessageBox.Show("Cadastrado com sucesso!");
    }
}