Calendar booking using datagridview

Question

Good day everyone, I make a calendar booking using datagridview. Please take a look the image below.

as you can see they have Book start and Book end. Now I want to put a color from date start to date end. Please take a look the image below.

I don't how to put the color in datagridview, I hope someone can help me to fix this problem.

• I have a code in Book car button and here the code.

if (textBox1.Text == "")

        {

            MessageBox.Show("textbox empty.");

        } else if (textBox1.Text != "")

        {

            string conStr = "server=localhost;user=root;database=carrental;password=;";

            MySqlConnection conn = new MySqlConnection(conStr);

            conn.Open();

            string dt1 = dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss");

            string dt2 = dateTimePicker2.Value.ToString("yyyy-MM-dd HH:mm:ss");

            string query = "insert into rent_schedule (carID,startBook,endBook) Values ('" + textBox1.Text + "','" + dt1 + "','" + dt2 + "')";

            MySqlCommand cmd = new MySqlCommand(query, conn);

            cmd.ExecuteReader();

            MessageBox.Show("done");

            con.Close();

            dataGridView1.Columns.Clear();

            Add_Columns();//call the module

        }

• And this is for the module,

private void Add_Columns()

    {

        string connection = "server=localhost;user=root;database=carrental;password=";//filter all dtgAllSTock //LOCAL CONNECTION

        string query = "SELECT * FROM rent_schedule";

        MySqlConnection con = new MySqlConnection(connection);

        MySqlCommand cmd = new MySqlCommand(query, con);

        MySqlDataAdapter da = new MySqlDataAdapter();

        da.SelectCommand = cmd;

        DataTable dt = new DataTable();

        da.Fill(dt);

        dataGridView1.DataSource = dt;

        dataGridView1.Columns[0].Width = 200;

        dataGridView1.Columns[1].Width = 550;

        dataGridView1.Columns[2].Width = 500;

        //dataGridView1.Columns[3].Width = 900;

        dataGridView1.Columns[0].HeaderText = "ID";

        dataGridView1.Columns[1].HeaderText = "Car ID";

        dataGridView1.Columns[2].HeaderText = "Book start";

        dataGridView1.Columns[3].HeaderText = "Book end";

        dataGridView1.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;

        dataGridView1.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;

        dataGridView1.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;

        dataGridView1.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;

        dataGridView1.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

        dataGridView1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

        dataGridView1.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

        dataGridView1.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

        ////

        ///

        //dataGridView1.Columns.Add("carID", "Car ID");

        int DayInMonths  = DateTime.DaysInMonth(monthCalendar1.SelectionStart.Year, monthCalendar1.SelectionStart.Month);

        int i;

        for (i = 1; i <= DayInMonths; i++)

        dataGridView1.Columns.Add("{0}", i.ToString());

    }

• And this my code for displaying color, but I think this code something wrong.

private void ShowCollors()//show label rent

    {

        string ConString = "server=localhost;user=root;database=carrental;password=;";

        MySqlConnection Con = new MySqlConnection(ConString);

        Con.Open();

        string query = "Select * from rent_schedule Where startBook >= @ST AND endBook <=@ET";

        DateTime startDate = new DateTime(monthCalendar1.SelectionStart.Year, monthCalendar1.SelectionStart.Month, 1);

        DateTime EndDate = startDate.AddMonths(1);

        MySqlCommand cmd = new MySqlCommand(query, Con);

        cmd.Parameters.AddWithValue("@ST", startDate);

        cmd.Parameters.AddWithValue("@ET", EndDate);

        MySqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())

        {

            int id = dr.GetInt32(1);

            DateTime startBook = dr.GetDateTime(2);

            DateTime endBook = dr.GetDateTime(3);

            int startBook_C = startBook.Day;

            int endBook_C = endBook.Day;

            int TotalDays = endBook_C - startBook_C;

            //dataGridView1(startBook_C, id - 1).Style.BackColor = Color.Red;

            int i;

            for (i = 1; i <= TotalDays; i++)

            {

                //dataGridView1(startBook_C, id - 1).Style.BackColor = Color.Red;/* TODO ERROR: Skipped SkippedTokensTrivia */

                TotalDays -= 1;

            }

            

        }

        Con.Close();

    }

Answer 1

Hi @Jay-R Hayashi ，Welcome to Microsoft Q&A,

I referenced the code from KOZ6.0 and created View_CellFormatting events. You'll need to use this event in a suitable place in your own datagridview.

Your code involves a database and you can only modify it with reference to our code.

