Insert into a table that has Foreign Keys

[ShabRaza]

Hi, I am trying to insert a new record into my table Products. The insert is done via a windows forms application that i am developing using vb.net. I am having problems trying to insert a new record into the Products table. The SQL code is not functioning properly. The Products table has 2 foreign keys and this is where i am having the problem i think. The table structures are as follows: Products: PK_ProductID, ProductName, FK_SupplierID, FK_CategoryID, QtyPerUnit LK_Categories PK_CategoryID, CategoryName, Description LK_Suppliers PK_SupplierID, SupplierName, Address, PostCode I have created a query call ProductDetails that is used to add a new product item. The user enters the details of the new product: ProductName, SupplierName, CategoryName, QtyPerUnit and the Products table should then be updated. I have used the CategoryName and SupplierName fields instead of the FK_CategoryID and FK_SupplierID as the ID's are just auto numbers and would bear no identity to the user. However i am having extreme difficulty in getting an INSERT statement to work. Please can someone help me?

[PIEBALDconsult]

Do the category and supplier exist before you try to add the product? What code are you using to perform the insert?

[ShabRaza]

yes they do exist. The LK_Suppliers table and the LK_Categories table have the values stored already. Both these tables are lookup tables, where the suppliers and categories details are stored. The products table uses the ID's from the LK_Categories and the LK_Suppliers tables as each product item has a category and supplier. Currently the INSERT Statement that i am using is as follows:

'"INSERT INTO Products (ProductName, SupplierName, CategoryName, QryPerUnity)

VALUES('" & Trim(txtProdNameIns.Text) & "', '" & Trim(SupplierNameComboBox.Text) & "' , '" & Trim

(CategoryNameComboBox.Text) & "' ,'" & Trim(txtQPUIns.Text) & "')"

However when i run this code in my application the following error comes up: "The INSERT INTO Statement contains the following unknown field name: 'SupplierName'. Make sure you have typed the name correctly, and try the operation again." Any advise with examples would be most appreciated. Thanks

[Ashfield]

It sounds like Products does not have a column called SupplierName. I suspect it is actually SupplierID if its a FK to the Supplier table.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[ShabRaza]

Yes that is true. I have created a query that displays the category name and supplier name, i did this because the FK_CategoryName and FK_SupplierName in the Products table are auto numbers and would not mean anything to the customer. I have linked the Products table, LK_Categories, LK_Suppliers with the ID's however i want to display the category names and supplier names to the user. Then when the user wants to add a product item i want it to insert a line in the Products table, but where i am going wrong is that the FK_CategoryID and FK_SupplierID are foreign keys in the Products table are that is not referenced in my SQL INSERT statement. I hope you understand my point. I am desperate to get this working, please help.

[PIEBALDconsult]

Before you insert the product you need to translate the category and supplier to get their IDs.

[Ashfield]

What you have done to display the nformation is correct, but what you need to do id translate the supplier and category names back to their ID values to insert into the product table. YOU have to do the lookup and supply the correct values for the insert. Personally I would be doing the insert in a stored procedure and would look up any FK values in the stored proc. Hopefully this makes sense

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP