asked on

Inserting newly created Primary Key from one table into another via stored procedure

Hello,

I was just wondering if something I was looking to do was doable or not.

I have a stored procedure that will first insert a record into a table (MO_ITEMS) and increment the primary key/generator accordingly.

I then want to take the newly created PK and insert that into another table which will be used as a cross reference (MO_ITEM_CATS).

Here is what I have so far to create the procedure and a trigger for the generator:

CREATE GENERATOR ITEM_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER CREATE_ITEM_ID FOR MO_ITEMS
BEFORE INSERT
POSITION 0
AS BEGIN
NEW.ITEM_ID = GEN_ID(ITEM_ID_GEN, 1);
END^

CREATE PROCEDURE NEW_ITEM
(
VendorID INTEGER,
CatID INTEGER,
ItemName VARCHAR(50),
Details BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
Cost NUMERIC(4,2),
ShowMain SMALLINT,
IsActive SMALLINT
)
AS
BEGIN
INSERT INTO MO_ITEMS (ITEM_ID, VENDOR, ITEM_NAME, DETAILS, COST, SHOW_ON_MAIN, IS_ACTIVE)
VALUES (0, :VendorID, :ItemName, :Details, :Cost, :ShowMain, :IsActive);
INSERT INTO MO_ITEM_CATS (ITEM_ID, CAT_ID) VALUES (:ItemID, :CatID);
END^
SET TERM ; ^

Will I need to set an output parameter for the ITEM_ID or is there a quicker way. I know SQL Server has the @@IDENTITY property and I'd like to do something like that.

Thanks.

ASKER CERTIFIED SOLUTION

Nick Upson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tbinkley

ASKER

Thanks for the reply, Nick.

That looks like a solid solution. I'll give it a try and let you know how it goes.

tbinkley

ASKER

I recreated my stored procedure as suggested, but I keep getting this error from my ASP page when I try to add an item:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/marketing/admin/add_item.asp, line 40

Here is the ASP code I'm using:

itemName = request.Form("itemName")
itemDetails = request.Form("itemDetails")
vendorID = request.Form("vendorID")
itemCat = request.Form("catID")
showMain = request.Form("showMain")
isActive = request.Form("isActive")
itemCost = request.Form("itemCost")

with objCmdItem
.ActiveConnection = objConn
.CommandText = "NEW_ITEM"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter(":ItemID", adInteger, adParamInput, , 0)
.Parameters.Append .CreateParameter(":VendorID", adInteger, adParamInput, , vendorID)
.Parameters.Append .CreateParameter(":CatID", adInteger, adParamInput, , catID)
.Parameters.Append .CreateParameter(":ItemName", adVarChar, adParamInput, 50, itemName)
.Parameters.Append .CreateParameter(":ItemDetails", adLongVarChar, adParamInput, 2147483647, itemDetails)
.Parameters.Append .CreateParameter(":Cost", adNumeric, adParamInput, , itemCost)
.Parameters.Append .CreateParameter(":ShowMain", adSmallInt, adParamInput, , showMain)
.Parameters.Append .CreateParameter(":IsActive", adSmallInt, adParamInput, , isActive)

.Execute, , adExecuteNoRecords

end with

I can get this code to work with other stored procedures that have only one line of SQL, but I've been getting the error posted above with each multi step operation. Will I have to break them apart into separate stored procedures (which I'd rather not do)?

tbinkley

ASKER

Sorry, forgot this was the Interbase forum and not ASP. If anyone can help with the above post, cool. If not, I'll just try the ASP forum.

Nick Upson

the error may be due to specifying itemid as an arg instead of an variable or calling them in the wrone order, the list in your asp code is not in the same order as the
definitions (I don't know ASP)

CREATE PROCEDURE NEW_ITEM
(
VendorID INTEGER,
CatID INTEGER,
ItemName VARCHAR(50),
Details BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
Cost NUMERIC(4,2),
ShowMain SMALLINT,
IsActive SMALLINT
)
itemid integer

SOLUTION

BAlexandrov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial