Sql-server – ‘You cannot add or change a record because a related record is required in table ‘Booking’.’ error when removing a PKFK value in a SQL database in C#

csql server

System.Data.OleDb.OleDbException: 'You cannot add or change a record because a related record is required in table 'Booking'.'

My sub-routine to get rid of the booking with a given BookingID in the Textbox 'TxbID' is given by:

Cmd.CommandText = "SELECT BookingID, Occupied FROM Room WHERE Room.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "DELETE FROM Booking WHERE Booking.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "DELETE FROM Occupant WHERE Occupant.CustomerID = '" + CID + "'";

My table creation code is:

Cmd.CommandText = "CREATE TABLE Occupant(CustomerName VARCHAR, CustomerNumber CHAR(11), CustomerID VARCHAR, PRIMARY KEY (CustomerID))";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "CREATE TABLE Booking(BookingID VARCHAR, DateIn DATE, DateOut DATE, TotalPrice FLOAT, CustomerName VARCHAR, CustomerID VARCHAR, FOREIGN KEY(CustomerID) REFERENCES Occupant(CustomerID), PRIMARY KEY (BookingID))";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "CREATE TABLE Room(BookingID VARCHAR, RoomNumber VARCHAR, RoomSize VARCHAR, RoomPrice FLOAT, Occupied BIT, PRIMARY KEY (RoomNumber), FOREIGN KEY(BookingID) REFERENCES Booking(BookingID))";
Cmd.ExecuteNonQuery();

I'm working on my computer science A-Level project so help is very appreciated.

Edit:

This function only allows number 0-9 to be input so injection shouldn't be an issue here? (The booking ID is an integer)

private void TxbID_TextChanged(object sender, EventArgs e)
{
     if (System.Text.RegularExpressions.Regex.IsMatch(TxbID.Text, "[^0-9]"))
     {
          MessageBox.Show("Please enter only numbers.");
          TxbID.Text = TxbID.Text.Remove(TxbID.Text.Length - 1);
     }
 }

And UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "' is where my error occurs.

My goal is to be able to delete a booking from the database by deleting the row from Occupant and Booking. My Room table is filled with the information for each room along with a BookingID which I wanted to set blank when the booking is deleted. How do I go about that?

Best Answer

"UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "'";

You don't have a Booking record with a BookingID of ''. I suspect you have your relationship the wrong way round; a Booking should have a RoomID rather than a room having a BookingID, or if you want to persist with the current architecture you should perhaps be setting Room.BookingID null

Please read this website, not because it solves your problem, but because you're making the most grave mistake anyone can make when writing database software, and you need to kick the habit now. (If you did it while working for my company, they'd just fire you)

Related Question