lundi 3 juillet 2017

Conversion failed when converting date and/or time from character string.

Im currently doing a webapp in which I need to filter a chart with a date range, this range is selected with two calendars that pass the dates to two different textboxs. I have tried with two different ways: with filterparameters and with the "between @date1 and @date2" query.

The thing is i get a problem that says:

Conversion failed when converting date and/or time from character string. This error only appears when i choose the second date range, it doesnt matter the order i choose the dates from the calendars. I've also checked my datetime conversions.

I have debbuged already and i dont see the error in my code:

protected void Calendar2_SelectionChanged(object sender, EventArgs e)
    {
        txtDate2.Text = Calendar2.SelectedDate.ToShortDateString();
        Calendar2.Visible = false;
    }

    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        txtDate1.Text = Calendar1.SelectedDate.ToShortDateString();
        Calendar1.Visible = false;
    }


this is my query in code and client side:

protected void btnShow_Click(object sender, EventArgs e)
{
    DateTime date1 = Convert.ToDateTime(txtDate1.Text);
    DateTime date2 = Convert.ToDateTime(txtDate2.Text);

    using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select Family, sum (TimeMins) as sumfield from DTCres where DateCres between @Date1 and @Date2 group by Family order by sumfield desc ";
                cmd.Parameters.AddWithValue("@Date1", date1);
                cmd.Parameters.AddWithValue("@Date2", date2);

                conn.Open();
                cmd.ExecuteNonQuery();
                //SqlDataReader dr = cmd.ExecuteReader();

                //if (dr.HasRows == !true)
                //{
                //    //dateLabel.Visible = true;
                //}
                //if (dr.HasRows == true)
                //{
                //    //dateLabel.Visible = false;
                //}
                conn.Close();
            }
            catch (Exception nessie)
            {
                string doc = nessie.Message;
                //dateLabel.Visible = true;
            }
        }
    }
}


<asp:SqlDataSource ID="SqlDS" runat="server" ConnectionString="<%$ ConnectionStrings:DTCrestronConnectionString %>" 
                    SelectCommand="select Family, sum (TimeMins) as sumfield from DTCres where DateCres between @Date1 and @Date2 group by Family order by sumfield desc"
                    FilterExpression="Family='{0}'">
                    <SelectParameters>
                        <asp:ControlParameter Name="Date1" ControlID="txtDate1" PropertyName="Text" Type="String"/>
                        <asp:ControlParameter Name="Date2" ControlID="txtDate2" PropertyName="Text" Type="String"/>
                    </SelectParameters>
                    <FilterParameters>
                        <asp:ControlParameter ConvertEmptyStringToNull="false" Name="Family" ControlID="cmbChartFam1" PropertyName="Text" />
                    </FilterParameters>
                </asp:SqlDataSource>
            </td>


please, any help would be so nice, and thanks in advance




Aucun commentaire:

Enregistrer un commentaire