stock control

[salim]

QUOTE]Message Forwarded From salim

Message Forwarded From salimhI ALL !i WOULD BE VERY GREATFUL IF YOU COULD SHOW ME HOW TO APPLY INSERT, UPDATE AND DELETE TO SEVERAL TABLES IN MY DATABASE IN ONLY ONE EVENT, THERE IS TRIGGER IN SQL, BUT i WOULD LIKE TO USE COMMAND PROCEDURES, ANYWAY I HAVE AN EXAMPLE TO SHOW YOUI have the following tables in my access DB;tbl_Stock ( stocID Primary Key)tblProducts_in_stock ( a result of m:m relationship between tbl_stock andtbl_Product)tbl_product (productID PK)tblProduct_sold ( a result of m:m relationship between tbl_sales andtbl_Product)tbl_sales (saleID PK)I have a stock control system that I want to have under control, i.e whenproduct is sold it will be ( deleted or updated ) from tbl_stock and insertedinto tbl_sales this way the system works well.I am using dreamweaver and I use stored command procedure that have generatedthis to me:<%var Command1 = Server.CreateObject("ADODB.Command");Command1.ActiveConnection =MM_reMovies_STRING;Command1.CommandText = "INSERT INTO Product_sold ( SaleID,ProductID ) SELECT Productsold.SaleID, Productsold.ProductID FROMProduct_sold";Command1.CommandType = 1;Command1.CommandTimeout =0;Command1.Prepared = true;Command1.Execute();%> <%var Command2 = Server.CreateObject("ADODB.Command");Command2.ActiveConnection =MM_reMovies_STRING;Command2.CommandText = "DELETE Product_sold.ProductID FROMProduct_sold ";Command2.CommandType = 1;Command2.CommandTimeout =0;Command2.Prepared = true;Command2.Execute();%> this is the message I get just when I put command1 ( insert)Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] The changes you requested to the tablewere not successful because they would create duplicate values in the index,primary key, or relationship. Change the data in the field or fields thatcontain duplicate data, remove the index, or redefine the index to permitduplicate entries and try again. /salem/TMPn0fd0ydlsw.asp, line 11 it seems that I can find my way out if this, I aam trying a lot but I can't. isthere anyway that I can follow, o scrip that I can write in sql ? is there anyproblem with my talbles?thanks________________________________________________________ Thanks,

[justsomeguy]

First off, those aren't stored procedures, they are just SQL queries. The problem is that you are trying to enter a row with a certain primary key, and the primary key already exists in the table. Find out which column or columns make up the primary key, and I'm sure you'll have your answer. If necessary, just make a new column to be the primary key and have it auto-increment.