JDBC Update Problem!!!
843859Aug 27 2007 — edited Nov 20 2014I am creating a database program that will allow a user to insert, delete and update user information in a database. Everything is working fine except my update statement. My insert and delete and select statements work fine but for some reason I get the error java.sql.SQLException: : [Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or changed because table 'PhoneNumbers' includes related records when trying to update.
So you know my database has four tables names, addresses, phone #'s and email addresses. I know about the inner joining of tables when you have foreign key constraints.
My prepared statement is:
sqlUpdateAll = connection.prepareStatement("UPDATE (([Names] INNER JOIN Addresses ON Names.PersonID = Addresses.PersonID) INNER JOIN EmailAddresses ON Names.PersonID = EmailAddresses.PersonID) INNER JOIN PhoneNumbers ON Names.PersonID = PhoneNumbers.PersonID SET [Names].PersonID = [?], [Names].FirstName = [?], [Names].LastName = [?], Addresses.AddressID = [?], Addresses.PersonID = [?], Addresses.Address = [?], Addresses.City = [?], Addresses.State = [?], Addresses.ZipCode = [?], EmailAddresses.EmailID = [?], EmailAddresses.PersonID = [?], EmailAddresses.EmailAddress1 = [?], EmailAddresses.EmailAddress2 = [?], PhoneNumbers.PhoneID = [?], PhoneNumbers.PersonID = [?], PhoneNumbers.PhoneNumber = [?] WHERE (((Names.PersonID)=[?]));");
Then I do set my values:
sqlUpdateAll.setInt(1, personID);
sqlUpdateAll.setString(2, firstName);
sqlUpdateAll.setString(3, lastName);
sqlUpdateAll.setInt(4, addressID);
sqlUpdateAll.setInt(5, personID);
sqlUpdateAll.setString(6, address);
sqlUpdateAll.setString(7, city);
sqlUpdateAll.setString(8, state);
sqlUpdateAll.setString(9, zip);
sqlUpdateAll.setInt(10, emailID);
sqlUpdateAll.setInt(11, personID);
sqlUpdateAll.setString(12, email1);
sqlUpdateAll.setString(13, email2);
sqlUpdateAll.setInt(14, phoneID);
sqlUpdateAll.setInt(15, personID);
sqlUpdateAll.setString(16, phone);
sqlUpdateAll.setInt(17, personID);
sqlUpdateAll.executeUpdate();
sqlUpdateAll.close();
message = "Status: database updated successfully";
connection.commit();
So I really don't kow what is going. My columns names are 100% correct and everything else that I can think of is there. The person object which has all those variables is full of data...so it is not null. So I am clueless as to why it is not working...please help!?