|
Subject:
|
Newbie stored procedure questions
|
|
Posted By:
|
harpua
|
Post Date:
|
10/19/2004 3:33:38 PM
|
Question 1. See code below. First off I am new to stored procedures so this may be wishful thinking, but I would appreciate any help on this in understanding it better. What I am trying to do is Insert the item requested into one table and Select it's ID(this part works), then I want to Select the current stock form my Inventory table and return that value through the Response.Write .Parameters("@intOnOrderCount") or does this need to be a Recordset, I am only returning one value? What I don't understand is how to link more than one table up to a stored procedure, I have tried alot of stuff but ended up here.
Question 2. The ultimate thing I want to do is have the user request an item. Then the database will Insert this information. From there Select the requested items inventory and subtract it, if the inventory is below a certain count I want it to return a value so I can send a notification email to me. Is this even possible before I continue on?
'ASP CODE
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = strConnect
.CommandText = "sp_InsertItems"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@InPut_OrderID", adInteger, adParamInput, 4, NewOrderID)
.Parameters.Append .CreateParameter("@InPut_OrderID", adInteger, adParamInput, 4, UserID)
.Parameters.Append .CreateParameter("@InPut_Item_ID", adInteger, adParamInput, 4, ItemID)
.Parameters.Append .CreateParameter("@InPut_QtyOrdered", adInteger, adParamInput, 4, QtyOrdered)
.Parameters.Append .CreateParameter("@intItemOrderedID", adInteger, AdParamOutput, , 0)
.Parameters.Append .CreateParameter("@intOnOrderCount", adInteger, AdParamOutput, , 0)
.Execute
'Selected @@Identity--This Works
Response.Write .Parameters("@intItemOrderedID")
'Inventory Count from another table, this doesn't work
Response.Write .Parameters("@intOnOrderCount")
End With
Set objCmd = Nothing
'Stored Procedure
Create PROCEDURE sp_AddItemsToOrder
(
@InPut_OrderID int,
@InPut_UsrID int,
@InPut_Item_ID int,
@InPut_QtyOrdered int,
@intItemOrderedID int OUTPUT,
@intOnOrderCount int OUTPUT
)
AS
BEGIN
INSERT INTO ItemsTable
(
Order_ID, Usr_ID, Item_ID, Qty
)
VALUES
(
@InPut_OrderID, @InPut_Usr_ID, @InPut_Item_ID, @InPut_QtyOrdered
)
Declare @ItemOrderedID int
SELECT @ItemOrderedID = @@IDENTITY
SET @intItemOrderedID = @ItemOrderedID
'How do I get this to link up with @intOnOrderCount????
SELECT OnOrder
FROM Inventory
WHERE Item_ID = @InPut_OrderID
END
GO
Thanks In Advance
Mike
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/19/2004 3:49:59 PM
|
Hi Mike,'How do I get this to link up with @intOnOrderCount????
SELECT @intOnOrderCount=OnOrder
FROM Inventory
WHERE Item_ID = @InPut_OrderID Hope this is what you are looking for. If not, explain what does @intOnOrderCount mean here, and how does that link with the data present in the Inventory table, may be with some sample data.
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
harpua
|
Reply Date:
|
10/19/2004 4:13:49 PM
|
Thanks a million, that worked! One more question, Is this doable through a stored procedure?
Question 2. The ultimate thing I want to do is have the user request an item. Then the database will Insert this information. From there Select the requested items inventory and subtract it, if the inventory is below a certain count I want it to return a value so I can send a notification email to me. Is this even possible before I continue on?
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/19/2004 4:57:40 PM
|
Yes, you can do that. It is possible.
_________________________
- Vijay G
Strive for Perfection
|
|
