View Full Version : Primary and foreign key problems in asp
02-11-2003, 03:24 AM
hi again me
I have been using insert into sql statement to insert data to a table created in Access. I have done all the coding but now it gives me the error of :
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'Branch'.
where I can understand the primary and foreign key relation is causing a problem. However the data in Branch table is not inserted it was put there by the administrator where in the Customer table the data is inserted by a customer by filling in the form on the web site.
I don't know how to solve this problem as I need to keep the relation and it is causing me problems.
Does anyone have any idea?
02-11-2003, 07:27 AM
how is the relationship defined, or: which table holds the primary key from the other table as a foreign key.
02-11-2003, 03:20 PM
Branch = Branch_Code, Branch_Address, Branch_Postcode,
Customer= Customer_No, Branch_Code, Resource, Future_News,
Customer Contact Details= Customer_No, Customer_Name,
In my ASP page I have a form that user enters data that should be inserted into the database. However it doesn't let me beacuse Branch_Code is a primary key in table Branch.
The details to the Branch is already inserted by administrator it is nothing to be inserted by the user.
In table Branch primary key is Branch_Code
In table Customer there is a composite primary ket which is Customer_No with Branch_Code
In Customer Contact Details the primary key is Customer_No.
02-12-2003, 08:13 AM
what data is filled in the form by the user (which columns in the db)?
which sql-statement are you using?
02-12-2003, 08:26 PM
All the coloumns in the Customer and Customer Contact Details is filled by the user apart from Customer_No as this is an autonumber created by the system. I kept is autonumber in the Customer and string in Customer Contact Details and my sqol ststament is :
strSQL = "INSERT INTO [Customer Contact Details](Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('" & strCustomer_Name & "','" & strCustomer_Address & "','" & strCustomer_Postcode & "','" & strCustomer_Phonenumber & "','" & strCustomer_Email & "')"
02-12-2003, 08:27 PM
and the second sql statement is
strSQL2 = "INSERT INTO Customer (Branch_Code, Resource, Future_News, Interested_Topic) Values ('" & Branch & "','" & Resource & "','" & Future_News & "','" & Interested_Topic & "')"
Hope that helps cos I am stuck with it :(
02-13-2003, 12:47 PM
do you present the branche-code as a dropdown or is it a text-field. probably the value for the branche code entered is not a valid id in the branche table
02-13-2003, 01:13 PM
Branch_Code in Customer table is a combo box in the design and the user chooses from a drop down menu to enter the branch into the database.
Branch_Code in Branch Table is a combo box as well. Do u think the problem is because of keeping them combo box instead of text box in the design. If that's the case do u think it'll be fixed if I change them into text in design and still keep the drop down menu in the page for the user to choose and insert the value into the database?
02-15-2003, 02:29 AM
Go to your database diagram in Enterprise manager and delete the specific table relationship that is causing the foreign key error... not sure how that will affect your entire application because it depends upon what you're trying to do, but that will get rid of the error! I'd back up all of your databases if you are concerned.
I ran into a problem with this error when trying to assign a value to the "foreign key" that didn't exist, since the foreign key was an autoincrement (or primary key) in another table.
The problem was the tables were related in SQL Server itself, so if the "foreign key" doesn't exist as a "Primary Key" in the related table, it will throw an error. I don't know if this helps, but it should give you a research point, such as I have!
02-15-2003, 10:52 PM
that would be one way of getting rid of the error, throwing away the thing that is keeping the integrity of your data.....
Personally i would response.write the sql which gives the error, then look if the values you try to enter in the database are valid values, is the branche_id you try to insert a valid id in the branche table. If not, debug the way the drop down is generated. my guess is thats the spot where things go wrong