Stored Procedure for Item Allotment
-
I am making a stored procedure in "SQL SERVER 2008" for "item allotment" Which has a different items and has to assign to particular user. What I am doing is, A table has a three columns 1-Total_item,2-Allotted_item and 3-Available_item , When i click the button ASSIGN it has to assign the item to particular User and the available item must have to be shown in Available_item column Suppose---Before allotment Total_item Allotted_item Available_item 10 0 0 8 0 0 Now After Allotment---- Total_item Allotted_item Available_item 10 1 9 8 2 6 I am going wrong Please help How could I make This happen Thanks
-
I am making a stored procedure in "SQL SERVER 2008" for "item allotment" Which has a different items and has to assign to particular user. What I am doing is, A table has a three columns 1-Total_item,2-Allotted_item and 3-Available_item , When i click the button ASSIGN it has to assign the item to particular User and the available item must have to be shown in Available_item column Suppose---Before allotment Total_item Allotted_item Available_item 10 0 0 8 0 0 Now After Allotment---- Total_item Allotted_item Available_item 10 1 9 8 2 6 I am going wrong Please help How could I make This happen Thanks
How does your item table look like?
Wrong is evil and must be defeated. - Jeff Ello Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
-
How does your item table look like?
Wrong is evil and must be defeated. - Jeff Ello Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
I HAVE 2 TABLES TABLE 1 = "ITEM ALLOTMENT" it has initially "total_item" = 10 or some other value, "allotted_item" = 0 and "item_available" = 0 CONSISTS OF COLUMNS--- ITEM_NAME NVARCHAR(50) TOTAL_ITEM--- INT ALLOTTED_ITEM---- INT ITEM_AVAILABLE--- INT AND TABLE 2 = ITEM it only stores the allocation details after clicking assign button CONSISTS OF COLUMNS--- ITEM_ID (PRIMARY KEY) PRODUCT_NO -- nvarchar(50) ITEM_NAME -- nvarchar(50) now after allotment details are filled in "ITEM" table that this "item_name" is allocated to this "product_no" and what is has to done in "item allotment" table is just after allotment its value must be updated that means column name "allotted_item" value must be increased and in "item_available" column the subtracted value is have to shown example If in "total_item" there is 10 items stored after allocation "allotted_item" value will be increased to 1 and subtracted value will be shown in "item_available" column all these have to be done on assign button click. So, I that i am using Stored Procedure Please help me out ,I am failing to which. Thanks
-
I HAVE 2 TABLES TABLE 1 = "ITEM ALLOTMENT" it has initially "total_item" = 10 or some other value, "allotted_item" = 0 and "item_available" = 0 CONSISTS OF COLUMNS--- ITEM_NAME NVARCHAR(50) TOTAL_ITEM--- INT ALLOTTED_ITEM---- INT ITEM_AVAILABLE--- INT AND TABLE 2 = ITEM it only stores the allocation details after clicking assign button CONSISTS OF COLUMNS--- ITEM_ID (PRIMARY KEY) PRODUCT_NO -- nvarchar(50) ITEM_NAME -- nvarchar(50) now after allotment details are filled in "ITEM" table that this "item_name" is allocated to this "product_no" and what is has to done in "item allotment" table is just after allotment its value must be updated that means column name "allotted_item" value must be increased and in "item_available" column the subtracted value is have to shown example If in "total_item" there is 10 items stored after allocation "allotted_item" value will be increased to 1 and subtracted value will be shown in "item_available" column all these have to be done on assign button click. So, I that i am using Stored Procedure Please help me out ,I am failing to which. Thanks
What is problem you are facing?, You should use the Begin and end trans block and update both tables in the same block on click of assign button.
-
I HAVE 2 TABLES TABLE 1 = "ITEM ALLOTMENT" it has initially "total_item" = 10 or some other value, "allotted_item" = 0 and "item_available" = 0 CONSISTS OF COLUMNS--- ITEM_NAME NVARCHAR(50) TOTAL_ITEM--- INT ALLOTTED_ITEM---- INT ITEM_AVAILABLE--- INT AND TABLE 2 = ITEM it only stores the allocation details after clicking assign button CONSISTS OF COLUMNS--- ITEM_ID (PRIMARY KEY) PRODUCT_NO -- nvarchar(50) ITEM_NAME -- nvarchar(50) now after allotment details are filled in "ITEM" table that this "item_name" is allocated to this "product_no" and what is has to done in "item allotment" table is just after allotment its value must be updated that means column name "allotted_item" value must be increased and in "item_available" column the subtracted value is have to shown example If in "total_item" there is 10 items stored after allocation "allotted_item" value will be increased to 1 and subtracted value will be shown in "item_available" column all these have to be done on assign button click. So, I that i am using Stored Procedure Please help me out ,I am failing to which. Thanks
This isn't normalized. I'd suggest that you drop table1 and add an allotted column to table2 which contains to whom it's allotted (null or ID). Then you use a query to get the state of allottments, such as:
SELECT ITEM_NAME
,Count(ITEM_ID) Total_item
,Count(Alloted) Allotted_item
,Count(ITEM_ID) - Count(Alloted) Available_item
FROM Table2
GROUP BY ITEM_NAMEWrong is evil and must be defeated. - Jeff Ello Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
-
What is problem you are facing?, You should use the Begin and end trans block and update both tables in the same block on click of assign button.
The Problem is in deceasing the value after assigning item.
-
The Problem is in deceasing the value after assigning item.
What is the command/Query you are using to do so and what issue do you face.