Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

JDBC Update Problem!!!

843859Aug 27 2007 — edited Nov 20 2014
I 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!?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 24 2007
Added on Aug 27 2007
9 comments
1,116 views