0
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?

3
  • Which line of code is specifically throwing the exception?
    – J.D.
    Commented Feb 28, 2021 at 4:17
  • Aside to the actual question, but this sample code is vulnerable to SQL injection attack. Commented Feb 28, 2021 at 10:44
  • I've edited my post^ Commented Feb 28, 2021 at 12:58

1 Answer 1

1

"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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.