I have a Windows Form where 225 buttons are added at the run time, also I have the database which have two columns (first column is the SeatNumber with the datatype nchar(4) and the other column is IsBooked with the datatype bit). The SeatNumber contains data like this, A1,A2,A3,A4................O13,O14,O15, The other column contains value either 0 or 1. What I want to do is that when the form loads it should check in the database which rows of the second column has the value 1, for e.g. If A1,A2,A3,A4 and B1,B2,B3,B4 has the value 1 the buttons with the same name i.e buttons A1,A2,A3,A4,B1,B2,B3 and B4 should appear RED when the form loads. I have written the code, but it is not working. Can anyone please help me.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace rough1
{
    public partial class Form3 : Form
    {
        List<Seat> list = new List<Seat>();
        Button[] buttons = new Button[225];
        //int i = 0;
        string connectionString = "Data Source=DESAI-PC\\SQLEXPRESS; Initial Catalog = RoughWork; Integrated Security = True;";

        public Form3()
        {
            InitializeComponent();

            ///////
            //Buttons (Seats) Creation
            //////

            //Button[] buttons = new Button[225];
            //list = new List<Seat>();

            //Constants 1:
            char[] rows = "ABCDEFGHIJKLMNO".ToCharArray();
            int[] columns = Enumerable.Range(1, 15).ToArray();
            int[] xy = new int[] { 0, 0 };

            for (int i = 0; i < rows.Length; i++)
            {
                for (int j = 0; j < columns.Length; j++)
                {
                    // 1. add seat to a generic list<T>:
                    Seat s = new Seat();
                    s.Number = String.Concat(rows[i], columns[j]);
                    //when creating seats set occupation on false (seat free)!
                    s.Occupied = false;
                    list.Add(s);

                    // 2. create and position the button on form:
                    xy = PositioningSeat(j, i, xy[0], xy[1]);
                    buttons[i] = new Button();
                    buttons[i].Name = String.Concat(rows[i], columns[j]);
                    buttons[i].Text = String.Concat(rows[i], columns[j]);
                    buttons[i].Size = new Size(35, 35);
                    buttons[i].Location = new Point(xy[0], xy[1]);
                    buttons[i].Click += new EventHandler(buttonSeats_Click);
                    buttons[i].BackColor = System.Drawing.Color.PowderBlue;
                    //buttons[i].ForeColor = System.Drawing.Color.White;
                    //buttons[i].FlatAppearance.BorderColor = System.Drawing.Color.Blue;
                    //buttons[i].FlatAppearance.BorderSize = 0;
                    //buttons[i].FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    this.Controls.Add(buttons[i]);
                }
            }

            DataTable table = GetDataFromDataBase();
            SetOccupiedSeats(table);
        }

        private int[] PositioningSeat(int column, int row, int x, int y)
        {
            if (column == 0 || column == 15)
            {
                x = 60; // starting X position or reseting X to 1st column

                if (row % 5 == 0)
                {
                    y = y + 50; //going to new sector of Y
                }

                else
                {
                    y = y + 37; // next seat for Y
                }
            }

            else if (column % 5 == 0)
            {
                x = x + 70; //going to new sector of X
            }

            else
            {
                x = x + 37; // next seat for X
            }

            return new int[] { x, y };

        }

        private void buttonSeats_Click(object sender, EventArgs e)
        {
            Button seat = sender as Button;
            Button selected = sender as Button;
            //MessageBox.Show(selected.Name);
            if (list.Where(w => w.Number == seat.Text).Select(s => s.Occupied).Contains(true))
            {
                MessageBox.Show("Seat Number " + seat.Text + " has already been taken!", "Reservation Attention", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                if (DialogResult.Yes == MessageBox.Show("Set number " + seat.Text + " is free.\nDo you want to reservate it?", "New reservation", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    //seat is being reserved...
                    list.Where(w => w.Number == seat.Text).Select(s => { s.Occupied = true; return s; }).ToList();
                    seat.BackColor = System.Drawing.Color.Red;
                    seat.BackColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderSize = 0;
                    seat.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    MessageBox.Show("Reservation confirmed.\nSeat number " + seat.Text + " is now reserved.", "Reservation confirmation", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

        public class Seat
        {
            public string Number { get; set; }
            public bool Occupied { get; set; }
        }

        private DataTable GetDataFromDataBase()
        {
            DataTable table = new DataTable();
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand.CommandText = @"select * from SampleTable1";
                    da.SelectCommand.Connection = cn;
                    da.Fill(table);
                }
            }
            return table;
        }

        private void SetOccupiedSeats(DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                int seatNumber = int.Parse(row["SeatId"].ToString());
                int seatOccupied = int.Parse(row["IsBooked"].ToString());
                string seatNumbr = Convert.ToString(seatNumber);

                if (seatOccupied == 1)
                {
                    list.Where(w => w.Number == seatNumbr).Select(s => { s.Occupied = true; return s; }).ToList();
                }
            }
        }
    }
}

Does the MessageBox from line 119 show up?

Your problem is in "SetOccupiedSeats".
You are not changing any of the values in the seat list.
Try this in place of your if (seatOccupied == 1) { ... } code.

list.First(w => w.Number == seatNumbr).Occupied = seatOccupied == 1;

Your problem is in "SetOccupiedSeats".
You are not changing any of the values in the seat list.
Try this in place of your if (seatOccupied == 1) { ... } code.

list.First(w => w.Number == seatNumbr).Occupied = seatOccupied == 1;

I tried what you suggested and changed my code according to that. But now its giving me the exception("InvalidOperationException was unhandled" Sequence contains no matching element). Do one thing I am attaching my database as well. Can you please correct my mistake. Now my code looks like this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace rough1
{
    public partial class Form3 : Form
    {
        List<Seat> list = new List<Seat>();
        Button[] buttons = new Button[225];
        //int i = 0;
        string connectionString = "Data Source=DESAI-PC\\SQLEXPRESS; Initial Catalog = RoughWork; Integrated Security = True;";

        public Form3()
        {
            InitializeComponent();

            ///////
            //Buttons (Seats) Creation
            //////

            //Button[] buttons = new Button[225];
            //list = new List<Seat>();

            //Constants 1:
            char[] rows = "ABCDEFGHIJKLMNO".ToCharArray();
            int[] columns = Enumerable.Range(1, 15).ToArray();
            int[] xy = new int[] { 0, 0 };

            for (int i = 0; i < rows.Length; i++)
            {
                for (int j = 0; j < columns.Length; j++)
                {
                    // 1. add seat to a generic list<T>:
                    Seat s = new Seat();
                    s.Number = String.Concat(rows[i], columns[j]);
                    //when creating seats set occupation on false (seat free)!
                    s.Occupied = false;
                    list.Add(s);

                    // 2. create and position the button on form:
                    xy = PositioningSeat(j, i, xy[0], xy[1]);
                    buttons[i] = new Button();
                    buttons[i].Name = String.Concat(rows[i], columns[j]);
                    buttons[i].Text = String.Concat(rows[i], columns[j]);
                    buttons[i].Size = new Size(35, 35);
                    buttons[i].Location = new Point(xy[0], xy[1]);
                    buttons[i].Click += new EventHandler(buttonSeats_Click);
                    buttons[i].BackColor = System.Drawing.Color.PowderBlue;
                    //buttons[i].ForeColor = System.Drawing.Color.White;
                    //buttons[i].FlatAppearance.BorderColor = System.Drawing.Color.Blue;
                    //buttons[i].FlatAppearance.BorderSize = 0;
                    //buttons[i].FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    this.Controls.Add(buttons[i]);
                }
            }

            DataTable table = GetDataFromDataBase();
            SetOccupiedSeats(table);
        }

        private int[] PositioningSeat(int column, int row, int x, int y)
        {
            if (column == 0 || column == 15)
            {
                x = 60; // starting X position or reseting X to 1st column

                if (row % 5 == 0)
                {
                    y = y + 50; //going to new sector of Y
                }

                else
                {
                    y = y + 37; // next seat for Y
                }
            }

            else if (column % 5 == 0)
            {
                x = x + 70; //going to new sector of X
            }

            else
            {
                x = x + 37; // next seat for X
            }

            return new int[] { x, y };

        }

        private void buttonSeats_Click(object sender, EventArgs e)
        {
            Button seat = sender as Button;
            //Button selected = sender as Button;
            //MessageBox.Show(selected.Name);
            if (list.Where(w => w.Number == seat.Text).Select(s => s.Occupied).Contains(true))
            {
                MessageBox.Show("Seat Number " + seat.Text + " has already been taken!", "Reservation Attention", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                if (DialogResult.Yes == MessageBox.Show("Set number " + seat.Text + " is free.\nDo you want to reservate it?", "New reservation", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    //seat is being reserved...
                    list.Where(w => w.Number == seat.Text).Select(s => { s.Occupied = true; return s; }).ToList();
                    seat.BackColor = System.Drawing.Color.Red;
                    seat.BackColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderSize = 0;
                    seat.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    MessageBox.Show("Reservation confirmed.\nSeat number " + seat.Text + " is now reserved.", "Reservation confirmation", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

        public class Seat
        {
            public string Number { get; set; }
            public bool Occupied { get; set; }
        }

        private DataTable GetDataFromDataBase()
        {
            DataTable table = new DataTable();
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = new SqlCommand { CommandText = @"select * from SampleTable1", Connection = cn };
                    da.Fill(table);
                }
            }
            return table;
        }

        private void SetOccupiedSeats(DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                int seatNumber = int.Parse(row["SeatId"].ToString());
                int seatOccupied = int.Parse(row["IsBooked"].ToString());
                string seatNumbr = Convert.ToString(seatNumber);
                label1.Text = seatOccupied.ToString();
                
                if (seatOccupied == 1)
                {
                    list.First(w => w.Number == seatNumbr).Occupied = seatOccupied == 1;
                }
            }
        }
    }
}

Does the MessageBox from line 119 show up?

Yes, it does show up the message box from line 119

I was trying to solve the wrong problem. In your SetOccupiedSeats method you don't change the button colors, so why would you think they would show up as red? :)

Also, there is no such word as "reservate", change it to "reserve" in line 110

I was trying to solve the wrong problem. In your SetOccupiedSeats method you don't change the button colors, so why would you think they would show up as red? :)

Also, there is no such word as "reservate", change it to "reserve" in line 110

Have you found out the error in my code. I got it what you said, but the "SetOccupiedSeats method" should at least set the occupied property to true for the seats, but it is also not doing that? Please check the the thread, I have posted a new code as well as the database, so that you can check the database as well.

In your SetOccupiedSeats() method the seat number is an actual number (you convert the value from the database into an int, then into a string to get seatNumbr). In the rest of your code the seat number is a letter followed by a number. Because of this your SetOccupiedSeats will never find the seat it is looking for. I've not looked at your database to see how you actually store the seat number, but it needs to match what you are looking for.

In your SetOccupiedSeats() method the seat number is an actual number (you convert the value from the database into an int, then into a string to get seatNumbr). In the rest of your code the seat number is a letter followed by a number. Because of this your SetOccupiedSeats will never find the seat it is looking for. I've not looked at your database to see how you actually store the seat number, but it needs to match what you are looking for.

Can we have a live chat on any Website, so that my problem may get solved quickly...

The reason the Seat.Occupied properties are not set is because you are using the SeatId to set SeatNumbr but this is an index in your table and does not match the Seat.Number property which is a string. You should perhaps be using the SeatNo field from the DB.

Try this

private void SetOccupiedSeats(DataTable table)
{
	foreach (DataRow row in table.Rows)
	{
		var seatID = int.Parse(row["SeatId"].ToString());
		var seatNumber = row["SeatNo"] as string;
		var seatOccupied = int.Parse(row["IsBooked"].ToString());

		list.Find(w => w.Number == seatNumber).Occupied = (seatOccupied == 1);
	}
}

Momerath is right about the initial button colour.
Calling SetOccupiedSeats is perhaps better done before creating the buttons.
That way you have the Occupied property set so you can initialise the button colour appropriately as you build them.

Note:
You are trying to synchronise three objects; the DB rows, the Seat objects and the buttons.
It might be better to build the Seat list using the data from the database directly in SetOccupiedSeats (it might then be better named GetOccupiedSeats).
This could be achieved easily if you modify the Seat class to wrapper the DataRows from the table. (You will have to keep the table in a global variable to do this.)

If Seat wrappers the DataRow and is further deriving from Button you can do away with the Button array and have a direct link to the database table.

Then when you come to update the database the datatable is already in synch with the buttons.

This is what I ended up with when I did this. It still needs the database update but I must leave something for you to do.;)

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
	public partial class Form1 : Form
	{
		private List<Seat> list = new List<Seat>();
		//private Button[] buttons = new Button[225];
		//int i = 0;
		private string connectionString =
			"Data Source=.\\SQLEXPRESS; Initial Catalog = RoughWork; Integrated Security = True;";

		public Form1()
		{
			InitializeComponent();

			DataTable table = GetDataFromDataBase();
			SetOccupiedSeats(table);

			///////
			//Buttons (Seats) Creation
			//////

			//Button[] buttons = new Button[225];
			//list = new List<Seat>();

			//Constants 1:
			char[] rows = "ABCDEFGHIJKLMNO".ToCharArray();
			int[] columns = Enumerable.Range(1, 15).ToArray();
			int[] xy = new int[] {0, 0};

			for (int i = 0; i < rows.Length; i++)
			{
				for (int j = 0; j < columns.Length; j++)
				{
					// 1. find seat
					var Number = String.Concat(rows[i], columns[j]);
					var seat = list.Find(w => w.Number == Number);

					// 2. create and position the button on form:
					xy = PositioningSeat(j, i, xy[0], xy[1]);
					seat.Name = String.Concat(rows[i], columns[j]);
					seat.Text = String.Concat(rows[i], columns[j]);
					seat.Size = new Size(35, 35);
					seat.Location = new Point(xy[0], xy[1]);
					seat.Click += new EventHandler(buttonSeats_Click);
					seat.BackColor = seat.Occupied ? System.Drawing.Color.Red : System.Drawing.Color.PowderBlue;
					seat.FlatAppearance.BorderColor = seat.Occupied ? System.Drawing.Color.Red : System.Drawing.Color.Blue;
					//buttons[i].ForeColor = System.Drawing.Color.White;
					//buttons[i].FlatAppearance.BorderColor = System.Drawing.Color.Blue;
					//buttons[i].FlatAppearance.BorderSize = 0;
					//buttons[i].FlatStyle = System.Windows.Forms.FlatStyle.Popup;
					this.Controls.Add(seat);
				}
			}
		}

		private int[] PositioningSeat(int column, int row, int x, int y)
		{
			if (column == 0 || column == 15)
			{
				x = 60; // starting X position or reseting X to 1st column

				if (row%5 == 0)
				{
					y = y + 50; //going to new sector of Y
				}

				else
				{
					y = y + 37; // next seat for Y
				}
			}

			else if (column%5 == 0)
			{
				x = x + 70; //going to new sector of X
			}

			else
			{
				x = x + 37; // next seat for X
			}

			return new int[] {x, y};

		}

		private void buttonSeats_Click(object sender, EventArgs e)
		{
			Seat seat = sender as Seat;
			if (seat == null)
				return;
		
			if (seat.Occupied)
			{
				MessageBox.Show("Seat Number " + seat.Text + " has already been taken!",
				                "Reservation Attention",
				                MessageBoxButtons.OK,
				                MessageBoxIcon.Warning);
			}
			else
			{
				if (DialogResult.Yes ==
				    MessageBox.Show("Set number " + seat.Text + " is free.\nDo you want to reservate it?",
				                    "New reservation",
				                    MessageBoxButtons.YesNo,
				                    MessageBoxIcon.Question))
				{
					//seat is being reserved...
					seat.Occupied = true;
					seat.BackColor = System.Drawing.Color.Red;
					seat.FlatAppearance.BorderColor = System.Drawing.Color.Red;
					seat.FlatAppearance.BorderSize = 0;
					seat.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
					MessageBox.Show("Reservation confirmed.\nSeat number " + seat.Text + " is now reserved.",
					                "Reservation confirmation",
					                MessageBoxButtons.OK,
					                MessageBoxIcon.Information);
				}
			}
		}

		public class Seat: Button
		{
			private readonly DataRow _row;

			public Seat(DataRow row)
			{
				if (row == null)
					throw new ArgumentNullException("row");
				_row = row;
			}

			public int ID
			{
				get { return int.Parse(_row["SeatId"].ToString()); }
			}

			public string Number
			{
				get { return _row["SeatNo"] as string; }
				set
				{
					if (value.Length <= 3)
					{
						_row["SeatNo"] = value;
					}
				}
			}

			public bool Occupied
			{
				get { return int.Parse(_row["IsBooked"].ToString()) == 1; }
				set { _row["IsBooked"] = value ? 1 : 0; }
			}
		}


		private DataTable GetDataFromDataBase()
		{
			DataTable table = new DataTable();
			using (SqlConnection cn = new SqlConnection(connectionString))
			{
				using (SqlDataAdapter da = new SqlDataAdapter())
				{
					da.SelectCommand = new SqlCommand {CommandText = @"select * from SampleTable1", Connection = cn};
					da.Fill(table);
				}
			}
			return table;
		}

		private void SetOccupiedSeats(DataTable table)
		{
			foreach (DataRow row in table.Rows)
			{
				list.Add(new Seat(row));
			}
		}
	}
}

If Seat wrappers the DataRow and is further deriving from Button you can do away with the Button array and have a direct link to the database table.

Then when you come to update the database the datatable is already in synch with the buttons.

This is what I ended up with when I did this. It still needs the database update but I must leave something for you to do.;)

Can you explain the above lines in simple language. What does this line means "If Seat wrappers the DataRow and is further deriving from Button you can do away with the Button array and have a direct link to the database table." and what is "wrappers"

Sorry : may be deleted, posted in wrong thread.:sad:

Can you explain the above lines in simple language. What does this line means "If Seat wrappers the DataRow and is further deriving from Button you can do away with the Button array and have a direct link to the database table." and what is "wrappers"

Sorry, that is a rather strange English.

In my version of your code I changed your Seat class to take a DataRow in its constructor.
The properties of my Seat class read and write to this DataRow object.
By doing this Seat now forms a "wrapper" around the DataRow object.

In addition, I derived Seat from Button so that it can be used as a Button on the form.
This merges all three components in to a single entity; hopefully making future management easier.

Note: The button could be a separate class (say SeatButton) that wraps Seat.

Sorry, that is a rather strange English.

In my version of your code I changed your Seat class to take a DataRow in its constructor.
The properties of my Seat class read and write to this DataRow object.
By doing this Seat now forms a "wrapper" around the DataRow object.

In addition, I derived Seat from Button so that it can be used as a Button on the form.
This merges all three components in to a single entity; hopefully making future management easier.

Note: The button could be a separate class (say SeatButton) that wraps Seat.

Sorry, I could not reply in time, because I was out on a vacation. Thanks for the help but I want to ask you one thing that how to I update the database about the new seats been booked other than which are already booked when the form loads. I mean when the form loads and the user books one of the seat which is not booked, I want the change to be reflected in the database immediately. so how can I do that?

And one more thing, what were you pointing out at when you said this

"Then when you come to update the database the datatable is already in synch with the buttons.

This is what I ended up with when I did this. It still needs the database update but I must leave something for you to do."

. So what had you left for me to do?

"So what had you left for me to do?" - Update the database with any changes!

One way is to use a DataAdapter, set up the Update, Insert and Delete queries, then pass the changed table (or row) in to the Update method.

Alternatively, for a single row, create an SQLCommand with an Update query, use parameters to load the new values, execute the command.

"So what had you left for me to do?" - Update the database with any changes!

One way is to use a DataAdapter, set up the Update, Insert and Delete queries, then pass the changed table (or row) in to the Update method.

Alternatively, for a single row, create an SQLCommand with an Update query, use parameters to load the new values, execute the command.

How do I delete the the seat occupied and where should the code be placed?

I think that very much depends on the application.
Your current interface does not allow clearing the IsBooked field; which is probably correct for a basic operator interface.

Using a second form with a "Clear All Seats" button (and other DB maintenance functions) that is only accessible to Admin level users would be one way to clear the IsBooked fields.

Point of Note
It looks like you have not considered multiple performances.
If you are booking seats for multiple performances then you will need to have a different set of seats per performance. I think this would totally change you DB structure.

I think that very much depends on the application.
Your current interface does not allow clearing the IsBooked field; which is probably correct for a basic operator interface.

I don't want to clear away the booked seats but, can I reflect the changes immediately about the seats booked in the database, I mean if the user clicks on for e.g. seat number E12 (which is not booked), the changes should be immediately made in the Database.

Hi nick, I have written following code to fulfill my requirement, but however it is not working as expected, can you help on the error? The code is as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace rough2
{
    public partial class Form1 : Form
    {
        private List<Seat> list = new List<Seat>();
        //private Button[] buttons = new Button[225];
        //int i = 0;
        string clickedButton;
        DataTable changedTable;
        private string connectionString =
            "Data Source=.\\SQLEXPRESS; Initial Catalog = RoughWork; Integrated Security = True;";

        public Form1()
        {
            InitializeComponent();

            DataTable table = GetDataFromDataBase();
            SetOccupiedSeats(table);

            ///////
            //Buttons (Seats) Creation
            //////

            //Button[] buttons = new Button[225];
            //list = new List<Seat>();

            //Constants 1:
            char[] rows = "ABCDEFGHIJKLMNO".ToCharArray();
            int[] columns = Enumerable.Range(1, 15).ToArray();
            int[] xy = new int[] { 0, 0 };

            for (int i = 0; i < rows.Length; i++)
            {
                for (int j = 0; j < columns.Length; j++)
                {
                    // 1. find seat
                    var Number = String.Concat(rows[i], columns[j]);
                    var seat = list.Find(w => w.Number == Number);

                    // 2. create and position the button on form:
                    xy = PositioningSeat(j, i, xy[0], xy[1]);
                    seat.Name = String.Concat(rows[i], columns[j]);
                    seat.Text = String.Concat(rows[i], columns[j]);
                    seat.Size = new Size(36, 35);
                    seat.Location = new Point(xy[0], xy[1]);
                    seat.Click += new EventHandler(buttonSeats_Click);
                    seat.BackColor = seat.Occupied ? System.Drawing.Color.Red : System.Drawing.Color.PowderBlue;
                    seat.FlatAppearance.BorderColor = seat.Occupied ? System.Drawing.Color.Red : System.Drawing.Color.Blue;
                    seat.FlatAppearance.BorderSize = seat.Occupied ? 0 : seat.FlatAppearance.BorderSize = 1;
                    seat.FlatStyle = seat.Occupied ? System.Windows.Forms.FlatStyle.Popup : seat.FlatStyle = System.Windows.Forms.FlatStyle.Standard;
                    //seat.ForeColor = System.Drawing.Color.White;
                    //seat.FlatAppearance.BorderColor = System.Drawing.Color.Blue;
                    //seat.FlatAppearance.BorderSize = 0;
                    //seat.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    this.Controls.Add(seat);
                }
            }
        }

        private int[] PositioningSeat(int column, int row, int x, int y)
        {
            if (column == 0 || column == 15)
            {
                x = 60; // starting X position or reseting X to 1st column

                if (row % 5 == 0)
                {
                    y = y + 50; //going to new sector of Y
                }

                else
                {
                    y = y + 37; // next seat for Y
                }
            }

            else if (column % 5 == 0)
            {
                x = x + 70; //going to new sector of X
            }

            else
            {
                x = x + 37; // next seat for X
            }

            return new int[] { x, y };

        }

        private void buttonSeats_Click(object sender, EventArgs e)
        {
            Seat seat = sender as Seat;
            clickedButton = seat.Number;
            changedTable = NewOccupiedSeats();            

            if (seat == null)
                return;

            if (seat.Occupied)
            {
                MessageBox.Show("Seat Number " + seat.Text + " has already been taken!",
                                "Reservation Attention",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Warning);
            }
            else
            {
                if (DialogResult.Yes ==
                    MessageBox.Show("Seat number " + seat.Text + " is free.\nDo you want to reserve it?",
                                    "New reservation",
                                    MessageBoxButtons.YesNo,
                                    MessageBoxIcon.Question))
                {
                    //seat is being reserved...
                    seat.Occupied = true;
                    NewOccupiedSeats();
                    seat.BackColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderSize = 0;
                    seat.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    MessageBox.Show("Reservation confirmed.\nSeat number " + seat.Text + " is now reserved.",
                                    "Reservation confirmation",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);
                }
            }
        }

        public class Seat : Button
        {
            private readonly DataRow _row;

            public Seat(DataRow row)
            {
                if (row == null)
                    throw new ArgumentNullException("row");
                _row = row;
            }

            public int ID
            {
                get 
                {
                    return int.Parse(_row["SeatId"].ToString());
                }
            }

            public string Number
            {
                get
                { 
                    return _row["SeatNo"] as string; 
                }
                
                set
                {
                    if (value.Length <= 3)
                    {
                        _row["SeatNo"] = value;
                    }
                }
            }

            public bool Occupied
            {
                get
                { 
                    return int.Parse(_row["IsBooked"].ToString()) == 1;
                }
                set 
                {
                    _row["IsBooked"] = value ? 1 : 0; 
                }
            }
        }


        private DataTable GetDataFromDataBase()
        {
            DataTable table = new DataTable();
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = new SqlCommand { CommandText = @"select * from SampleTable1", Connection = cn };
                    da.Fill(table);
                }
            }
            return table;
        }

        private void SetOccupiedSeats(DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                list.Add(new Seat(row));
            }
        }

        private DataTable NewOccupiedSeats()
        {
            DataTable updatedTable = new DataTable();
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = new SqlCommand { CommandText = @"select * from SampleTable1", Connection = cn };
                    da.Fill(updatedTable);                   
                }
            }

            // step 1: set the PrimaryKey property for the DataTable object
            DataColumn[] myprimarykey = new DataColumn[2];
            myprimarykey[0] = updatedTable.Columns["SeatNo"];
            updatedTable.PrimaryKey = myprimarykey;

            // step 2: use the Find() method to locate the DataRow
            // in the DataTable using the primary key value
            DataRow editDatarow = updatedTable.Rows.Find(clickedButton);

            // step 3: change the column values
            editDatarow["IsBooked"] = 1;

            // step 4: use the AcceptChanges() method of the DataTable to commit
            // the changes
            updatedTable.AcceptChanges();

            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    DataSet myDataset = new DataSet();
                    da.SelectCommand = new SqlCommand { CommandText = @"select * from SampleTable1", Connection = cn };
                    da.Fill(updatedTable);
                    da.Update(myDataset, "updatedTable");
                }
            }
            return updatedTable;      
        }
    }
}

