Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jan 2003
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Primary and foreign key problems in asp

    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?

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    how is the relationship defined, or: which table holds the primary key from the other table as a foreign key.
    I am the luckiest man in the world

  • #3
    Regular Coder
    Join Date
    Jan 2003
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Branch = Branch_Code, Branch_Address, Branch_Postcode,
    Branch_Email, Branch_Phonenumber

    Customer= Customer_No, Branch_Code, Resource, Future_News,
    Interested-Topic

    Customer Contact Details= Customer_No, Customer_Name,
    Customer_Address,
    Customer_Phonenumber,
    Customer_Email



    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.

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    what data is filled in the form by the user (which columns in the db)?
    which sql-statement are you using?
    I am the luckiest man in the world

  • #5
    Regular Coder
    Join Date
    Jan 2003
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 & "')"

  • #6
    Regular Coder
    Join Date
    Jan 2003
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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
    I am the luckiest man in the world

  • #8
    Regular Coder
    Join Date
    Jan 2003
    Posts
    148
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

    Thanks

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!
    Last edited by whammy; 02-15-2003 at 02:32 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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
    I am the luckiest man in the world


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •