Inserting mysql datas between 2 dates using c#

Question

<asp:TextBox ID="txtstartdate" class="form-control" autocomplete="off" CssClass="datepicker1" runat="server"></asp:TextBox>

<asp:TextBox ID="txtenddate" class="form-control" autocomplete="off" CssClass="datepicker2" runat="server"></asp:TextBox>

<asp:TextBox id="txtreason" TextMode="multiline"  runat="server" />

<asp:Button ID="submit" CssClass="login" runat="server" Text="Submit" OnClick="Submit_click" />

Using the Submit_click I want to insert data into table between the 2 dates selected on txtstartdate and txtenddate. txtreason should repeat for all the dates on the table.

For example if select dates 07/30/2018 and 08/04/2018 on txtstartdate and txtenddate and enter "hello" as reason for txtreason, I should get all the dates between 07/30/2018 and 08/04/2018 in the ldate column on the data table and hello should be repeated on reason column for 6 times for each individual dates. The below method works like a charm if you change date format.

protected void Submit_click(object sender, EventArgs e)
{
        DateTime startdate = Convert.ToDateTime(txtstartdate.Text);
        DateTime enddate = Convert.ToDateTime(txtenddate.Text);
        for (DateTime date = startdate; date <= enddate; date = date.AddDays(1))
        {
            try
            {
                string MyConString = "SERVER=localhost;DATABASE=mydb;UID=myid;PASSWORD=abc123;";
                MySqlConnection connection = new MySqlConnection(MyConString);
                string cmdText = "INSERT INTO approved(agentlogin ,leavetype ,date ,time, reason)VALUES ( @login, @type, @date, 'Full day', @reason)";
                MySqlCommand cmd = new MySqlCommand(cmdText, connection);
                cmd.Parameters.AddWithValue("@login", Label1.Text);
                cmd.Parameters.AddWithValue("@type", ddlleavetype.Text);
                cmd.Parameters.AddWithValue("@date", date);
                cmd.Parameters.AddWithValue("@reason", txtreason.Text);

                connection.Open();

                int result = cmd.ExecuteNonQuery();
                connection.Close();

                //lblError.Text = "Data Saved";

            }
            catch (Exception)
            {
                Console.Write("not entered");
                //lblError.Text = ex.Message;
            }
        }
    }

Answer 1

Use the below method to change the date format. Hope this helps

protected void Submit_click(object sender, EventArgs e)
{
    DateTime startdate = Convert.ToDateTime(txtstartdate.Text);
    DateTime enddate = Convert.ToDateTime(txtenddate.Text);
    for (DateTime date = startdate; date <= enddate; date = date.AddDays(1))
    {
        try
        {
            var shtdate = date.ToShortDateString();
            string MyConString = "SERVER=localhost;DATABASE=mydb;UID=myid;PASSWORD=abc123;";
            MySqlConnection connection = new MySqlConnection(MyConString);
            string cmdText = "INSERT INTO approved(agentlogin ,leavetype ,date ,time, reason)VALUES ( @login, @type, @date, 'Full day', @reason)";
            MySqlCommand cmd = new MySqlCommand(cmdText, connection);
            cmd.Parameters.AddWithValue("@login", Label1.Text);
            cmd.Parameters.AddWithValue("@type", ddlleavetype.Text);
            cmd.Parameters.AddWithValue("@date", shtdate);
            cmd.Parameters.AddWithValue("@reason", txtreason.Text);

            connection.Open();

            int result = cmd.ExecuteNonQuery();
            connection.Close();

            //lblError.Text = "Data Saved";

        }
        catch (Exception)
        {
            Console.Write("not entered");
            //lblError.Text = ex.Message;
        }
    }
}