Passing data field parameters

Question

I have a problem until simple to solve, but since I am new in C# I am not getting the solution.

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

An exception occurred without treatment of the type "System.Data.Sqlclient.Sqlexception" in System.Data.dll

Additional information: The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

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 2 code

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();
    }
}

Answer 1

First, try not to assemble the query by concatenating the parameters, even if 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 implement the interface IDisposable.

third point, avoid making unnecessary conversions on SqlServer, return the Datetime and treat directly on C#.

Follow an example with the 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");
}