Passing Date parameters for queries

Question

I am having a problem when passing a parameter. I tried to debug by passing the Date as `string, but the problem arises that one expects a number instead of a character.

After that I searched other ways and tried to pass the parameters in the most correct way and try a value of type Odbctype.Datetime, but there is a value conversion error saying that 'could not convert a DateTimePicker for a DateTime'.

I would like to understand the reasons for the errors and how I can solve this problem so I can use my fields Date as a query filter.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        dtaFinal.Value = DateTime.Today.AddDays(-1);
        dtaInicial.Value = DateTime.Today.AddDays(-1);
        txtEDV.MaxLength = 20;

        comboBanco.Items.Clear();

        List<Planta> plantas = new List<Planta>();

        using (StreamReader arquivo = File.OpenText(@"C:\Conexoes\Estados.txt"))
        {
            string linha;
            while ((linha = arquivo.ReadLine()) != null)
            {
                var espaçoArquivo = linha.Split(':');

                var planta = new Planta();
                planta.Local = espaçoArquivo[0];
                planta.Banco = espaçoArquivo[1];


                plantas.Add(planta);
            }

        }

        foreach (Planta result in plantas)
        {
            comboBanco.Items.Add(result);
        }
        comboBanco.DisplayMember = "Local";
        comboBanco.ValueMember = "Banco";

        comboBanco.SelectedIndex = 0;
    }

    private void comboBanco_SelectedIndexChanged(object sender, EventArgs e)
    {
        FrmGrid formb = new FrmGrid();

        switch (((Planta)comboBanco.SelectedItem).Local)
        {
            case "CT":
                formb.lblLocal.Text = ((Planta)comboBanco.SelectedItem).Local;
                break;

            case "CU":
                formb.lblLocal.Text = ((Planta)comboBanco.SelectedItem).Local;
                break;

            case "AT":
                formb.lblLocal.Text = ((Planta)comboBanco.SelectedItem).Local;
                break;

            default:
                break;
        }
    }

    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            OdbcConnection conn;
            string edvResultado = txtEDV.Text;
            //string date_inicial = dtaInicial.Value.ToString("DD/MM/YYYY HH24:MI:SS");
            //string date_final = dtaFinal.Value.ToString("DD/MM/YYYY HH24:MI:SS");
            Planta planta = (Planta)comboBanco.SelectedItem;

            conn = new OdbcConnection(planta.Banco);

            MessageBox.Show(conn.State.ToString());

            conn.Open();

            MessageBox.Show(conn.State.ToString());

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            OdbcDataAdapter ada = new OdbcDataAdapter();
            OdbcCommand cmd = new OdbcCommand();
            //cmd.Parameters.Add("@edvResultado", OdbcType.NVarChar).Value = "%" + edvResultado + "%";
            cmd.Parameters.Add("@data_inicial", OdbcType.DateTime).Value = dtaInicial;
            cmd.Parameters.Add("@data_final", OdbcType.DateTime).Value = dtaFinal;


            string sql = String.Format("SELECT * from emp where SSNO like '%{0}%' and LASTCHANGED between TO_DATE('@data_inicial 00:00:00', 'DD/MM/YYYY HH24:MI:SS') and TO_DATE('@data_final 23:59:59', 'DD/MM/YYYY HH24:MI:SS')", edvResultado);


            cmd.CommandText = sql;

            cmd.Connection = conn;

            ada = new OdbcDataAdapter(cmd);
            ada.Fill(dt);

            MessageBox.Show(dt.Rows.Count.ToString());

            FrmGrid c = new FrmGrid();
            c.lblLocal.Text = ((Planta)comboBanco.SelectedItem).Local;
            c.lblConexao.Text = groupBox1.Controls.OfType<RadioButton>().SingleOrDefault(rad => rad.Checked == true).Text;
            c.grdRelatorio.DataSource = dt;
            c.grdRelatorio.Refresh();
            c.ShowDialog();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

    }

}

Answer 1

The problem is the value you are passing for the parameters, which will be an object of the type DateTimePicker (or something like that) instead of a value like DateTime.

Change the code to the following:

cmd.Parameters.Add("@data_inicial", OdbcType.DateTime).Value = dtaInicial.Value;
cmd.Parameters.Add("@data_final", OdbcType.DateTime).Value = dtaFinal.Value;

This way is passing the Value, that will be of the type DateTime, control for the parameters.

This is not the only problem, the SQL command you are passing to OdbcCommand is not correct (at least as I know it). Try changing to the following:

string sql = String.Format("SELECT * from emp where SSNO like '%{0}%' and LASTCHANGED between ? and ?", edvResultado);

If you still can’t get results back, do the following, remove the parameters assignment and change the SQL query:

string sql = String.Format("SELECT * from emp where SSNO like '%{0}%' and LASTCHANGED between '{1:yyyy-MM-dd hh:mm:ss}' and '{2:yyyy-MM-dd hh:mm:ss}'", edvResultado, dtaInicial.Value, dtaFinal.Value);

Attention, this form is not the most recommended, because we are basically doing SQL injection.