Resolved Getting error message regarding a foreign key when inserting data into a table.

sock1992

Well-known member
Joined
May 20, 2020
Messages
107
Programming Experience
Beginner
When signing up data will be stored within two tables. customerLogin and customer.
1616497782728.png
1616497884907.png


The code for this is shown below, it works the way i want it to, inserting the correct details into both tables.

C#:
 {                
                    string query_1 = "Insert into customer  (firstName, lastName, address, contactNumber) values ( @firstName, @lastname, @address, @contact)";
                    string query_2 = "select customerId from customer where customerId = (select max(customerId) from customer)";
                    string query_3 = "Insert into customerLogin (email, password, customerId) Values (@email, @password, @customerId)";

                    _IsqlDataFunctions.GetConnection().Open();
                    cmd = new SqlCommand(query_1, _IsqlDataFunctions.GetConnection());
                    cmd.Parameters.AddWithValue("@firstName", txtFirstName.Text);
                    cmd.Parameters.AddWithValue("@lastName", txtLastName.Text);
                    cmd.Parameters.AddWithValue("@address", txtboxAddress.Text);
                    cmd.Parameters.AddWithValue("@contact", txtboxContact.Text);
                    cmd.ExecuteNonQuery();
                   
                   
                    cmd = new SqlCommand(query_2, _IsqlDataFunctions.GetConnection());
                    var customerId = cmd.ExecuteScalar();
                    userDetails.Add(customerId.ToString());
                   
                   
                    cmd = new SqlCommand(query_3, _IsqlDataFunctions.GetConnection());
                    cmd.Parameters.AddWithValue("@email", txtEmailAddress.Text);
                    cmd.Parameters.AddWithValue("@password", SqlDataFunctions.hashPassword(txtPassword.Text));
                    cmd.Parameters.AddWithValue("@customerId", customerId);
                    cmd.ExecuteNonQuery();
                    _IsqlDataFunctions.GetConnection().Close();
                    MessageBox.Show("You have been registered succesfully");    
                    clearFields();
                }

When the user creates a booking I want to insert the following data into a bookings table:
1616498077046.png


The code for this is below:
C#:
 }          

           
            string query = "Insert into Bookings (seatNo, ticketType, price, dateOfPurchase, customerId, scheduleId)  VALUES ( @seatNo, @ticketType, @price, @date, @customerId, @scheduleId )";

            _IsqlDataFunctions.GetConnection().Open();
            cmd = new SqlCommand(query, _IsqlDataFunctions.GetConnection());
            cmd.Parameters.AddWithValue("@seatNo", selectedSeatNo);
            cmd.Parameters.AddWithValue("@ticketType", txtTicketType.Text);
            cmd.Parameters.AddWithValue("@price", txtPrice.Text);
            cmd.Parameters.AddWithValue("@date", dateOfPurchase.Text);
            cmd.Parameters.AddWithValue("@customerId", SignIn.userDetails[0]);
            cmd.Parameters.AddWithValue("@scheduleId", txtSchedule.Text);
         
                   
            if (n > 1 || selectedSeatNo == null)
            {
                MessageBox.Show("Please ensure one seat has been selected");
            }
            if (n == 1)
            {

                cmd.ExecuteNonQuery();
                _IsqlDataFunctions.GetConnection().Close();        
                addBookingPanel.BringToFront();
                UserControlManager.ShowUserControl(addBookingPanel, new User_Control.Uc_inputtingPaymentDetails());
               
            }


This is where I'm getting my exception, and i honestly have no idea why. I feel that it is something simple but i just don't know what it is. I've delete the customer table and customerId foreign key and create a new one, but this didn't fix it.
I removed the customerId value and executed the query and the rest of the data stored fine i.e seatNo, ticketType, price, dateOfPurchase and scheduleId (see image below). its just the customerId thats giving me an issue.

1616498925811.png


Document
NameValueType
$exception{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Bookings_customer\". The conflict occurred in database \"TGCSBookingSystem\", table \"dbo.customer\", column 'customerId'.\r\nThe statement has been terminated."}System.Data.SqlClient.SqlException


1616498530050.png
 
The customerId parameter that you pass in MUST be equal to a valid existing customer ID in the customer table. Are you sure that SignIn.userDetails[0] contains such an id?

Also as an aside: this in fraught with peril, specially if you ever get into multiple client machines, or multiple threads:
C#:
string query_2 = "select customerId from customer where customerId = (select max(customerId) from customer)";
Search on Google on the correct way to get the autoincrement values after performing an insert.
 
Back
Top Bottom