Passing Date Field Parameters

Question

Passing Date Field Parameters

Navigation

#1 by (1 votes)

-1

I'm having a problem until it's simple to solve, but as I'm new to C # I'm not getting the solution.

I have two form, I have txtBox of the form that I put the start date and end date, and this form 1 I have a print button which calls from 2 already with the fields start date and end date filled, only when I am calling form 2 is giving the exception exception error.

An unhandled exception of type   "System.Data.SqlClient.SqlException" in System.Data.dll

Additional information: The conversion of a varchar data type to a

Follow code (only relevant part) of form 1

private string conm = @"Data Source=192.168.0.250;Initial Catalog=DADOSADV;Persist Security Info=True;User ID=sa;Password=SQL";

SqlConnection conexao = null;
SqlCommand comando = null;


private void ListaGrid()
{
    string strSQL = @"SELECT
                      CONVERT(VARCHAR(10), CAST(SC.C5_EMISSAO AS DATE), 103) AS [EMISSÃO PED.],
                      SC.C5_NUM AS PEDIDO,
                      CONVERT(VARCHAR(10), CAST(SF.F2_EMISSAO AS DATE), 103) AS [EMISSÃO NF.],
                      SC.C5_NOTA AS NF,
                      SC.C5_XCLIDES AS CLIENTE,
                      SC.C5_VOLUME1 AS VOLUME,
                      S4.A4_NOME AS TRANSPORTADORA
                   FROM SC5020 AS SC
                   INNER JOIN SF2020 AS SF WITH (NOLOCK) ON SF.F2_DOC = SC.C5_NOTA
                   INNER JOIN SA4020 AS S4 WITH (NOLOCK) ON S4.A4_COD = SC.C5_TRANSP
                   WHERE SC.D_E_L_E_T_ <> '*' AND SC.C5_NOTA <> ''
                   AND SF.F2_EMISSAO BETWEEN CONVERT(datetime,'" + txtDtInicial.Text +"', 103) AND CONVERT(datetime,'"+ txtDtFinal.Text +"', 103) ORDER BY SF.F2_EMISSAO";

    conexao = new SqlConnection(conm);
    comando = new SqlCommand(strSQL, conexao);

    try
    {
        SqlDataAdapter dados = new SqlDataAdapter(comando);
        DataTable dtLista = new DataTable();
        dados.Fill(dtLista);

        dgPedidoDiario.DataSource = dtLista;
    }
    catch
    {
        MessageBox.Show("Não existem dados a serem encontrados");
    }
}

private void button1_Click(object sender, EventArgs e)
{
    frmImpPedidoDiario pedido = new frmImpPedidoDiario(txtDtInicial.Text, txtDtFinal.Text);
    pedido.Show();
}

follow form code 2

public partial class frmImpPedidoDiario : Form
{
    public frmImpPedidoDiario(string INICIAL, string FINAL )
    {
        InitializeComponent();
        txtDtInicial.Text = INICIAL;
        txtDtFinal.Text = FINAL;
    }

    private void frmImpPedidoDiario_Load(object sender, EventArgs e)
    {
        DateTime dtDe, dtAte;
        DateTime.TryParse(txtDtInicial.Text, out dtDe);
        DateTime.TryParse(txtDtFinal.Text, out dtAte);

        this.PedidoDiarioPSTableAdapter.Fill_PedDiario(this.DSPedidoDiario.PedidoDiarioPS, dtDe.ToString("ddMMyyyy"), dtAte.ToString("ddMMyyyy"));

        this.rpwPedidoDiario.RefreshReport();
    }
}

c#

asked by anonymous 19.04.2017 / 14:46

1 answer

Animation in Before Overwriting Text Search by date not working

score 1 · Accepted Answer

First, try not to mount the query by concatenating the parameters, even though there is no risk of SQL Injection , but SQL Server will not be able to save the query execution plan.

Second point, using is your friend and should be used whenever the object implements the IDisposable interface.

Third point, avoid making unnecessary conversions in SqlServer , return DateTime and deal directly with C# .

Here is an example with suggested changes:

string strSQL = @"
    SELECT
        CAST(SC.C5_EMISSAO AS DATE) AS [EMISSÃO PED.],
        SC.C5_NUM AS PEDIDO,
        CAST(SF.F2_EMISSAO AS DATE) AS [EMISSÃO NF.],
        SC.C5_NOTA AS NF,
        SC.C5_XCLIDES AS CLIENTE,
        SC.C5_VOLUME1 AS VOLUME,
        S4.A4_NOME AS TRANSPORTADORA
    FROM SC5020 AS SC
    INNER JOIN SF2020 AS SF WITH (NOLOCK) ON SF.F2_DOC = SC.C5_NOTA
    INNER JOIN SA4020 AS S4 WITH (NOLOCK) ON S4.A4_COD = SC.C5_TRANSP
    WHERE SC.D_E_L_E_T_ <> '*' AND SC.C5_NOTA <> ''
    AND SF.F2_EMISSAO BETWEEN @DataInicial AND @DataFinal ORDER BY SF.F2_EMISSAO"; 

try
{
    using (conexao = new SqlConnection(conm))
    {
        conexao.Open();
        using (comando = new SqlCommand(strSQL, conexao))
        {
            var dataInicial = DateTime.Min;
            var dataFinal = DateTime.Max;
            DateTime.TryParseExact(txtDtInicial.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out dataInicial);
            DateTime.TryParseExact(txtDtFinal.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out dataFinal);
            comando.Parameters.AddWithValue("@DataInicial", dataInicial);
            comando.Parameters.AddWithValue("@DataFinal", dataFinal);

            using (var dados = new SqlDataAdapter(comando))
            {
                DataTable dtLista = new DataTable();
                dados.Fill(dtLista);
                dgPedidoDiario.DataSource = dtLista;
            }
        }
    }
}
catch
{
    MessageBox.Show("Não existem dados a serem encontrados");
}