Slight modification needed
-
I received great help on this from David Burrows (Thanks again David) but I am having slight problems with it.
The way it is intended to work is that tblstatus table has prepopulated data and tbltask table also has prepopulated data.
The prepopulated data for status table is.
statusID 1 for 'Not Started', 2 for 'Started', 3 for 'In Progress', 4 for 'On Hold', and 5 for 'Completed'.
Once a new project is created, the project has a default statusID of 1 which means 'Not Started' and the project can go through each until all tasks are completed meaning statusID 5 is reached.
It does this great.
The problem now though is that when more than two projects are being worked on concurrently and let's assume that the first project has a task that has a statusID of 1 which means 'Not Started', any additional tasks being worked will not see the statusID 1.
What this means is that once the first task from project A has a status of 'Not Started', any subsequent tasks cannot be assigned the status of 'Not Started' because that status is no longer available on the status List.
The stored proc does not allow for the concurrent use of any particular status.
Is there anyway to change this stored proc to allow for concurrent usage of one status by the more than one task.
Here is the current sp.
Thanks in advance.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertNProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertNProject]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Procedure spInsertNProject(
@requestorname varchar(50),
@requestorLoc varchar(50),
@dueDate datetime,
@ProjectType varchar(50),
@projName varchar(50),
@justification varchar(1000),
@reqDate datetime,
@description varchar(2000),
@Assigned varchar(50),
@Priority varchar(50),
@EstCompDate datetime,
@name varchar(50))
AS
Declare
@ProjectID int
/* Create and populate new Project record */
Insert into nettblProjects(
Section,
requestorName,
requestorLoc,
division,
startDate,
projName,
justification,
mapTitle,
requestedCompletionDate,
email,
description,
estimatedCompletionDate,
AssignedTo,
Priority,
Purpose,
Geographic,
fullName,
Manager,
PR_Percent)
VALUES(
'Networking',
@requestorName,
@requestorLoc,
'Not Applicable' ,
@dueDate,
@projName,
@justification,
'Not Applicable',
@reqDate ,
'james@hotmail.com',
@description,
@EstCompDate,
@Assigned,
@Priority,
'NA',
'NA',
@name,
'James Bond',
0)
/* Move the identity value of the new record into a variable */
SET @ProjectID = @@IDENTITY
/* Create and populate new tblprojecttasks record */
INSERT INTO NettblProjectTasks (
Project_ID,
TaskID,
statusID
)
SELECT @ProjectID,
t.taskID,
s.StatusID
FROM (SELECT b.taskID FROM NettblProjectType a INNER JOIN NettblTypeTask b ON b.typeID = a.typeID WHERE a.projectType = @ProjectType) t
CROSS JOIN tblstatus s
where s.statusID = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Are you saying that you cannot call the proc more than once? Are you getting errors from the proc? What do you mean by 'because that status is no longer available on the status List'?
I am presuming that you have a PK on NettblProjectTasks of Project_ID, TaskID, statusID !
Far away is close at hand in the images of elsewhere.
Anon. -
hi David, good to hear from you again!
Recall that the stored proc is intended to associate tasks to a project based on project type.
Recall also by looking at the attached stored proc that the first time a project is created, by default, the statusID is set to 1 meaning 'Not Started'.
As you work through the project, you will continue to update it based on where you are at.
For instance, if at the end of the day, your task is progressing well, but not completed, you can update it by selecting the 'In Progress' status option.
That works well.
But what is happening is that if you are working on more than one projects and in one project, you update the task by using the 'Not Started' option or any of the 5 status options, if you go another project you are working on and attempt to update the task of that project with the option you have already selected for the task of one of your other projects you are working on concurrently, that option is no longer available.
What that means is that the status options cannot be used concurrently.
If you are using one status to update a task, that status cannot be used to update task from another project because that option is no longer available.
How can I make the status opion, say for instance 'Not Started' be used more than once at the same time?
I hope my explanation is clearer; please let me know if it is still murky.
-
I am still not certain where the problem lies. You use the NettblProjectTasks table to control what tasks belong to a project and what status the task is, e.g.
Project_ID TaskID statusID
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1
indicates that two projects (1 & 2) have tasks (1,2,3) assocuated with them and all the tasks are status 1.
if you insert another record
Project_ID TaskID statusID
1 1 2
you will end up with
Project_ID TaskID statusID
1 1 1
1 1 2
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1
which will indicate that project 1, task 1 had been status 1 then status 2
or you could update the original record
Project_ID TaskID statusID
1 1 1
to
Project_ID TaskID statusID
1 1 2
which will indicate that project 1, task 1 has status 2 (being it's current status)
If you have a PK on this table consisting of all 3 columns (Project_ID, TaskID, statusID) then you can store any combination. Only a restrictive PK or non unique index will stop you.
Sorry if this seems basic bit I am trying to understand your problem
Edited by - davidburrows on 04/15/2003 06:11:14 AM
Far away is close at hand in the images of elsewhere.
Anon. -
thanks again David!
Table tblprojecttasks has the following:
projecttaskid int pk identity,
statusID int --foreign key from status table,
taskid int -- foreign key from task table,
project_id int -- foreign key from project table.
As you can see, pk is unique and doesn't appear restrictive but yet as indicated earlier,
once a status is updated for one task belonging to one project, that status disappears from from the status list and is no longer available for use in updatin another task from another project.
Puzzling!!!
-
Ah I think the fog is beginning to clear a bit. I understand the tblprojecttasks table now and it should not give you an problems. I personally do not put an identity on this type of table (reference) but make make all the cols the PK but then thats me.
I still want to understand the update and the 'that option is no longer available' problem. You must be using another query or proc to do the status change for a particular task for a project! Can u post that proc / code?
Far away is close at hand in the images of elsewhere.
Anon. -
I think I am beginning to narrow down where the problem is coming from myself, thanks in large part to you.
I am using a fron end tool (ASP) to do update the status.
When I say status options, I mean, there is a dropdown list with the 5 option lists from 'Not Started' to 'Completed'.
I was hoping stored proc is where the problem is coming from because it is usually easier to fix with sp, knowing I can count on people like you to help than ASP.
I will make the 3 cols in projectasks all pks, though as you already know, that is the problem from what you have seen and said.
Oh, boy!
-
This problem won't go away.
I have been trying to fix the asp part of this code (please read the problems I have been having in prior posts) but it is not working.
I was wondering if anyone can give me a hint on how to accomplish this with a stored proc.
I know how to call a stored proc with asp.
I just need to know how use stored proc to accomplish the same thing I am to accomplish with embedded query in asp.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply