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 2 of 2
  1. #1
    New Coder
    Join Date
    Mar 2005
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access SQL INSERT

    I'm new to SQL, so forgive my ignorance.

    I have a number of related tables. Of these there are 3 which are of concern to my problem: Invoices, InvoiceItems and Products. There is a 1:M r/ship between Invoices as well as between Products and Invoice items. The Invoice items table has InvoiceItemID, InvoiceID, ProductID, Quantity and Price.

    I would like to add 3 InvoiceItems as records in my InvoiceItems table and i would like to have them related to one Invoice (the last Invoice in my case) in the invoices table, that is, also the field in the InvoiceItems called InvoiceID for these 3 records should have the same InvoiceID. All the Primary Keys are set to AutoNumber.

    These are the the statements i used:
    INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
    VALUES (4, 8, 1, 0);

    INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
    VALUES (4, 9, 4, 0);

    INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
    VALUES (4, 10, 2, 0);



    I would like to do this using some sort of INSERT SQL statements, but it's just not working. I can INSERT one InvoiceItem, but when i try to INSERT the next one i get some long error saying something about "type conversion failure","key violations", "lock violations" and "validation rule violations"

    Does anyone know why this is happening and how to correct it?
    Any help would be much appreciated.

  • #2
    New to the CF scene
    Join Date
    Oct 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In invoiceItems are InvoiceID and ProductID 2 diffrent keys or a combination key.


  •  

    Posting Permissions

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