Passing Date parameters for queries

1

I'm having a problem when passing a parameter. I tried debugging by passing Date to string, but the problem arises that a number is expected instead of a character.

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

I would like to understand the reasons for the errors and how I can solve this problem so that I can use my Date fields 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());
        }

    }

}
    
asked by anonymous 30.08.2018 / 14:19

1 answer

1

The problem is in the value that is passing to the parameters, which will be an object of type DateTimePicker (or something like that) instead of a value of type 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;

In this way you are passing Value , which will be of type DateTime , of the control to the parameters.

This is not the only problem, the SQL command 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 not return results, do the following, remove the parameter 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);

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

    
30.08.2018 / 14:42