Please re-read this post.

And this.

Also, consider that the seat button already contains the DataRow that is changed!!

Also, consider that the seat button already contains the DataRow that is changed!!

I have done it, I managed to update the data immediately on the button clicked, just one more thing I want to ask you that, I have added a button on the form for resetting the seat booked which I managed to do, the data in the "IsBooked" column of my database is reseted but on the form the seat (Buttons) is still showing that the seat is booked. How do I solve that problem? I have tried "this.Refresh", calling "Form1_Load" on the Reset button clicked but could not perform the desired task, just one thing worked "Application.Restart" but I don't want to use that. Please provide some idea. I am attaching the project along with the Database and also pasting the code below. Reply Soon.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace rough2
{
    public partial class Form1 : Form
    {
        private List<Seat> list = new List<Seat>();
        //private Button[] buttons = new Button[225];
        //int i = 0;
        string clickedButton;
        private string connectionString = "Data Source=.\\SQLEXPRESS; Initial Catalog = RoughWork; Integrated Security = True;";

        public Form1()
        {
            InitializeComponent();            
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable table = GetDataFromDataBase();
            SetOccupiedSeats(table);

            ///////
            //Buttons (Seats) Creation
            //////

            //Button[] buttons = new Button[225];
            //list = new List<Seat>();

            //Constants 1:
            char[] rows = "ABCDEFGHIJKLMNO".ToCharArray();
            int[] columns = Enumerable.Range(1, 15).ToArray();
            int[] xy = new int[] { 0, 0 };

            for (int i = 0; i < rows.Length; i++)
            {
                for (int j = 0; j < columns.Length; j++)
                {
                    // 1. find seat
                    var Number = String.Concat(rows[i], columns[j]);
                    var seat = list.Find(w => w.Number == Number);

                    // 2. create and position the button on form:
                    xy = PositioningSeat(j, i, xy[0], xy[1]);
                    seat.Name = String.Concat(rows[i], columns[j]);
                    seat.Text = String.Concat(rows[i], columns[j]);
                    seat.Size = new Size(36, 35);
                    seat.Location = new Point(xy[0], xy[1]);
                    seat.Click += new EventHandler(buttonSeats_Click);
                    seat.BackColor = seat.Occupied ? System.Drawing.Color.Red : System.Drawing.Color.PowderBlue;
                    seat.FlatAppearance.BorderColor = seat.Occupied ? System.Drawing.Color.Red : System.Drawing.Color.Blue;
                    seat.FlatAppearance.BorderSize = seat.Occupied ? 0 : seat.FlatAppearance.BorderSize = 1;
                    seat.FlatStyle = seat.Occupied ? System.Windows.Forms.FlatStyle.Popup : seat.FlatStyle = System.Windows.Forms.FlatStyle.Standard;
                    //seat.ForeColor = System.Drawing.Color.White;
                    //seat.FlatAppearance.BorderColor = System.Drawing.Color.Blue;
                    //seat.FlatAppearance.BorderSize = 0;
                    //seat.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    this.Controls.Add(seat);
                }
            }
        } 

        private int[] PositioningSeat(int column, int row, int x, int y)
        {
            if (column == 0 || column == 15)
            {
                x = 60; // starting X position or reseting X to 1st column

                if (row % 5 == 0)
                {
                    y = y + 50; //going to new sector of Y
                }

                else
                {
                    y = y + 37; // next seat for Y
                }
            }

            else if (column % 5 == 0)
            {
                x = x + 70; //going to new sector of X
            }

            else
            {
                x = x + 37; // next seat for X
            }

            return new int[] { x, y };

        }

        private void buttonSeats_Click(object sender, EventArgs e)
        {
            Seat seat = sender as Seat;
            clickedButton = seat.Number;          

            if (seat == null)
                return;

            if (seat.Occupied)
            {
                MessageBox.Show("Seat Number " + seat.Text + " has already been taken!",
                                "Reservation Attention",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Warning);
            }
            else
            {
                if (DialogResult.Yes ==
                    MessageBox.Show("Seat number " + seat.Text + " is free.\nDo you want to reserve it?",
                                    "New reservation",
                                    MessageBoxButtons.YesNo,
                                    MessageBoxIcon.Question))
                {
                    //seat is being reserved...
                    seat.Occupied = true;                    
                    seat.BackColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderColor = System.Drawing.Color.Red;
                    seat.FlatAppearance.BorderSize = 0;
                    seat.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
                    //MessageBox.Show("Reservation confirmed.\nSeat number " + seat.Text + " is now reserved.", "Reservation confirmation", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    using (SqlConnection cn = new SqlConnection(connectionString))
                    {
                        try 
                        {
                            cn.Open();
                            string UpdateString = @"update SampleTable1 set IsBooked = 1 where SeatNo='" + clickedButton + "'";                            
                            SqlCommand cmd = new SqlCommand(UpdateString, cn);
                            cmd.ExecuteScalar();
                        }

                        catch(Exception ex) 
                        {
                            MessageBox.Show("Error: " + ex.Message);
                        }     
                    }
                }
            }
        }

        public class Seat : Button
        {
            private readonly DataRow _row;

            public Seat(DataRow row)
            {
                if (row == null)
                    throw new ArgumentNullException("row");
                _row = row;
            }
            
            public int ID
            {
                get 
                {
                    return int.Parse(_row["SeatId"].ToString());
                }
            }

            public string Number
            {
                get
                { 
                    return _row["SeatNo"] as string; 
                }
                
                set
                {
                    if (value.Length <= 3)
                    {
                        _row["SeatNo"] = value;
                    }
                }
            }

            public bool Occupied
            {
                get
                { 
                    return int.Parse(_row["IsBooked"].ToString()) == 1;
                }
                set 
                {
                    _row["IsBooked"] = value ? 1 : 0; 
                }
            }
        }


        private DataTable GetDataFromDataBase()
        {
            DataSet ds = new DataSet();
            DataTable table;
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = new SqlCommand { CommandText = @"select * from SampleTable1", Connection = cn };
                    da.Fill(ds, "SampleTable1");
                    table = ds.Tables["SampleTable1"];
                }
            }
            return table;            
        }

        private void SetOccupiedSeats(DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                list.Add(new Seat(row));
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                try
                {
                    cn.Open();
                    string ResetString = @"update SampleTable1 set IsBooked = 0 where SeatId<=225";
                    SqlCommand cmd = new SqlCommand(ResetString, cn);
                    cmd.ExecuteNonQuery();
                    Form1_Load(sender, e);
                }

                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message);
                }
            }
        }              
    }
}

The simplest way to do this would be to iterate through list and set Occupied property in each seat to false.
Also, if you move the code that sets the button colour in to the Occupied Set then you can easily make the button match the property value.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.