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

[sock1992]

When signing up data will be stored within two tables. customerLogin and customer.

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

 {                
                    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:

The code for this is below:

 }          

           
            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.

Document

	Name	Value	Type
▶	$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

 

[Skydiver]

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:

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.