private void View_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
    if (e.RowIndex < 0 || e.ColumnIndex <= endTimeColumn.Index) return;
    if (view.Rows[e.RowIndex].IsNewRow) return;

    object startValue = view[startTimeColumn.Index, e.RowIndex].Value;
    object endValue = view[endTimeColumn.Index, e.RowIndex].Value;
    if (!(startValue is DateTime) || !(endValue is DateTime)) return;
    DateTime startTime = (DateTime)startValue;
    DateTime endTime = (DateTime)endValue;

    int day = int.Parse(view.Columns[e.ColumnIndex].HeaderText);
    DateTime dayStart = new DateTime(gridYear, gridMonth, day);
    DateTime dayEnd = dayStart.AddDays(1);

    if (startTime < dayEnd && endTime >= dayStart)
    {
        e.CellStyle.BackColor = Color.LightBlue; // Set the color
    }
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This is a drawing sample of DataGridView.

using System;
using System.Drawing;
using System.Windows.Forms;

public partial class Form1 : Form
{
    private readonly DataGridView view;
    private readonly DataGridViewTextBoxColumn startTimeColumn;
    private readonly DataGridViewTextBoxColumn endTimeColumn;
    private readonly Brush brush = new SolidBrush(Color.Lime);
    private int gridYear = 2024;
    private int gridMonth = 04;

    public Form1() {
        InitializeComponent();
        view = new DataGridView();
        view.Dock = DockStyle.Fill;
        startTimeColumn = AddTimeColumn("Book start");
        endTimeColumn = AddTimeColumn("Book End");
        for (int day = 1; day <= DateTime.DaysInMonth(gridYear, gridMonth); day++) {
            AddDayColumn(day);
        }
        Controls.Add(view);
        var rowIndex = view.Rows.Add();
        view[startTimeColumn.Index, rowIndex].Value = DateTime.Parse("4/17/2024 7:47 AM");
        view[endTimeColumn.Index, rowIndex].Value = DateTime.Parse("4/18/2024 7:47 AM");
        view.CellPainting += View_CellPainting;
    }

    private DataGridViewTextBoxColumn AddTimeColumn(string headerText) {
        var timeColumn = new DataGridViewTextBoxColumn();
        timeColumn.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
        timeColumn.HeaderText = headerText;
        timeColumn.DefaultCellStyle.Format = @"M/d/y hh:mm tt";
        view.Columns.Add(timeColumn);
        return timeColumn;
    }

    private DataGridViewTextBoxColumn AddDayColumn(int day) {
        var dayColumn = new DataGridViewTextBoxColumn();
        dayColumn.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
        dayColumn.HeaderText = day.ToString();
        dayColumn.Width = 30;
        view.Columns.Add(dayColumn);
        return dayColumn;
    }

    private void View_CellPainting(object sender, DataGridViewCellPaintingEventArgs e) {
        if (e.RowIndex < 0 || e.ColumnIndex <= endTimeColumn.Index) return;
        if (view.Rows[e.RowIndex].IsNewRow) return;

        object startValue = view[startTimeColumn.Index, e.RowIndex].Value;
        object endValue = view[endTimeColumn.Index, e.RowIndex].Value;
        if (!(startValue is DateTime) || !(endValue is DateTime)) return;
        DateTime startTime = (DateTime)startValue;
        DateTime endTime = (DateTime)endValue;

        int day = int.Parse(view.Columns[e.ColumnIndex].HeaderText);
        DateTime dayStart = new DateTime(gridYear, gridMonth, day);
        DateTime dayEnd = dayStart.AddDays(1);
        TimeSpan daySpan = dayEnd - dayStart;

        if (startTime < dayEnd && endTime >= dayStart) {
            e.Paint(e.CellBounds, DataGridViewPaintParts.All);

            int top = e.CellBounds.Top + 4;
            int bottom = e.CellBounds.Bottom - 4;

            int left = (int)(e.CellBounds.Width * (startTime - dayStart).TotalMinutes / daySpan.TotalMinutes);
            if (left < 0) left = 0;
            left = e.CellBounds.Left + left;

            int right = (int)(e.CellBounds.Width * (dayEnd - endTime).TotalMinutes / daySpan.TotalMinutes);
            if (right < 0) right = 0;
            right = e.CellBounds.Right - right;

            Rectangle face = Rectangle.FromLTRB(left, top, right, bottom);
            e.Graphics.FillRectangle(brush, face);

            e.Handled = true;
        }
    }
}

Answer 3

Good day to all my Master KOZ6.0, Jiale Xue - MSFT,

I would like to say thank you for your help, my assignment is already finish.

Thank you so much.