I am working on a small project that my boss thinks should take no more than 1 week.
I have a category called Projects and a subcategory called Tasks
The requirement is to select a project and the tasks associated with that project; then implement each task as listed in alphabetical order.
In order words, if the first task is db design, then that task must be implemented first before moving to the next task.
If a developer attempts to move to the next task without completing the first task, he/she is refused until the first task on the list is completed.
Once a task is completed, a check mark is placed on that task and you are automatically moved to the next task.
I have been doing asp for a little over a year but I am lost on this one.
Anyone's help will BE GREATLY APPRECIATED!
Ribeyed
02-24-2003, 06:12 PM
hi,
do you have a database ready for this or do you need to develop that as well?
What you need to do can be easy or hard, this I think will depend on how well you develop the database structure. I can see you will need 2 tables, 1 for projects and 1 for tasks. I would presume that a project can have many tasks but a task can only belong to one project so this is a one-to-many relationship. You didn't say if a project will be assigned to more than one developer, but i would again presume that there is more than 1 developer, therefore you will need another table for developers. Your developers are going to be working on more than one project, but i am not sure if more than 1 developer can be working on the same project i will presume that the later is correct. There would be a many-to-many relationship with the Developer and the project table therefore you will need a relationship table for the many-to-many situation. You can call this table "tbldeveloperProject" .
If you’re database has been developed then can you provide some tables for us to work with?
Post a reply and we can start working on some code to work the database.
janice
02-24-2003, 08:48 PM
hi Ribeyed!
You are right in almost everything except that sadly enough, no matter how big a project is here, only one developer is assigned that project, that is why the requirement to complete one task before moving to the next is required.
In any case, there are two tables involved - a task table and a project table and yes it is a 1:m relationship (one project, many tasks).
The project table structure.
Project_id identity seed, int,
projName varchar(50),
status varchar(50),
priority varchar(50),
startDate datetime,
requestedCompletionDate datetime,
estimatedCompletionDate datetime
Task table structure:
taskID identity seed int,
project_id -- from the project table,
taskname varchar(50),
startDate datetime,
endDate datetime,
dateCompleted datetime,
status varchar(50),
issues varchar(1000).
This is pretty much the structure. This is done in sql server.
Although I can convert to sql server if it is more convenient to do this in access.
I must also add that what makes this very complex, at least from my perspectives, is that the task info, according to my boss, must be displayed in the edit screen of the project.
In other words, when a project record is being created via insert statement, only project info should be created.
Once a project record is created, you are redirected to the "View Project Listings" screen.
From there, you can click on "Edit" to edit project info and there, according to him, he wants to see task info.
Thanks for the help!!!.
Please feel free to add to the project/task tables.
j
janice
02-25-2003, 06:40 AM
thank you very much Dave.
I have the project list page and the add page.
Should I post those?
janice
02-25-2003, 07:37 AM
hi!
Thanks again!
I have zipped those and one more dependent file called edit.asp.
As you can see from these files, the biggest problem from my perspective is to add the task functionality to the edit screen and ensure that all tasks are listed and with checkmark will be checked once each task is completed and to ensure that no task is skipped for another until its completion.
Please remove or uncomment the first few lines of code that check each user's access level at the top of each page.
thanks again soooo much.
janice
02-27-2003, 11:43 AM
hi Dave!
I am not sure if you have had time to look over the files I zipped but I have two files that will be satisfactory for my boss if I can get them to work.
I have them seperately.
One is a javascript file that lists all tasks associated with a particular project.
For instance, let's say that there is a project called TravelAndTraining, and the tasks named in the js file are the tasks associated with that project,
I will like to have this js integrated with the asp code such that when a project is displayed for editing, all the tasks associated with it are displayed with checkboxes besides them.
The way this js file is code is that one the current checkbox is enabled while the rest are disabled.
Once a ckeckmark is placed on the current checkbox, the next checkbox is enabled for processing but not before.
This works fine, so does the edit.asp file that is included.
I am just trying to find a way to integrate them.
If you or any one of the experts can help me out on this, I can use what I have now in terms insert.asp, view.asp and go with those.
Thank you for all the help!!
taskpage.htm:
***************
<HTML>
<HEAD>
<TITLE>enable</TITLE>
<script language="javascript">
<!--
var numOfBoxes = 8;
function enable(inBox) {
if (!document.forms['myForm']['cb' + inBox].checked) {
// If the user unchecked a box, then disable all after it
for (var i = inBox + 1; i <= numOfBoxes; i ++) {
document.forms['myForm']['cb' + i].checked = false;
document.forms['myForm']['cb' + i].disabled = true;
}
}
else {
// User checked a box, so enable the next
document.forms['myForm']['cb' + (inBox+1)].disabled = false;
}
}
//-->
</script>
</td></tr></table>
</form></center>
<%
RecordSet.Close
Set RecordSet = Nothing
Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing
%>
janice
03-09-2003, 02:58 PM
Implementing a task step by step
2 weeks ago, I threaded this question on this helpful forum about how to insert a task record into the task table while in the project edit screen.
The requirement I am working on calls for the implementation of 5 functionalites.- To add a project, delete a project, view project list, search for a specific project, and to update a project.
All of these functionalities have been implemented except the update a project functionality.
Although the update a project functionality is implemented and works fine, our client wants to change it slightly so that when you click the 'edit a project' button, and the edit screen opens up for editing, to have all tasks associated with a particular project be displayed also.
For instance, you open up an edit screen to edit projectID 1 and project name called test1, then all tasks associated with that project needs to be displayed on this edit screen so that as you completed each task, you will be saving it along with any changes made to any project info.
I have come up with a javascript that will list all tasks associated with a particular project and each has a checkbox that allows the user to place a checkmark on any checkbox of a task that has been completed.
Implementing this task has been a challenge.
Can someone please help me in implementing this functionality.
I have the viewprojectList and edit a project codes.
Please if anyone is interesting in helping and needs these codes, please so I can send them.
I hope I am clear in describing the problem.
I have also included the table structures for project and task tables.
A task table and a project table and yes it is a 1:m relationship (one project, many tasks).
The project table structure.
Project_id identity seed, int,
projName varchar(50),
status varchar(50),
priority varchar(50),
startDate datetime,
requestedCompletionDate datetime,
estimatedCompletionDate datetime
Task table structure:
taskID identity seed int,
project_id -- from the project table,
taskname varchar(50),
startDate datetime,
endDate datetime,
dateCompleted datetime,
status varchar(50),
issues varchar(1000).
This is done in sql server.
Thanks in advance
Ribeyed
03-09-2003, 07:48 PM
hi Janice,
I know you have spent a lot of time on this so i have put in a few hours to try to help solve your problem. I'm sorry if this code is not exactly what you’re looking for but it is my interpretation of what information you have supplied in your post and my own experience of what clients need. You should be able to work in your own code to the code I have given you. If you need anything modified or its not anywhere near what you are looking for just let me know what I can do to change the code. Anyway hope this helps you out, maybe it will just help you come up with a solution yourself.
You will have to change the connection i used a DNS, and i think i called my tables slightly different names from yours
<body>
<%
set DBConn = Server.CreateObject("ADODB.Connection")
DbConn.Open "project"
val = request.form("val")
if val = "" then
%>
<form name="form1" method="post" action="/projecttask.asp">
<select name="sltProjects">
<%
sql = "SELECT tblProjects.Project_Id, tblProjects.ProjName FROM tblProjects "
Set RS = DbConn.Execute(sql)
While not RS.EOF
%>
<option value="<%= RS(0)%>"><%=RS(1)%></option>
<%
RS.MoveNext
Wend
Rs.Close
Set RS = nothing
%>
</select>
<input type="submit" name="Submit" value="Submit">
<input name="val" type="hidden" id="val" value="selected">
</form>
<%elseif val = "selected" then
projupdate = request.form("projupdate")
if projupdate = "yes" then
for each item in request.Form
if item = "Submit" then
ProjName = request.form("ProjName")
ProjStatus = request.form("ProjStatus")
StartDate = request.form("StartDate")
requestedComDate = request.form("requestedComDate")
EstimateComDate = request.form("EstimateComDate")
Project_ID = request.form("sltProjects")
set UpdateSet = Server.CreateObject("ADODB.RecordSet")
UpdateSet.Open "SELECT * FROM tblProjects WHERE tblProjects.Project_id = "&Project_ID&"", DBConn, 2, 2, &H0001
If not UpdateSet.EOF then
UpdateSet("ProjName") = ProjName
UpdateSet("Status") = ProjStatus
UpdateSet("StartDate") = StartDate
UpdateSet("requestedCompletionDate") = requestedComDate
UpdateSet("estimatedCompletionDate") = EstimateComDate
UpdateSet.Update
UpdateSet.close
set UpdateSet = nothing
end if
message = "Changes made to project details"
elseif item = "ProjName" or item = "ProjStatus" or item = "StartDate" or item = "requestedComDate" or item = "EstimateComDate" or item = "val" or item = "projupdate" or item = "sltProjects" or item = "TaskName" or item = "StartDate" or item = "EndDate" or item = "DateCompleted" or item = "Taskstatus" or item = "taskissues" or item = "taskStartDate" then
else
taskitem = left(item,Len(item) - 1)
if taskitem = "Edit" then
newitem = Right(item, 1)
else
updateitem = Right(item, 1)
set UpdateSet = Server.CreateObject("ADODB.RecordSet")
UpdateSet.Open "SELECT * FROM tbltasks WHERE tbltasks.taskid = "&updateitem&"", DBConn, 2, 2, &H0001
If not UpdateSet.EOF then
TaskName = request.form("TaskName")
if TaskName <> "" then
UpdateSet("TaskName") = TaskName
end if
taskStartDate = request.form("taskStartDate")
if taskStartDate <> "" then
UpdateSet("StartDate") = taskStartDate
end if
EndDate = request.form("EndDate")
if EndDate <> "" then
UpdateSet("EndDate") = EndDate
end if
datecompleted = request.form("datecompleted")
if datecompleted <> "" then
UpdateSet("datecompleted") = datecompleted
end if
taskstatus = request.form("taskstatus")
if taskstatus <> "" then
UpdateSet("status") = taskstatus
end if
taskissues = request.form("taskissues")
if taskissues <> "" then
UpdateSet("issues") = taskissues
end if
UpdateSet.Update
UpdateSet.close
set UpdateSet = nothing
taskmessage = "updated the taskID "&updateitem&""
end if
end if
end if
next
end if
ProjectId = request.Form("sltProjects")
thissql = "SELECT tblProjects.Project_ID, tblProjects.ProjName, tblProjects.Status, tblProjects.StartDate, tblProjects.requestedCompletionDate, "
thissql = thissql & "tblProjects.estimatedCompletionDate FROM tblProjects WHERE tblProjects.Project_ID = "&ProjectID&""
set RS = DBConn.Execute(thissql)
Hi Ribeyed, thank you soooo much for taking your precious time to try and help me out on this.
There is one problem. The task requirement is so weird that no one wants to even approach.
You are the first to give it a go.
What my boss promised a client is that the tasks associated with a particular project will be listed within the edit screen of a project.
Please see if you can run the following code to get a clear idea of what the layout should be like.
Maybe you can help me integrate a portion of your code with this.
The reason the I don't have this working is because I can't figure our how to insert the tasknames into the task table while in the edit screen like this:
<html>
<head>
<script language="javascript">
<!--
var numOfBoxes = 8;
function enable(inBox) {
if (!document.forms['myForm']['cb' + inBox].checked) {
// If the user unchecked a box, then disable all after it
for (var i = inBox + 1; i <= numOfBoxes; i ++) {
document.forms['myForm']['cb' + i].checked = false;
document.forms['myForm']['cb' + i].disabled = true;
}
}
else {
// User checked a box, so enable the next
document.forms['myForm']['cb' + (inBox+1)].disabled = false;
}
}
//-->
</script>
</td></tr></table>
</form></center>
<%
RecordSet.Close
Set RecordSet = Nothing
Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing
%>
</body>
</html>
Ribeyed
03-10-2003, 07:28 AM
hi,
no problem i will have a look at it for you, do you mind if i change so of your code?
I would also like to point out that you can use an external inc file for holding your database cusor values, its called an adovbs.inc . Save this in your root.
This is then addded to all your pages that perform database tasks.
<!--#include virtual="/adovbs.inc"-->
Then the inc file will contain this example from my own:
<%
'--------------------------------------------------------------------
' Microsoft ADO
'
' Copyright (c) 1996-1998 Microsoft Corporation.
'
'
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------
hi Janice,
i can't get your code to run so i started to make modifications so it would run even once.
I see in your code that you only create 1 recordset and populate it with records from the project table only.
SQL = "SELECT tblProjects.Project_ID, tblProjects.* FROM tblProjects WHERE (Project_ID =" & Request.QueryString("Project_ID") & ");"
Further down your code when you are retrieving your records you reference fields in the tasks table:
hi Janice,
I'll was just thinking i will just go ahead and change your code. If you have any problems with me changing any of your code just let me know. I'm not sure of the page before where the project is selected for editing, i see your requesting it from a querystring so you maybe have a list of projects on the previous page with the projectID as a querystring.
janice
03-10-2003, 08:30 AM
Ribeyed, I can't thankyou enough. Please feel free to make any changes you wish to make.
Yes, I was referencing only project table because I was having trouble referencing both project and taskrequest tables.
Secondly, I was retrieving taskRequest records because I added them to the project table which I know is the incorrect way of doing it.
I was just having all kinds of problems with it.
Please modify as yo please.
Thanks again!
janice
03-10-2003, 08:34 AM
hi Ribeyed!
I'll was just thinking i will just go ahead and change your code. If you have any problems with me changing any of your code just let me know. I'm not sure of the page before where the project is selected for editing, i see your requesting it from a querystring so you maybe have a list of projects on the previous page with the projectID as a querystring.
Per above comments from you, I have a page called viewAllProjects.
That's the page that passes the project_id to the edit.asp that you are working.
That part needs to stay as is if it is ok with you.
Ribeyed
03-10-2003, 08:48 AM
hi Janice,
i am really sorry but the more i work with your code the more confused i become on what the page is actually ment to be doing.
There are a few things that are confusing, it is not clear if the page is ment to be editing the Project itsself or just the task for the Project or both.
I see that you are called a field from your database called Description. In the post you supplied tables and fields i can see a field called Description so which tables is this from, the Projects or the Tasks?
The part where you have Tasks, then you have a task name and a checkbox, are they ment to be pulled from the database then displayed?
Does the check box value get stored in the database?
When you click on a check box does this mean you can then edit the task details in the form bellow?
On the form you have a box for editing the project and the projectID. If projectID is your primary key for your table then you would not want the user to be able to change this value. If this is changed then you may end up with data intergaty problems. Never let a user manually edit your primary key.
Now on the form you have a text area for Daily notes. I don't see this field in your database structure and again im not sure which table this is from.
I can see the top 2 text boxes are for editing the project details in the project table but the daily notes i would think must be related to the tasks, am i correct?
If so i would then assume that you are going to be having more then 1 Daily note for each task. This is a 1 to many situation and therefore i am thinking that Daily notes would be in a seperate table from the tasks but linked through a foreign key (TaskID). If this is the case where is the table structure for the tblNotes?
Now i am looking at the issues text box, i see that this field is in the Tasks table. You only have 1 of these so i would persume than if i edit this i would be editing the first task for the current project? How do i edit the second or third task?
Next you have the end date text box. Is this the task enddate or the project end date? If its the task enddate is it for the first task or what?
Lastly you have % completed, can't see where this is stored either. Is it for the current task or for the whole project?
well thats it just now, get back to me on these queries.
janice
03-10-2003, 09:14 AM
hi Ribeyed, thanks for your patience!!
for this:
I see that you are called a field from your database called Description. In the post you supplied tables and fields i can see a field called Description so which tables is this from, the Projects or the Tasks?
The fieldname in the project table is called Description. On the form I called it Daily Notes.
The part where you have Tasks, then you have a task name and a checkbox, are they ment to be pulled from the database then displayed?
This is meant to be stored in the taskRequest table.
The idea is when a task is completed, you put a checkbox on it and update the issues/daily notes records and click the submit button.
The taskname is stored to the taskRequest table and the issues/daily notes are updated in the project table.
On the form you have a box for editing the project and the projectID. If projectID is your primary key for your table then you would not want the user to be able to change this value. If this is changed then you may end up with data intergaty problems. Never let a user manually edit your primary key.
This is a good point. I have fixed that by removing the input tag.
Now on the form you have a text area for Daily notes. I don't see this field in your database structure and again im not sure which table this is from.
All of these belong to the project table. They will also be in the task table but for now, my biggest headache is how to store those tasknames in the taskrequest table from that edit screen.
Next you have the end date text box. Is this the task enddate or the project end date? If its the task enddate is it for the first task or what?
This is called estcompDate in the project table.
Again, every field on the form belong to the project table except the tasknames. Those we want to go to the taskRequest table.
Lastly you have % completed, can't see where this is stored either. Is it for the current task or for the whole project?
This is called PR_Percent in the project table.
All the other fields you see in the taskRequest table I can update in a seperate update statement but for now, according to the requirement, all the taskRequest info that needs to be in the edit.asp screen is the tasknames.
Again, I can't thank you enough. Please let me know if you need more clarifications.
Ribeyed
03-10-2003, 10:38 AM
hi janice,
The fieldname in the project table is called Description. On the form I called it Daily Notes.
Ok got that.
This is meant to be stored in the taskRequest table.
The idea is when a task is completed, you put a checkbox on it and update the issues/daily notes records and click the submit button.
The taskname is stored to the taskRequest table and the issues/daily notes are updated in the project table.
taskRequest table is this the task table or different?
When the page first loads where are the tasks with texboxes comming from. I mean where are the first inputed? I know you are wanting to add these to the database but where is the tasks inputed?
This is called PR_Percent in the project table.
How is this calculated?
Ribeyed
03-10-2003, 10:53 AM
hi JAnice
cont. from last post....
All of these belong to the project table. They will also be in the task table but for now, my biggest headache is how to store those tasknames in the taskrequest table from that edit screen.
Surely your not planing on storing the same data twice in 2 different tables?
The storing of task names is not the hard to do, but again where are the values for the tasks comming from, i only see checkboxes with labels. I know your labels are going to be the tasks but if there is no place to input new tasks how can you do anything with the tasks. I just can't get what your trying to do here.
Are the tasks going to be the same regardless which project they belong to, for example:
Evaluate Requirements task would appear in project 1 and project 2.
Design task would appear in project 2 and project 3.
or maybe its every project has all the same tasks.
so project 1 would show all tasks, project 2 all taks etc.?
if this is the case then the relationship is a many-to-many with the Projects and Tasks.
Each project has many tasks, but each task can also belong to many projects.
If this is the case then you need a relationship table.
The Description or Daily notes, you mention that they are updated. Under your present structure any previous entery would be lost is this what you want?
I would persume not. You obviously want to store and previous daily notes concerning the project. I would recomend that you have a sperate table for Daily notes and link through Project_Id again. This way you can store all the Daily notes for each project instead of updating and lossing old ones.
janice
03-10-2003, 11:15 AM
Hi Ribeyed!
_______________________________________________________
taskRequest table is this the task table or different?
When the page first loads where are the tasks with texboxes comming from. I mean where are the first inputed? I know you are wanting to add these to the database but where is the tasks inputed?
______________________________________________________
Sorry, it is the task table. I have it here as taskRequest but it is task. Please continue to use any naming conventions you wish to. I can adjust those here.
The way it is intended to work, is to use either the addNew method or straight insert statement and then add each to the database by checking each checkbox as the task is completed.
That is part of the difficulty I am having getting my logic together.
Sorry to say this but pretend that you want to implement this logic.
Whatever logic you come up with definitely beats mine because I am stumped on how to approach it.
My boss is on vacation and will be back on Wednesday. I was just going to tell him I couldn't do it the way he wanted it, and then I see what happens.
________________________________
How is PR_Percent calculated?
__________________
I used a vb function to code this functionality.
The way it works is that PR_Percent by default is 0.
In other words, as soon as you create a new project, %completed is 0.
As you make progress with the project, you continue to update PR_Percent with increment of 5.
Everything is rounded out to the nearest whole number.
I am using gif images to represent the percentages.
This functionality works fine.
Again, may a huge reward for your help and kindness come upon you.
janice
janice
03-10-2003, 11:36 AM
let me answer this in reverse.
______________________________________________
The Description or Daily notes, you mention that they are updated. Under your present structure any previous entery would be lost is this what you want?
I would persume not. You obviously want to store and previous daily notes concerning the project. I would recomend that you have a sperate table for Daily notes and link through Project_Id again. This way you can store all the Daily notes for each project instead of updating and lossing old ones.
________________________________________________________
The Description or Daily Notes are updated daily whether a task is completed or not.
So for instance, You go to edit screen and update the Description record based on dates:
03/09/2003: This is what is going on today
03/10/2003: This is what is going on today
Same with issues
03/09/2003: No issues today
03/10/2003: This bloody task is driving me insane.
or maybe its every project has all the same tasks.
so project 1 would show all tasks, project 2 all taks etc.?
if this is the case then the relationship is a many-to-many with the Projects and Tasks.
Each project has many tasks, but each task can also belong to many projects.
If this is the case then you need a relationship table.
_______________________________________________________
We used the project lifecycle approach.
The project lifecycle as we deisigned it are:
1, problem definition
2, feasibility study
3, analysis
4, system/database design
5, acquisition and programming
6, implementation
7, maintenance
8, other
Using this convention, we deducted that each of these steps are considered as tasks and that since each project you develop is going to need these steps, we decided these are our tasks.
We did not want to break them out and say: Ribeyed, you design and develop the web, janice you code the stored procedures. We just went with that standard; hence it is 1:m besides, it is easier to code(we thought).
______________________________________
Surely your not planing on storing the same data twice in 2 different tables?
The storing of task names is not the hard to do, but again where are the values for the tasks comming from, i only see checkboxes with labels. I know your labels are going to be the tasks but if there is no place to input new tasks how can you do anything with the tasks. I just can't get what your trying to do here.
No, it was a misstep on my part.
What I meant was that all fields on the task table you are looking at will eventually be in the task table and I will have a separate update code to update them but for now, all we would like to do is have the taskNames appear in the edit screen of the project record just the way they are laid out.But the project records will not be duplicated in the task table.
Again, normally when you want to store a record in the database, you either have the addNew method or the insert statement method. You would then use either checkbox to store each record or some other form method.
That is what we are trying to accomplish here.
The big problem is we are doing it within the project edit screen rather than a separate task screen.
I sincerely apologize for all the inconvenience.
I know you are trying to help and it is incumbent upon me to provide you with all the information you need to help.
It is just hard the way we have it laid out.
Hopefully, you will continue to be patient with me until I satisy all your queries
Ribeyed
03-10-2003, 01:20 PM
hi Janice,
ok good things are coming clearer now.
I hear what you are saying about the tasks and even although the are the same tasks for all projects it is still many-to-many and not 1 to many. You will find this a hell of a lot easier and not hard if you implemented correct normalization to your database tables.
Good i thought that we were working with the tasks that are based on the project life cycle. In this case the tasks are static and do not change. The tasks are going to be the same for every project.
I feel that we can't implement the functions you want on the present database structure. So, what I want to do is slightly change the database structure to work with normalized data tables I hope you can implement these changes.
You will need to make changes to the add project page to implement the new table structure. Don't worry i can give you code to handle the relationship tables for that page.
When you enter the edit page all the tasks will be listed (same as you already have), slight difference will be the current task or the first task will be checked and the rest disabled. I will add a drop down box at bottom of form which will have different types of status listed. The user will select a status for the task. One when this status is set to complete will the next checkbox become available.
Give me a few hours to work on this and I will post you code and an image of the new table structure, again I am sorry but I wouldn’t be able to get this to work correctly for you unless I can work with the new normalized database tables.
janice
03-10-2003, 01:56 PM
Great!
You are an angel sent my way by God.
Like I said, please make whatever changes you wish to make.
I wouldn't know what to do show my appreciation.
Ribeyed
03-10-2003, 07:14 PM
hi Janice,
Right i have finished writing you some code. Please find attached a zip file which contains the edit.asp page, another .asp page that i used just to generate the links from the project to the edit page, an access database so you can see the tables, relationships and data in the tables. I have also included a screen shot of the relationships in case you don't have access, sorry this is not in SQL, i don't have it installed at the moment.
Basically what i have done is created a table for to hold the static tasks. Every time you create a new project you need to automatically populate the tblProjectsTasks table with the primary key of all the tasks and set the StatusID to 1. Each task in the project has a current status this is taken from the status table. The status table holds static status names and the primary key number is pulled into the tblprojecttasks table.
When you enter the edit page for the first time only the first task will be active. The status of this task, task 1 is set to start. The user can then enter notes and issues for this current task. If the status of the task is being changed then the user can select the status from the list box at the bottom of the form.
Now a few things can happen here when the update button is pressed. If the current task's status is the same and nothing is entered then no database working will be done. If the current task's status is change and again no notes or issues are add then the current task is updated to the selected status. If there is any issues or notes entered into the form then these are entered into there separate tables to reflect this. If the current task's status is set to complete then the status for this task is set to complete in the database and the next task is then set to start. On return to the page in this case you will now see a check for the first task and a check for the second task as this is now the current task. This continues until the last task is set to complete in this case the last note and issues is entered into the database and the current date/time is entered into the projects table. The user is presented with a message and not a form.
Well I hope this is what you need because that took ages to do, lol. If its not don’t worry it shouldn’t be far off what you need.
janice
03-10-2003, 09:29 PM
Thank you sooo very much Ribeyed!
You are one unique individual.
I know this is one of the probably the most helpful site I have ever visited but it is one thing to know your "stuff".
It is quite another to share it.
I really, really appreciate your help, efforts and all the time you put into this since the first time I threaded this question.
I am currently working converting them into sql.
I will get back to you.
janice
03-11-2003, 08:37 AM
hi Ribeyed!
Let me again thank you very much for all the time and efforts, and most of the patience to have done what you did for me.
I have a few follow-up questions, if I could.
Now you said to automatically set the statusID to 1 in the tblprojecttask table whenever a new project is created.
Do you manually go in there and set the statusID to 1 or is the code doing this somewhere?
2, I see in the tblprojecttask table statusID is set to 5.
What does it mean when it is set to 5?
3, I ran the projecttask.asp page and this part:
The Projects Tasks
Task Name StartDate End Date Date Completed Status Issues
are all blank. Is something supposed to be in them?
Finally, How is projecttask.asp page passing project_id and statusID to the edit.asp page?
Because when I ran the edit.asp page, I get this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
which means no project_id is being passed to the edit.asp page.
When I used my other page called viewN.asp, a project_id gets passed to the querystring but then another error comes up:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
line 240 which is this line:
sql = "SELECT * FROM tblStatus WHERE tblStatus.StatusID >= "¤ttaskstatusID&""
set statusset = DataConnection.execute(sql)
Ribeyed
03-11-2003, 09:15 AM
Hi Janice,
It’s no problem helping you. On something like this once you start helping you have to help until it’s finished.
When a user creates an new project all you want them to do is to insert the project in formation and that’s it nothing else, but behind the scenes as it were you need to do the extra work with populating the tasks to the project. So you would need something like this:
<%
sql = “Insert your project details into tblProjects”
‘now do a select on the task table to get all the task id’s
thissql = “select tblTasks.TaskID FROM tblTasks ”
set RS = dataconnection.execute(thissql)
‘now do a while loop to retrieve the tasked
while not RS.EOF
‘now for every record in this record set you want to insert a record in the
‘tblProjectTasks along with the projected and also insert the first statusID from
‘the status table which should be 1
moresql = “insert into tblProjecttasks the TaskID and the projectID”
RS.MoveNext
wend
%>
The Status table with the status ID is to keep track of which stage the task is at. The status table can have 10 stages if you want or 20 etc. I have just put 5. The idea is when the project is first created all the tasks are set to 1 (not started). As the users work through the tasks they can come back to this page and update the status of the task. When the task is the current task then it is presumed to be started and therefore set to 2 (started). Again you can have as many stages as you want between status started and your final status (complete). Currently the complete status is 5 because that’s; a) It’s the last record in the tblStatus and b) What I set to be the completed stage.
So walking along the scenario, the user creates a projected and you automatically populate the data into the tblProject and the tblProjectTasks tables to start things off. This should enter 1 record in the tblProject and 8 records in the tblProjectTask table which relate to the tasks in the tbltasks table. All the tblProjectTasks statusID are set to 1. When the user is updating the change the status of the task moving it up in status. This is reflected in the tblProjectTasks table by editing the statusID. Once the status is equal to your completed status you know that this task is now finished so you want to automatically set the next task to start. Again this continues until the task reaches the last stage the completed stage.
If you need help to code the other pages just let me know.
Ribeyed
03-11-2003, 09:18 AM
hi,
forgot to attach the file:D
janice
03-11-2003, 10:11 AM
right now, things are beginning to look good. It finally looks like I will be here after all when my boss comes back from vacation. For a moment, I thought I was done because this has dragged for awhile. All the thanks go to you.
A few problems/questions.
When I run the projectList page and select a project to edit, I quickly notice 2 things.
1, all the checkboxes are automatically checked. Is it supposed to work that way.
I thought all will be disabled indicating "not started" and once you select "started", the first is enabled.
Sorry for these dumb questions.
Second, when I click on a checkbox to check or uncheck it, I get an error. I use textpad so it is hard to figure out javascript errors.
So should I ignore projecttask.asp page then?
Or is of some use?
In addition to my add.asp page , the only pages I have now are the projectlist.asp page, and the edit.asp page
The other question is that I am using stored procedure and then using two asp pages to call the stored procedure. That is how I have been creating records and this works fine but given the enormity of the changes you have made, I am going to need change that.
What are the tables that need the insert statements, just tblprojects and tblProjecttasks only.
As far as tblProjecttasks is concerned, I am manually entering the taskid and projectId, right?
Ribeyed
03-11-2003, 10:35 AM
Good, I’m glad you’re going to be keeping your job; I too am in that situation all day every day. You just keep sending posts until you’re happy with what you have.
You are asking about the checkboxes, well yes I am not that happy with that as well but more from a usability angle. I would have though of a whole different approach to this page but that’s the way your client wanted it, right?
To answer your question if there was no checked boxes checked to start with then you would be forcing your user to check 1 or more every time they use the page, where as the way I have got it, the current task and all completed tasks are automatically check so the user doesn’t have to do anything with the checkboxes, which leads me to point out that the checkboxes don’t really do anything. If you decide to keep them you many need to add some error trapping code to make sure that they don’t leave one unchecked this would cause problems.
Again if you decide to keep the checkboxes then I would have a look at your Javascript code for you, see I don’t bother making sure that your javascript worked because I sort of made it not depend on the enabling and disabling of the checkboxes.
You should ignore the projecttask.asp file this has no baring on your other pages. You may however want to keep it as it has some very useful techniques that you maybe able to use in any future developments that you do.
Well looks like you have your work cut out converting all the sql’s into procedures, good luck on this, lol :D
Do you want me to write the “add new project page” as well? If so send me your current page as a template and I will make the changes for you. As far as tblProjecttasks is concerned no you don’t do any manual entering direct into the database you write the code in the “add new project” page to do this for you.
janice
03-11-2003, 10:45 AM
hi Ribeyed!
This is my current add page.
This will then call the insert statement page.
Is this the page you want to see?
<%
Response.Buffer = True
If Session.Contents("Access_Level") <> "ISAdmin" AND _
Session.Contents("Access_Level") <> "GISAdmin" Then
Response.Redirect "index.asp"
End If
%>
<html>
<head></head>
<body>
<center>
<table border="1" cellpadding="8" cellspacing="0" bgcolor="#CECFCE" width="90%">
<tr>
<td align="right" colspan="2" bgcolor="#181839">
<TR>
<TD><FONT CLASS='Arial10' ><B>
If needed in Less than 24 hrs, Justify
</TD>
<TD>
<TEXTAREA VALUE="" NAME="justification" rows=5 cols=25></TextArea>
</TR>
ok where is the page with the working code submitProjectRec.asp?
janice
03-11-2003, 11:09 AM
hi Ribeyed!
I still have a qustion about the checkboxes. Per this:
where as the way I have got it, the current task and all completed tasks are automatically check so the user doesn’t have to do anything with the checkboxes
If I am not mistaken, the checkboxes get checked as you increase the %completed, right?
Right now, as soon as you open the edit screen, all checkboxes are automatically checked.
How do I make do exactly what you said above so that only the current or ones detemined as completed get checked?
I don't want to make it work any harder than you have already.
janice
03-11-2003, 11:13 AM
sorry I hadn't seen your response before I posted the last message.
Here is submitprojectRec.asp. This page calls the stored procedure. I am not sure you want to see the sp.
If you do, please advise.
Dim objConn,objCmd
Set objConn = server.CreateObject("ADODB.Connection")
Set objCmd= server.CreateObject("ADODB.Command")
objConn.CursorLocation = 3
objConn.Open "DSN=wetts"
With objCmd
.CommandType = adCmdStoredProc
Set .ActiveConnection = objConn
.CommandText = "spInsertGISProject"
<TR><TD align="middle" >
<b> <font color="green" style="arial"> Thank you for Submitting to our database!<br>
Your Information has been stored in our database </font></b>
</TD>
</TR>
</TABLE>
</body>
</html>
<html>
<head>
<title>Back To Project Listings</title>
<META HTTP-EQUIV="refresh" content="2;url=viewAllG.asp">
</head>
<body>
</body>
</html>
Ribeyed
03-11-2003, 11:30 AM
janice go into the database and set all the Tasks in the tblProjectTasks to have a Status ID of 1. This will be reset as if the project has just been created.
janice
03-11-2003, 03:56 PM
oh my gush!
I forgot to ask you to please ignore the following codes at the top of each page:
<%
Response.Buffer = True
If Session.Contents("Access_Level") <> "ISAdmin" AND _
Session.Contents("Access_Level") <> "NetWorkAdmin" Then
Response.Redirect "login.asp"
End If
%>
I have them here and they work fine.
Ribeyed
03-12-2003, 06:36 AM
hi Janice,
ok i think your only going to need slight changes to the submitprojectRec.asp to work in your new tables etc.
After your last store procedure you need to do a select statement to find out the new ProjectID for the project your created.
You know the last record to ne instered is the new project so do a select statement and then order by decending.
sql = "SELECT tblProjects.Project_id FROM tblProjects ORDER BY tblProjects.Project_id DESC"
set bla, bla, bla............
currentProjectID = RS("Project_id")
Now is just the case of creating a record in tblProjectTasks for each task. So start by doing a select on the tbltasks table.
sql = "SELECT tblTasks.TaskID FROM tblTasks "
set RS = DBConn.Execute(sql)
Now for every record on the RS recordset you want to create an ew enter in the tblProjectTasks, so do a while loop. All the new tasks have to be set to the starting statusID
While not RS.EOF
TaskID = RS("TaskID")
StatusID = 1
sql = " INSERT INTO tblProjectTasks (Project_id, TaskID, StatusID) VALUES (currentProjectID, TaskID, StatusID) "
DBConn.Execute sql
RS.MoveNext
wend
Thats it your tblProjectTasks should now have all the tasks assigned to the new project.
janice
03-12-2003, 07:54 AM
hi Ribeyed!
I know you have invested an enormous amount of your time on this. Please stay with me a little longer.
what page should your codes be placed in?
Remember that submitprojectRec.asp is an asp page.
The stored procedure was written in pure sql and is called spInsertGISProject.
I don't mind continuing with the stored proc. I certainly don't mind using a straight insert statement, something similar to what you started.
They just can't be mixed up.
Here is what I have, just to give you a sense of what I am talking about.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertGISProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertGISProject]
GO
/* Move the identity value of the new record into a variable */
SET @TaskID = @@IDENTITY
/* Create and populate new TaskDetails record */
Insert INTO TaskDetails(
Project_ID
)
VALUES(
@ProjectID
)
/* Move the identity value of the new record into a variable */
SET @ProjectID = @@IDENTITY
SET @TaskDetailsID = @@IDENTITY
/* Create and populate new Task record */
Insert INTO TaskRequest(
Project_ID,
TaskDetailsID,
TaskCode,
StartDate,
EndDate,
DailyNotes,
TaskName,
Issues,
Service_Type
)
SELECT
@ProjectID,
@TaskDetailsID,
StatusCode,
StartDate,
EndDate,
Comments,
Null,
Null,
Null
From TaskDetails
WHERE project_id = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
thanks a lot for everything
Ribeyed
03-12-2003, 09:14 AM
Hi Janice,
Ok lets go back to your scenario, you¡¦re user needs a page to be able to create a new project. Once the new project is created the user goes into the Project list page and locates the new project. A project consists of a set number of tasks 8 in this case, the project life cycle stages. Each project that is created is treated as a new project, but every project would consist of all the same tasks. To start the project the user/project creator needs to click on the project listed on the project list page to take them to the edit page. Here the user can do 2 things; 1) Enter any opening issues and reports. 2) Set the status to start Task therefore initializing the project. Every day the user will come to the edit page and enter daily reports and issues. If the task has not changed status then the user doesn¡¦t select any status and leaves it to the default. If the task is moving up in status then the user changes the status to suit. This would continue until the current task is complete and again continues until all tasks for the project are complete.
So first you create an add project page. You provide a form for the user to complete. When this is submitted you need to first store the project details. Then you have to set the tasks to the project in the tblProejctTasks table. The page you are submitting the project details is the submitprojectRec.asp page, so this is the page that you also set the tasks. The page that you pasted with the sql parameters for your store procedures that you use to insert the project details need to be updated with code similar to what I posted.
I understand that you are using SQL Server and I am giving you answers in Access, this is causing you slight problems, but I don¡¦t have SQL Server installed in my PC and if I did it would take me a good few days work to get it installed, database constructed and sql store procedures create then I would have to convert all the access to sql. Unfortunately I don¡¦t have the time to do all this but if I did I would, you¡¦ll understand. So you will just have to put in those extra hours at the office to get this done ƒº
I don't mind continuing with the stored proc. I certainly don't mind using a straight insert statement, something similar to what you started.
If would benefit you more to continue using SQL Store procedures; a) You will learn how to use both methods as you will have to convert Access sql to SQL store procedures; b) SQL store procedures are much more efficient.
janice
03-12-2003, 11:36 AM
hi Ribeyed!
Sorry I went out to the field and just got back.
ok, I will try and figure this last part out.
I thank you for everythin from the bottom of my heart.
You have been wonderful!!!
Ribeyed
03-12-2003, 11:46 AM
hi,
its no problem i will still help you until you get it working, but if you try write some code the same as i posted then i can help you along with it.
janice
03-12-2003, 11:51 AM
I will do the best I can to get it to work.
The only reason it appears to be a daunting task for me is because I did not use the insert statement .
If I did, I think it would have been a lot easier to follow.
I did it in straight sql and your example, though has an embedded insert statement but has asp flavor.
So a bit harder to break out and integrate into sql stored proc but I will see what I can with it.
Again, you are an angel and I thank you.
janice
03-13-2003, 12:11 PM
hi Ribeyed!
Ok, almost everything is working fine.
I have got the stored procedure to do exactly what we intend it to do and your direction helped me get it done.
But I have got just one tiny problem and I am confident everything will be ready to go once this problem is taken care of.
The PR_Percent is not working anymore.
PR_Percent is in the edit.asp page.
After a task is completed, PR_Percent is to be incremented and the increment is 5. The default value as soon as a project is created is 0. It doesn't matter what you do, it stays as 0.
Do you happen to know why?
Ribeyed
03-13-2003, 06:21 PM
hi Janice,
i will have a look at it for you. I need to just clear up a few things first. You said that it is increment by 5 every time a task is completed. If there are 8 tasks to every project the the increment is 12.5. If its 12.5 then that should be no problem dynamically calculating that, you can even go fancy and create a bar chart out of a image to represent the percent completed.
Let me know what you want.
janice
03-13-2003, 07:16 PM
hi Ribeyed and nice to hear from you again.
Well, I have struggled mildly with this.
Going fancy sounds great but I can't do it for the short time that I have; unless of course you want to continue to amaze me.
I have asked questions a lot in this forum and have helped a lot in my oracle days but I don't think I have come across anyone as patient and helpful as you and I know that
someone by the name Dave Clark has been very helpful himself.
Ribeyed
03-14-2003, 11:55 AM
hi Janice,
here is the code you need for the edit.asp to calculate the percentage of the project completed. This is calcuated dynamically therefore you don't need to store the value in the database you can get rid of the field PR_Percent as it is not required. Anyway add this code in where the PR_Percent was being displayed.
<td bgcolor="silver">% Completed:</td>
<td>
<%
sql = "SELECT COUNT(tblTasks.TaskID) AS CountOfTasks FROM tblTasks "
set counttaskset = DataConnection.Execute(sql)
if not counttaskset.eof then
countoftasks = counttaskset("CountOfTasks")
end if
counttaskset.close
set counttaskset = nothing
sql = "SELECT COUNT(tblProjectTasks.ProjectTaskID) AS CountOfCompletedTasks FROM tblProjectTasks WHERE tblProjectTasks.StatusId = "&compstatusid&""
set countofcompleteset = DataConnection.execute(sql)
if not countofcompleteset.EOF then
CountOfCompletedTasks = countofcompleteset("CountOfCompletedTasks")
end if
if countoftasks <> "" then
if CountOfCompletedTasks <> "" then
pecentpertask = 100 / countoftasks
PR_Percent = pecentpertask * CountOfCompletedTasks
else
PR_Percent = 0
end if
else
PR_Percent = 0
end if
countofcompleteset.close
set countofcompleteset = nothing
%><%=PR_Percent%>%</td>
</tr>
janice
03-14-2003, 01:45 PM
hi Ribeyed!
Yes! Yes! Yes!
Everything seems to be working GREAT right now.
I create a new project and after submitting, I am redirected to the viewproject page ( Ichanged it because the client wants to
see fields displayed in a report form like:
project Id Project Name start Date End Date Action (edit is the action). It takes me to this screen. I click "edit" and it takes me to edit.asp page.
I edit the records and after iterating through the statusIDs, completing one task, it automatically moves me to the next task.
The only two issues that I have is that issuesNote and DailyReports get overwritten after an update.
It would have been nice to have retain each issueNote/DailyReport for their management perusal like
03/13/2003: Did this today then when you come back for another update, you see this:
03/13/2003: Did this today then you add another note:
03/14/2003: Did that today but other than those,
everything works great
I cannot possibly, possibly thank you enough.
You are my angel.
In other forums I have been to, like DBASupport.com forums, a geniuse like you, who has demonstrated not only their stuff, but the ability to help struggling ones, like me, are usually givens starts.
I wish I could give you something for really saving me.
Thank you sooo much!
Ribeyed
03-14-2003, 02:11 PM
Hi Janice,
Good I’m glad everything is coming together for you and thank you for you’re very kind comments.
The code I have written for you allows for a new entry for every new “issues” and “daily report”. The only reason I can see that it would write over the previous one if your primary key for both tables is not set to automatic number. Is it is not then yes every new one will be written over the old one.
Hopeful thats it fixed and you have it working:D
janice
03-14-2003, 03:03 PM
Oh, issues/notes don't get overwritten.
I thought you could see them once you click the edit button but that is not a big deal.
I can create an interface for just view issues.
Again, thank you and may more knowledge come your way.
And I apologize for consuming too much of your time. Please don't let it discourage from helping other folks who might have similar problems as I have had with this project.
Ribeyed
03-14-2003, 05:53 PM
Hi Janice,
Yes I agree a separate page for displaying issues and notes is the best way to go. You can provide this function within one asp page, which I am willing to help you create. You would require the page to start with code the same as the displayprojects.asp page and create links to each projects notes/issues. The you would send to the same page passing of a value to move your code on to the next part which will display all the tasks, daily notes and issues grouped by each task and ordered newest first. So on your page you would have something like this:
Project Name : Project 1
Current Project Status: 25% complete
This task took (say) 6 days to complete
----------------------------------------------------------------------
This is all achievable with the information you have in the database.
janice
03-14-2003, 07:41 PM
hi Ribeyed!
Ok, where do I start with that because it sure looks very attractive.
I am just hoping I don't run out of your help and kind-naturedness. You have continued to amze me.
Is it something that will take more than a day because we are tentatively scheduling a demo for the client on Tuesday.
This way, I can walk this piece into that demo in my planning.
Ribeyed
03-15-2003, 05:44 AM
Hi Janice,
I just realised I forgot to put code in the edit page that would add in start dates for each task as it moves on to the next task, if you need help on this just ask.
Here is an example of a page that I discussed above. Again it is short of the code for counting the days between the start of the task and the completed date. Apart from that everything else works great. I will post back with code for this page that will workout the length of time the task took to complete. Hope everything goes well on Tuesday for you.
<%
if viewdetails = "" then
SQL = "SELECT * FROM tblProjects"
set RS = DataConnection.Execute(sql)
while not RS.EOF
%>
<table width="100%" border="0">
<tr>
<td><a href="viewprojectissues.asp?Project_ID=<%=RS("Project_id")%>&Select=yes"><%=RS("ProjName")%> Issues & Notes</a></td>
</tr>
</table>
<%
RS.MoveNext
wend
else
ProjectID = request.querystring("Project_ID")
sql = "SELECT tblProjects.ProjName FROM tblProjects WHERE tblProjects.Project_id = "&ProjectID&""
set projnameset = dataconnection.execute(sql)
if not projnameset.eof then
projname = projnameset("projname")
end if
projnameset.close
set projnameset = nothing
%>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td><%=projname%></td>
</tr>
</table>
<%
startstatus = 1
competestatus = "Completed"
sql = "SELECT * FROM tblStatus WHERE tblStatus.StatusName = '"&competestatus&"'"
set statusset = DataConnection.execute(sql)
if not statusset.eof then
compstatusid = statusset("StatusID")
end if
statusset.close
set statusset = nothing
sql = "SELECT * FROM tblProjectTasks WHERE tblProjectTasks.Project_id = "&projectID&""
sql = sql & "AND tblProjectTasks.StatusID > "&startstatus&" ORDER BY tblProjectTasks.TaskID DESC"
set projtaskset = dataconnection.execute(sql)
if not projtaskset.eof then
while not projtaskset.EOF
TaskID = projtaskset("TaskID")
StatusID = projtaskset("StatusID")
ProjectTaskID = projtaskset("ProjectTaskID")
startdate = projtaskset("startdate")
datecompleted = projtaskset("datecompleted")
thissql = "SELECT * FROM tblTasks WHERE tblTasks.TaskID = "&TaskID&""
set tasknameset = dataconnection.execute(thissql)
if not tasknameset.eof then
taskName = tasknameset("TaskName")
end if
sql = "SELECT tblStatus.StatusID, tblStatus.StatusName FROM tblStatus WHERE tblStatus.statusID = "&StatusID&""
set statusset = dataconnection.execute(sql)
statusname = statusset("StatusName")
statusset.close
set statusset = nothing
%>
<HR><table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="11%">Task Name:</td>
<td width="89%"><%=taskName%></td>
</tr>
</table>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="7%"> </td>
<td width="14%">Current Status:</td>
<td width="79%">
<%=statusname%>
<%
if StatusID = compstatusid then
response.write " This task was completed on " & DateCompleted
end if
%>
</td>
</tr>
</table>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="7%"> </td>
<td width="14%">Start Date:</td>
<td width="79%"><%=startdate%></td>
</tr>
</table>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="7%"> </td>
<td width="14%">End Date:</td>
<td width="79%"><%=projtaskset("EndDate")%></td>
</tr>
</table>
<BR>
<%
sql = "SELECT * FROM tblIssues WHERE tblIssues.ProjectTaskID = "&ProjectTaskID&" ORDER BY tblIssues.IssuesDate DESC"
set issuesset = dataconnection.execute(sql)
if not issuesset.eof then %>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="9%">Issue Date</td>
<td width="91%">Issues</td>
</tr>
</table>
<%
While not issuesset.eof
IssuesNote = issuesset("IssuesNote")
IssueDate = issuesset("IssuesDate")
%>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="9%"><%=IssueDate%></td>
<td width="91%"><%=IssuesNote%></td>
</tr>
</table>
<%
issuesset.movenext
wend
else
response.write "Sorry no issues for this task <BR>"
end if
issuesset.close
set issuesset = nothing
%>
<%
sql = "SELECT * FROM tblNotes WHERE tblNotes.ProjectTaskID = "&ProjectTaskID&" ORDER BY tblNotes.reportDate DESC "
set noteset = dataconnection.execute(sql)
if not noteset.eof then
%>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="9%">Note Date</td>
<td width="91%">Note</td>
</tr>
</table>
<%
while not noteset.eof
DailyReport = noteset("DailyReport")
reportDate = noteset("reportDate")
%>
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="9%"><%=reportDate%></td>
<td width="91%"><%=DailyReport%></td>
</tr>
</table>
<%
noteset.movenext
wend
else
response.write "Sorry no notes for this task<BR>"
end if
noteset.close
set noteset = nothing
%>
<%
if startdate = "" or datecompleted = "" then
else
response.write(datediff("d", startdate, datecompleted ))
end if
tasknameset.close
set tasknameset = nothing
projtaskset.MoveNext
Wend
else
response.write "this project has not started yet."
end if
projtaskset.close
set projtaskset = nothing
%>
<%
end if
DataConnection.Close
set DataConnection = nothing
%>
</body>
</html>
janice
03-15-2003, 08:29 AM
oh, wow Ribeyed!
You write code with relative ease!!
This is truly amazing.
For the startDate on the edit.asp page, I will look into that and try and take of that.
Thank you again for EVERYTHING.
Thank you!!!
This feels good even though it is all your work.
Ribeyed
03-15-2003, 01:19 PM
hi Janice,
personally I think that if you have a good normalized database and you utilize the power of databases through sql then your code will be much easier to write. I am not the neatest writer of code, maybe that’s because I had to learn ASP on my own. I am sure the more I write ASP the neater and more efficient my code will be, it will be the same for you. I can’t stress enough how important normalization is to databases and dynamic web sites. If you have not heard or don’t fully understand the rules of normalization I would be happy to give you links, help and advice until you are comfortable with it. Once you understand it you will say to yourself “ah that’s why it’s done like that” or something like that, lol.
Bellow is the code you need to insert the start date it is in the edit.asp page and I have marked the line for you.
Thank you very much again for you’re kind comments and also thank you to Dave Clark as well. Really it wasn’t that much trouble, I was glad of the chance to gain more experience. Anyway good lucky on Tuesday and again any more problems just post and I will take a look.
if int(txtstatus) = int(compstatusid) then
nextptaskID = currenttask + 1
set UpdateSet = Server.CreateObject("ADODB.RecordSet")
UpdateSet.Open "SELECT * FROM tblProjectTasks WHERE tblProjectTasks.Project_id = "&ProjectID&" AND tblProjectTasks.TaskID = "&nextptaskID&"", DataConnection, 2, 2, &H0001
if not UpdateSet.eof then
curentstatus = Updateset("StatusID")
nexttaskstatusID = curentstatus + 1
UpdateSet("StatusID") = nexttaskstatusID
'--------------------updated code---------------------------
'if the current task is completed and we set the datecompleted value to the current date/time.
'The project now moves on to the next task. To show this happening we should automatically updated
'the next tasks start date to the current/date time as well, here is the code.
Updateset("StartDate") = now()
'---------------------updatedcode ended-------------------------------------
UpdateSet.Update
UpdateSet.close
else
set UpdateSet = Server.CreateObject("ADODB.RecordSet")
UpdateSet.Open "SELECT * FROM tblProjects WHERE tblProjects.Project_id = "&ProjectID&"", DataConnection, 2, 2, &H0001
cont.....
janice
03-15-2003, 07:32 PM
hi Ribeyed!
Please trust me when I say that words are too cheap to truly express my sincere thanks for the enormity of time that you put into helping me to get the codes to come together and work as good as it is now.
What is most admirable, I believe, is your patience.
As good as I think I am as far as helping others, I don't thinkt that I can remotely come close to your patience and kindness.
You have just made me a better person with this experience as far as learning to share and continue to share until the beneficiary is satisfied.
Thank you (again).
As far as the links to database design is concerned, please forward them to me.
I am a firm believer of not passing up any opportunities to learn something new or add to what I know.
If those links include asp, or sql server, please pass them along too.
I am not that versed with asp so when I run into situations where I have to pass multiple autonumbers from different tables into one table, I usually have problem doing it even with stored procedures where I feel I am the strongest.
This problem leads me to sometimes denormalize my tables (as is the case here) thinking it would make life easier for me.
Again, thanks for all your help.
This forum has certainly lived up to the hype of not only boasting of having some of the best brains but also having the kindness of heart to share their knowledge with the needy like me.
Ribeyed
03-16-2003, 10:55 AM
hi Janice,
thank you again, I am just glad of the chance to give back to someone the help I have had through using forums over the last few years. I understand where you are coming form and I do find people are not that keen to share there knowledge, however I am always happy to share information because I never know you may one day return the favour, but maybe in even bigger quantities. Of course Janice when I joined the forum many people have shared there knowledge we me so really I am just doing the same as everyone else here.
Here is some information on normalization.
This link is to Microsoft knowledge base articles on normalization.
http://support.microsoft.com/default.aspx?scid=kb;en-us;209534
I would also recommend you get your boss to buy you this book;)
Database Systems. A practical approach to design, implementation and management. Second Edition.
Revised and updated by Thomas Connolly and Carolyn Begg.
ISDN: 0-201-34287-1
This book is University standard and was used thoughout my degree course. It has a large chapter on normalization and it pays a lot of attention to SQL. It has great chapters on joining tables(), using aggregate functions like COUNT, SUM, MIN, AVG and MAX. It will show you have to build nested queries, inner and outer joins, exists and not exists, union, itersect and except, and do 2 and 3 table joins.
Janice I will take as an example the database that you gave me to start with and take you through how I converted it to a normalized database.
There is only 3 rules to normalization each one is called a "normal form". You start of with a database with unnormalized data this is called unnormalized form. To move on to first normal form you need to remove any repeating fields and create a new table for each set of related data. Bellow is the tables you gave me:
Now giving the information from your client you constructed the above tables. During our posts we found that the relationship between the 2 tables was a many-to-many situation. This was because each project was going to be having the same tasks.
Under the present unnormalized form our data table would have looked like this:
So you can see if this continues for thousands of project there would be a lot of repeated data. The taskname field repeats itself all the way through; “Identify Objectives” appears 3 times in 6 records. The status field repeats itself and it contains a lot of NULL values and so does Issues and notes fields.
According to database design methodology a many-to-many situation must be resolved with a relationship table. We would add in a table with the 2 primary keys as foreign keys in the new table. You could however have just a composite key but in your case it was better to just have a primary key.
Each projecttask table can belong to only 1 project
To get the project table to first normal form you would remove the status field because this is not required. By working with the status of each task you can workout the status of the entire project; this would therefore not be required.
So now we have something like the following which is in first normal form:
Now you need to add in the one-to-many relationships. You now have the following relationships:
Each project can have more than 1 Project/task but each Project/task can only belong to 1 Project.
Each Task can have more than 1 Project/Task but each Project/Task can only belong to 1 Task.
Each Project/task can have more than 1 issue but each issue can only belong to 1 Project/Task
Each Project/Task can have more than 1 note but each note can only belong to 1 Project/Task.
Each Status can have more than one Project/task but each Project/Task can have only 1 status
Now you have to insert the foreign key for the relationships in the appropriate tables:
Second normal form deals with Full Functional dependency. Full functional dependency indicate that if A and B are attributes of a relation, B is fully functionally depended on A if B is functionally dependent on A, but not on any proper subset of A.
In our structures there are no such cases there for we can move onto the 3rd normal form. I would advise however that you look for some more examples of this.
Third normal form eliminates fields that do not depend on the key. Again this was not the case with your data so there was nothing done here, however this is still not truly normalized as much as possible but in some cases it may be more inefficient for to have to lots of small tables. I can see that there maybe a case where the datecompleted field could repeat, many tasks can be completed on the same day, however this would be more efficient for it to stay where it is rather then being put in another table.
What we ended up with was a normalized table to 3rd normal form with no data redundancy, our database would be free from anomalies and our database integrity will not be compromised.
Janice learning normalization to me a hell of a long time but it has also saved me a long time. Getting information from the database on to web pages is a lot easier with normalized tables.
Anyway another one of these very long posts, i better let you go find out more information, more than what i can give you anyway :)
Hope this information helps you get a good start with normalization,
all the best
David
janice
03-16-2003, 04:42 PM
Ribeyed, thanks again.
I will definitely get that book.
This topic must be the most threaded discussion on this forum.
I have learned a lot from it and I hope the over 404 forum members who have "listened in" to this discussion have learned a thing or two from it also.
I am confident my demo will go well on Tuesday. I wish I could say to my boss, "someone helped me tremendously do this".
What is important is my profound gratitute for everything you have done to make this a success.
janice
03-17-2003, 09:41 AM
Hi Ribeyed!
I just have a question/issue.
First a question.
This is in reference to the edit.asp page.
First an issue.
When you create a new project, by default the statusID is set to 1 which is 'Not started' and % Completed is 0.
This works fine when you just create a new project for the first time but once that project is completed and a new project project is created, statusID still defaults to 1. This is fine but the %Completed still says 100.
This should say 0 at the start of a new project, right?
Now the question.
Once a new task is started, anytime you update an issue / notes, you should be able to see that in the viewprojectissues.asp page. This is not doing that.
I created a test project and when I tried to updating notes box and issues, it appears to have saved but when I ran the viewprojectissues.asp, it displays the message 'sorry no notes at this time.
Thank you very much!
Ribeyed
03-17-2003, 02:57 PM
hi Janice,
please find attached an updated edit.asp page. Sorry for these teething problems, not to worry i have now fix the first one for you.
2 things i did to the page, firstly i created an sql to check the tblprojecttasks table to see if all the tasks for the project are completed. I then enclosed all the rest of the code in an if statement based on what is returned from the above sql statement. If all the tasks are completed then the recordset should be emtpy therefore display the page, but if the recordset is not empty then the project is not completed so we display the page.
2nd thing is the project completed, this was just an error with the sql statement, i needed to add to the sql to include the tblProjecttasks.Project_ID field and then select the records that have the current project_id and the statusID is not equal to the completed task statusID, hope that made sense.
Im away to look at the second part, i will pst again very soon, i know you have to show this tomorrow, i have a few hours spare tonight so fire away.
Ribeyed
03-17-2003, 03:00 PM
hi,
oops forgot to attach file:o
Ribeyed
03-17-2003, 04:22 PM
hi Janice,
fixed the second problem. The error was again in the edit.asp page. I was inserting the wrong foriegn key into the Notes and Issues tables. I have now fixed the problem and its now working fine.
janice
03-17-2003, 09:13 PM
Hi Ribeyed!
I apologize for responding now.
we were involved in swapping old pcs with new ones and configuring them.
First, please do not apologize for the problems.
I should be apoligizing for all that I am taking from you and nothing to give back so far.
I am eternally indebted.
Your explanations on the first posting made sense.
I am downloading both and I believe once I run them, they will completely take care of all bugs.
Again, again, again, thank you and I hope I don't have to bother you anymore on this.
I am truly short on works to express how I feel.
janice
03-17-2003, 09:48 PM
hi Ribeyed!
Now %Completed is stuck at 0%.
I have completed 3 tasks which should have moved the %completed to 37.5% but it is still 0%.
I will try and see resolve this.
If I have already started to irritate then fine but if not, please help still needed but let me see if I can get it.
Ribeyed
03-18-2003, 09:40 AM
hi Janice,
fixed the problem, just an error with the sql statement. Please find attached the new edit.asp page.
janice
03-18-2003, 10:04 AM
oh the Good Lord!
Hi Ribeyed!
Just in time for my demo.
Bless you tenfold!!!!
You are forever my hero!!!!
Ribeyed
03-18-2003, 10:20 AM
lol,:D no problem ;) good luck!
janice
03-21-2003, 09:28 AM
hi Ribeyed!
My demo which was supposed to have occurred on Tuesday was postponed till Thursday yesterday afternoon and it went VERY well.
They liked what they saw but too bad I could not say you did a majority of it.
There were minor changes recommended and I have implemented most of those changes.
I want to once again accept my most sincere and profound appreciation for everything.
j
Ribeyed
03-21-2003, 11:59 AM
hi Janice,
good stuff i am glad all went well. Its not a problem i understand your position regarding the work i did, i'm just glad that everything went well. Any more problems just post them here and we can take a look at it.
janice
04-04-2003, 11:51 AM
hi Ribeyed!
Can I trouble you once more, please.
I have a couple of new changes to make.
The client now wants to make a project have its on tasks. In other words, rather than have 8 tasks regardless of what new project is created, they now want certain tasks be associated with a particular project based on project type.
As a result, I have created two additional tables.
One called tblProjectType and the other called tblTypeTask.
tblProjectType has two fields, typeID, projectType.
This table is prepopulated.
The tbltypeTask table is a bridge between tblTask and tblProjectType and contains only two fields:
TypeID and TaskID.
I have changed my stored procedure to reflect this new change.
I was wondering if you can help me make the change in edit.asp. I think that is the only place a change is needed or you tell me where I can make this change.
I tried making the change and it isn't working so far.
Secondly, (this is really not critical but they would like to change it also) to change the percentage to number.
Example, instead of saying 12.5% completed, they would like to see 1 out of 8 completed 2 of 8 completed or 1 out of whatever the number of task is completed instead of the % completion.
I promise to leave you alone after this no matter what else is required to be changed.
Ribeyed
04-04-2003, 01:04 PM
hi Janice,
not a problem will take a look at it for you. Can you post the edit.asp page for me and mark the bits you tried to add to it?
i'll have a look at what you are trying to do, maybe find out where your having problems and help you get these changes made.
janice
04-04-2003, 04:43 PM
hi Ribeyed!
In the original edit.asp page, tasks are coded to be associated with a paticular project name.
This time, projectType is going to determine what tasks are associated with a particular project.
The first thing I tried fixing was the arraySort. Originally we had the number at 8.
Now, we won't know how many tasks need to be associated to a project. The maximum number is still 8 but a project could have anywhere from 1 task to 8.
The other is I tried adding tblprojectType and tblTypeTask to this line:
<table border="1" bordercolor="silver" cellpadding="0" cellspacing="0" bgcolor="silver" width="100%">
<tr>
<td bgcolor="silver">Task Names:</td>
<td> <%
It did not error out but it did nothing either.
I am attaching the edit.asp and the new relationship table.
Like I said earlier, changing the percentage is desirable but not really critical at this time.
Thank you my angel!
janice
04-04-2003, 04:53 PM
sorry for dumping again.
here is the revised relationship diagram and the schema.
Ribeyed
04-06-2003, 10:21 AM
hi Janice,
just a quick post to ask how long you got for to make these changes?
Im still working on a solution to another post on this forum and its a bigger project than yours so its taking a bit of time. I promise i will look at it and get make to you before your meeting with the client but i need to know what my time limit is.
thanks
janice
04-06-2003, 12:03 PM
hi Ribeyed!
I have always said you are God's gift to us.
Please take your time and complete that other one.
I can make an argument to them that I am working on the changes to be release2.
They are actually using it as it is now.
I am also trying to see if I can do it.
So Please take your time, good Angel
janice
04-14-2003, 09:07 AM
Hi Ribeyed!
I know we have already talked about this and you extremeley busy with work and something else you are doing for someone.
I just have one quick question.
On the edit.asp, the client is saying that when they are working on more than one projects concurrently, they have a bug when they attempt to update a task.
Recall that in the edit page, we have statusNames:
Not Started, Started, In Progress, On Hold, Completed.
Now when they are working on a project, if they choose the status of 'Not Started', when they start working on another project, they get cannot see the status 'Not Started'.
It gets tied up by the task currently using that status.
I have tried adding AdOpenDynamic and AdLockOptimistic hoping that will solve it but I am running into issues.
Is there any way you can suggest to me how to fix this so a client can use them concurrently?
Thank you and I apologize for this.
Ribeyed
04-21-2003, 07:57 AM
Hi Janice,
taking a look at your project today, just to clear up a few things to do with the tasks.
Each project can have a number of tasks from 1 to 8, does the number of tasks for each project depend on the type of project or are each and every new project unique or slightly different in some way?
janice
04-21-2003, 12:59 PM
hi Ribeyed!
Nice to hear from you again!
You know the questions you raised were the exact same questions we raised during the requirements gathering phase but they insisted they wanted it just the way we did it before.
Now they are making some few changes.
1, Now, a project is driven by a project type.
The project types are prepopulated. I am not sure if I had sent you the new table structures.
Also, each project is now unique.
Rather than have every project use the same tasks, now each project is unique but more importantly, tasks, like you said, depend on project type.
According to them, a project may have one task based on the project type or it could have 8 tasks, again based on what the project type is.
Please let me know if you want the new structures sent to you again.
Ribeyed
04-21-2003, 01:18 PM
hi Janice,
yes i got the new tables. Good news or bad news first?
...........................
hmm.....:( bad first i think.
current structure no good. Need to scrap it and start again. mmm...project and task is no longer a many-to-many situation, it is now only a one-to-many, each project can have many tasks but each task can belong to only 1 project.
This means that tblProjectTasks is no longer required or even valid. Currently we have 6 tables linking to this one so all these need to come out and well...hmmm.....we need to start again!
Good news Janice:D
I will continue to help you, but my help maybe very slow. Still got a few weeks of major work for my boss to do over the next fews weeks, setting up a windows 2000 server LAN and WAN network, mail server to install and configure, VPN to web server for database replication and a million other things for the entire domain. This is all before i get back to developing there e-commerce site :(
I normally work from home but i have been at the office for the last 2 weeks and will be there at least another 2 weeks.
I will try to come up with a new database structure over the next few days then we can take it from there.
janice
04-21-2003, 01:36 PM
Sorry, I forgot to also mention that I am having another type of problem and that is anytime the client updates a task with one status, that status is no longer available in the option menu.
For instance, if one task is updated with the 'Not 'Started' status stage, if another user tries to update another task with the same 'Not Started' option, that option is no longer available to be selected.
I am not sure why that is happening.
Ribeyed
04-21-2003, 01:43 PM
hi Janice,
not getting what the problem with that is.:confused: The way it works is that there are say 5 stages for the task from not started to completed. Each task start off as being Not started. You can submit the page as many times as you like the Not started status will stay there, if how ever you move the task up a stage, the Not started disapears. This is because it is started you don't want the task going down in status do you? surely you can't start a task then set it back to not started that doesn't make sense.
janice
04-21-2003, 04:32 PM
hi Ribeyed!
I did not make any changes to it.
I left it the entire code the way you had it.
At first, I checked my stored proc to see if that was the problem but it was not.
I think that when the select statement gets the data (5 statuses) from the database, it passes them to a dataset and them they get retrieved from there and recycled just like you said but somehow, it doesn't allow for concurrent usage.
What I have witnessed after it was brought to my attention is that if, for instance, you have 2 projects going on at the same time, and you update a task (remember every single project uses the same 8 tasks), then the next developer working on another project attempts to update his/her task, if the developer wants to update his/her task with the same status that was used by the last developer, that status is not available.
A perfect example would be right now, they are working on 4 different projects. As soon as the first developer creates his/her project and goes to the edit screen to update a task, say from 'Not Started' to 'Started', 'Not Started' is no longer on the status option menu.
If the same developer updates another task to 'In Progress', then 'In Progress' disappears.
If you still have a working copy of the projects, please try pretending like you are working on 2 different projects by creating 2 dummy projects.
Create the first project, update the first task, maybe the second also, then create another project.
If you go to update it, the only 2 status options available are 'On Hold' and 'Completed'. The rest cannot be found on the option list any more.
Ribeyed
04-21-2003, 06:22 PM
hi Janice,
yeap i get it now, bummer all i can say is design flaw on my part :o . Ok lets put that to the side for them moment well sort that with the database structure redesign.
janice
05-02-2003, 09:34 AM
hi Ribeyed!
Hope is well with you!
I sincerely feel bad for bothering you but I need your help URGENTLY.
Remember the Task stages with status that is not working correctly?
The client is supposed to have 'Not Started', 'Started', 'In Progress', 'On Hold', 'Completed'.
Right now, nothing is showing on the Task Stages, not even the 'Completed' status.
As a result, edit.asp is not working anymore since no status is being passed to the edit page.
I am in a panicky mode right now.
The way it was before was, was allowing me to buy time.
Everything else can wait until we can get this to work.
Please HELP!
If there are some tips you can give me to get to work on my own, that would be great.
Thanks,
Ribeyed
05-02-2003, 12:58 PM
hi Janice,
will promise a few hours to work this out for you tomorrow, in the mean time i would start to basically fault find. I suggest you start by working looking for the lines that relate to the status and in particular statusID. Check the drop down box for the status and find out the list/menu name. On the other page look to see where i have write this to a variable. Start by writing that variable out to screen, it it appears then move on if not stop there and find out why. I would next check the places where this variable is being used, again write as much as you can out to screen make sure the values are being written to the variables.
janice
05-02-2003, 01:31 PM
hi Ribeyed!
Glad to hear from you.
First, the only dropdown box we have is the edit.asp page and right now, the dropdown box is blank.
If you are referring to the stored procedure, I have already checked that and that works fine.
Infact I verified it with one of our stored procedure experts who said the problem has to be with our asp page.
Unfortunately, he knows nothing about asp.
The other favor I would ask is IF (this is a big if giving how busy you are), if you have time, it would help me if the code is well commented.
That way, I can figure things like this out without having to bother you.
But if you can't this time, that is fine.
This is the error I am getting. The error will be followed by the line of code where the error is coming from.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
/track/edit.asp, line 115
and the line:
set statusset = DataConnection.execute(thissql)
This is the recordset:
thissql = "SELECT * FROM tblStatus where tblStatus.StatusID = "&txtstatus&""
set statusset = DataConnection.execute(thissql)
I will try some more troubleshooting and see if I get lucky.
janice
05-02-2003, 01:45 PM
hi Ribyed!
Sorry, I just wanted to pass along some information that might help us pinpoint the problem.
Just to test, I went to tblstatus table and added the same values twice.
By that I mean, this is the way it currently is:
1 Not Started
2 New
3 In Progress
4 On Hold
5 Completed
and when you go to edit.asp, Task Stages dropdown box was blank.
I went and added another set of statuses (just for testing only) that looks like this now:
1 Not Started
2 New
3 In Progress
4 On Hold
5 Completed
6 Not Started
7 New
8 In Progress
9 On Hold
10 Completed
and now, the error is gone and edit is working again.
So I don't know if this helps in narrowing down the source of the problem.
Ribeyed
05-02-2003, 02:03 PM
hi Janice,
the error you gave is the SQL statement. Normally because the sql statement is not execute until line 115 that is where the error says it has occured. The fact that the list/menu is empty also means that the recordset was empty and therefore again a problem with the sql statement. The syntax for the sql statment looks to be ok which only leaves the parameter. I would check to make sure what the value of the txtStatus is before passing it to the sql statement make sure this is working.
As far as commenting goes, yes i am really sorry i am very bad at that, not commenting my code, shame on me. Next time i am going through i will add comments into the code for you, that is no problem Janice.
I am still under pressure at work and will be for at least another month. This has dramatically cut into the amount of time i have free, i am working 9am-7pm at the moment and i normally work from home but last month and this month i have been work at the office.
janice
05-02-2003, 02:18 PM
Ribeyed, trust me I clearly understand.
Infact I feel lousy asking you to help giving how busy you are and how much time you have spent on this.
That is why I raised the issue of commenting; that way, all I have got to do is follow your comment and at least pinpoint the problem.
Once a problem is determined, I am sure that I can either solve it or at least present it to someone who can solve as long as the problem is narrowed.
If you squeeze out tomorrow like you said, I will hope begin to spend more time understanding it and hopefully will eliminate the need to harrass you as much as I am doing.
I am also hoping that some of our forum members can understand what is going on enough to assist whenever possible.
Thank you for all you have done.
janice
05-05-2003, 12:15 PM
hi Ribeyed!
I know this is becoming a harrassment.
I am sorry but I was wondering if you have had time to look at this thiw w/end.
I have tried posting this question to other forums.
They would ask me to post the entire code and when I do, no body response anymore.
Hopefully, someone from this forum can take a look and see if they can help me figure this out.
Client and management are meeting on wednesday about this since they can't use this anymore because all the status stages have dropped off from the dropdown menu.
I am choked to near death right now.
I am trying to think of a way to rewrite the entire code but I can't even make any headway with my thought process.
Ribeyed
05-05-2003, 06:14 PM
HI Janice,
working on it now, had to rewrite it from scratch, almost finished so don't worry.
Ribeyed
05-05-2003, 07:21 PM
hi Janice,
here you go :D
janice
05-05-2003, 10:23 PM
hi Ribeyed!
Just getting a response from you reduced my blood pressure.
You certainly have saved me (again).
Thank you so so very much.
As happy as I am that you have continued to help me, I feel badly for being helpless and for bugging you too much and knowing how swamped you.
There are lots of comments.
It certainly helps to pinpoint problem whenever they occur.
Thank you!
I just have two more questions and I intend to leave you alone, come what may.
I noticed we eliminated the tblPojecttask table entirely.
That means we decided to determine what tasks belong to what project.
Can I see what your insert statements are (the code that inserts into project, and task tables).
When I tested with the one I modified, what I noticed was the tasks did not start from the first task like we had it before.
After I created a record and went to view the record with projectList (my version), it started putting a checkbox from the middle of tasknames versus starting from the beginning.
It's got to be my insert statement.
Please, once again, accept my heartfelt thanks.
I hope that somehow, there will be a day and way in this life where I can repay you; it's a small world and you never know.
Ribeyed
05-06-2003, 03:36 AM
Hi Janice,
No problem, i see what you are saying about the tasks but the only way that would be mixed up is if you didn't enter them in order in the first place.
If you find the lines of code like this:
sql = "SELECT * FROM tblTasks WHERE tblTasks.Project_id = "&ProjectID&""
set tasksSet = DataConnection.execute(sql)
If you cange it to:
sql = "SELECT * FROM tblTasks WHERE tblTasks.Project_id = "&ProjectID&" ORDER BY tblTasks.TaskID ASC "
set tasksSet = DataConnection.execute(sql)
This should make sure that the first task is displayed first and so on.
Janice again goog luckwith your meeting on Wednesday, any problems just give me a shout.
janice
05-06-2003, 06:58 AM
hi Ribeyed!
If I don't thank you again, I want you to know that it is because I have run out of words to express my profound gratitude and appreciation for everything you have done and continue to do.
Thanks (again)!
Now I am still confused about what we did here this time around ( I stay confused about this. I can't wait for it to go away.
The way you did it now means that a project can have one task or 8 task.
The thing that is confusing me, at least from the database point of view is if a project has 4 tasks associated with it, how can I insert the same project_id 4 times into the task table?
If you recall, I sent a modified datamodel that has two new tables called tblprojectType and tblTypeTask.
tblProjectType has a prepopulated data about project types and TblTypeTask has typeID and taskID.
This way, tblTypeTask has various combinations of projecttypes and tasks associated with each project type.
As a result, Ihave this stored procedure which retrieves the correct project types and their associated tasks and inserts them into projecttask table.
Please take a look. Even though it is a stored procedure, the insert statements and select are basic.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertNProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertNProject]
GO
/* Move the identity value of the new record into a variable */
SET @ProjectID = @@IDENTITY
/* Create and populate new tblprojecttasks record */
INSERT INTO tblProjectTasks (
Project_ID,
TaskID,
statusID
)
SELECT @ProjectID,
t.taskID,
s.StatusID
FROM (SELECT b.taskID FROM tblProjectType a INNER JOIN tblTypeTask 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
If you have a moment to spare, please tell how the insert is done.
Or even better, if I know what portion of code needs to be modified to make it work with this code, that would be really wonderful.
janice
05-07-2003, 03:52 PM
Hi Ribeyed!
Well, hmm; I am including just for visual stimulation, the data model that determines what type of tasks are associated with a particular project based on project ID.
tblProjectType, tbltasks, tbltypetask are all pre-populated with data.
tblProjectType table has all the project types that any given project they develop must have. Any project goes to projecttype table to select project types associated with that.
tbltypetask has all combinations of tasks and projecttypes.
for instance, if a project type is 'New IT Order', and the id is one; and if there are 3 tasks associated with a project based on that project type, and the taskIDs are 1,2.3 then the typeTask table would look like:
typeid taskid
1 1
1 2
1 3
All the combinations are then selected and inserted into the projecttask table.
That is what my stored proc is intended to do and does that flawlessly.
The one big favors(one of many I have asked so far) is to either tell me how your new model captures this or redline the portions of the edit.asp page that I need to change to capture this.
If I knew you were going to write the code again from scratch, I would have reminded you of the new model.
I thought you were just fixing the status dropdown.
As I did the first time the client was coming here, I called in sick because I did not have answer to the question of why it is taking too long to modify something I did.
I am including the new data model.
Those that have pre-populated data, come with data in them.
I know I have worked you to death, and for free but don't give up now, please.
Ribeyed
05-07-2003, 05:17 PM
Hi Janice,
OK hmmm.... I think that we are both slightly of with what the client needs and what the database structure should actually be. I have spent the last half an hour looking at your database and yes I think I get what you are trying to do. However I have a good feeling that what I did for you really does the same maybe not as technical as what you have come up with but in the end should do the same thing.
I think you need to give me more information about the type of projects and project tasks associated with the projects. Sorry Janice but this is like programming blind here.
You have got to give me a clear unambiguous definition of what a Project is, what is a task, for example:
A project can run for various weeks, months and years. A project can be called one name and no other project can be called the same name. An example of a project name is "Apollo" or "PR110". A project can have many tasks. Each task is associated with one project and only one project, therefore unique to the project. Similar projects can have the same amount of tasks but the tasks are different. An example of a task is "Design" or "Implementation". Another example of a task could be "Fill in daily report" or "Mail quote for project Apollo". Each task can have many stages but every task has the same amount of stages. An example of a stage is "Not Started" or even "Waiting Client Approval"
That was an example of what I would be expecting to know before I designed anything for a client. Even reading back through the above example it started raising lots of issues with your structure.
Again with reading the above example it also raised issues with mine. Where do we go from here :(
My way of thinking with the code and structure was like each project has its own unique set of tasks and that’s it. Doesn't matter if the same types of projects have the same number of tasks, the database doesn't need to know that so you don't need to build that in. The only issue I would have with my structure would be if the same project types have not only the same number of tasks but the tasks themselves are the same.
Just to raise another issue with both our structures, what happens with the status of the tasks? Are they now going to be unique to the task or the project type or are the going to be the same for every task?
Again Janice i will help you with this, again time very limited but lets get this sorted out once and for all, maybe it would help if you were to email me the information you gathered from your client regarding the specifications of the project.
janice
05-07-2003, 08:25 PM
Hi Ribeyed!
I truly understand how you feel and I am really, really sorry.
It is unfortunate but we are dealing with a client who, to me, doesn't seem to know what they want.
I would have been perfectly happy if the status (task stages) are working because trust me before I even started posting this to the forum, it had changed so many times; so much so that I could no longer program it.
What I got out of my last meeting with them was that now they wanted me to add project types and that they want the project type to drive what the tasks are.
They gave me copies of their project types and according to them, will NEVER change.
According to them, there are 3 different sections that use the tracking program we designed - Information System (IS), GIS and Networking.
The Networking section has their own project types and the tasks associated with those projects;
The IS section has their set of project types that have their own set of tasks; and the GIS has their own set of project types that have their associated tasks.
According to the client, projects are unique but they can have any of the project types.
So on the form page, they would have an input text box to type in the name of the project and then a dropdown that has all the project types. They would then select a project type that belong to that project.
Again, the project types do not change and the tasks do not change.
What is unique is the project name.
On the model that I attached in my last thread, that is based on what they gave me just before the status (task stages) broke.
The tblProjectType has all the project types for the 3 sections - IS, Networking and GIS.
The tblTask is same. It has all the tasks for all 3 sections.
The tblTypeTask has all the combinations of projecttypes and the tasks associated with them.
What should happen now is when a client types in a new project and selects a project type, tblprojecttasks gets populated with tasks for a particular project based on that project type.
The client has a lot to do with the problem because I have seen a lot of project tracking programs in the past but I have never seen one done the way we are doing it now but my biggest thing is to get the status to work.
Like I told my boss, once this is done, I am prepared to drop this whole thing if they come back and ask for another change.
I have been assured that the way the data model looks right now is the way it is going to be. No more changes.
I can't apologize enough for all the inconvenience.
I really can't.
Just to recap, there are now a total of 12 project types and each section knows which project types belong to them.
There are 16 tasks.
Each project can have one task associated with it or it can have 8 tasks associated with.
All depend on the project types.
This project type/task combinations have been combined in the tblTypeTask table and won't change (they said).
Project name is unique but they can be of any of the project types in the dropdown.
This whole thing has been going on since October last year.
Each time, I would declare that it is ready but they would come up with something new.
My boss has had a meeting with them to let them know that if this current spec changes, they would have to get support somewhere because this has tied me up now for so long, not to talk about the immense inconvenience it has caused you.
This is not your type tracking program.
My PROMISE is that if we can get the task stages to work correctly, even if the project type thing is not implemented, I repeat, I PROMISE you, you won't hear from me again about this program.
janice
05-10-2003, 07:48 AM
hi Ribeyed!
Unfortunately, my desperation forces me to bug again.
I wasn't sure if the information I provided in my last thread is sufficient or not since I have not gotten any feedback from you.
I have been given an ultimatum of Monday, although I lied to them that I am testing and may not complete my testing on till late Monday.
As busy as you are, and as much confusion and inconvenience this has caused you, please help out ONE more time.
If you don't think you will have time, can you tell me areas that I need to change to make either the first code work or the second one?
As I think back to this whole thing, it would have been better for me if I had told my boss originally that this could not be done.
Having gone this far, I have been thinking of a possible to tell him that but it won't make any sense.
So please if you need an additional information, please let me know as soon as you can so I can provide that.
I am in the office today.
Like I said in the last post, at this time, I don't care of the project/relationship get changed from what is was but as long as the status is ok.
If we need to move the status from the edit page into the insert page and still have tasks appear in the edit page and still work, I will also be happy with that.
Ribeyed
05-11-2003, 06:24 PM
HI janice,
the last pages i sent you, has the status fixed and working correctly, what is the problem your having with it????
janice
05-11-2003, 07:55 PM
hi Rieyed!
Even just getting a response from you really makes a difference mentally.
The only problem I have is that the data model that accompanies the edit.asp pages does not allow me to create the right project/task combination.
Remember my question after I received it?
The new model you attached has 5 tables - tblissues, tblnotes, tblprojects, tblstatus and tbltasks and the tbltask table is where project_ids that are associated with tasks are inserted.
My question was if I have a situation where I have, say 4 tasks associated with a project, that would mean:
project_id will be inserted 4 times for 4 different tasks like this:
project_id taskname
1 design
1 implement
1 deploy
1 maintain
The above would be an example where 4 different tasks are associated with 1 project (project_id).
Given the data model you attached with the new edit.asp pages, how is it possible to capture a situation where it would be possible to associate 1 task with one project or 8 tasks with one project.
Maybe I am not understanding how you are inserting records into them.
So what I am saying is if you can help explain to me how you are associating tasks with a project, that, as always would be greatly appreciated.
If the only thing that needs fixing is the database, we can work together with your direction to get the database to work with the new edit.asp
Also, Ribeyed, please work me as busy as you are and as much as you have helped so far.
i need this to work long enough for me to study what you did and be able to be self-reliant as far as modifying it.
I will NOT bother you on this again once we have it working this time, even if they recommend more changes.
I am forever indebted to you for not only your help but with the patience.
Ribeyed
05-13-2003, 04:02 AM
Hi Janice,
My question was if I have a situation where I have, say 4 tasks associated with a project, that would mean:
project_id will be inserted 4 times for 4 different tasks
that is correct because now every project has more that one task but every task can only be associated with 1 project, therefore a one-to-many situation so no relationship table is required and a foriegn key from the many table is included in the one table.
As i mentioned in previous post this is fine unless you are having having the same task associated with more than one project.
See Janice the database doesn't need to know that a particular project has x amount of tasks, all you do is select and count the tasks with the projectID x.
when you do your create a new project page this is where you control the amount of tasks that are entered for each project. Again in the database it doen't matter how many tasks are associated with each project, on the front end thats where you control how many tasks you let the user insert for each project.
janice
05-13-2003, 07:12 AM
hi Ribeyed!
As always, thanks for taking the time to responde.
Just as a refresher, I am taking you back to the post I sent on march 21. This post came before task stages broke.
Please take a moment to read this again.
here it is:
************************************************
hi Ribeyed!
Can I trouble you once more, please.
I have a couple of new changes to make.
The client now wants to make a project have its on tasks. In other words, rather than have 8 tasks regardless of what new project is created, they now want certain tasks be associated with a particular project based on project type.
As a result, I have created two additional tables.
One called tblProjectType and the other called tblTypeTask.
tblProjectType has two fields, typeID, projectType.
This table is prepopulated.
The tbltypeTask table is a bridge between tblTask and tblProjectType and contains only two fields:
TypeID and TaskID.
I have changed my stored procedure to reflect this new change.
I was wondering if you can help me make the change in edit.asp. I think that is the only place a change is needed or you tell me where I can make this change.
I tried making the change and it isn't working so far.
Secondly, (this is really not critical but they would like to change it also) to change the percentage to number.
Example, instead of saying 12.5% completed, they would like to see 1 out of 8 completed 2 of 8 completed or 1 out of whatever the number of task is completed instead of the % completion.
I promise to leave you alone after this no matter what else is required to be changed.
*********************************************.
So you see what I meant when I said that I thought you were just fixing the task stages only.
If I knew you were doing it from scratch, I would have reminded you of this post.
What we have now, does not meet there spec above.
This is indeed very frustrating right now.
Don't take this the wrong way because it is not intended for you but I would have been better off telling my boss from the outset that I could not program the tracking thing the way the client wanted.
I don't know what would have happened but it would have been better than my current situation now.
What I have now is a situation where he is asking me why it is taking longer to fix something I did.
My original goal was to take my time and study what you did and be able to make changes when needed but it broke so soon that I don't know what to do.
So as you can see from the post, we didn't do it the way the client wanted it.
The client needs to have a task be associated with a project based on project type.
Even though project is unique, projecttypes are the same and the client can be working on more than one projects that may require similar project types.
For instance, a project called Water Basing Cleaning can have a project type called Data CD extract.
They could get another project with a different name but may use the same project type called data CD extract or they may use a different project type.
This project type can have 5 tasks associated with this project.
I also posted a sample form with the project types.
Which means that same task can be used by more than one project.
It all depends on projecttype.
Did this make it kind of clear?
What is killing me right now is their work stoppage.
They are not using this tracking anymore because it is not working.
Ribeyed
05-13-2003, 12:08 PM
hi Janice,
i think we need to talk we are at a sticking point here, i'm am still sure what i have given you is working add will work, so can you maybe suggest a way you can contact me and we can talk about this one?
janice
05-13-2003, 01:27 PM
hi Ribeyed!
maybe my email address?
I can't leave my number here.
It might be detected, I don't know.
I am sorry for all I am putting you through.
All I am saying is that I have read a lot of your posts and I know how much you care about data normalization.
So the database design you attached with your most current asp pages won't solve the problem.
Assume for instance that you have a project called Water Basin Exploits; the project type for this project is called CD extract.
According to the client matrix, this project called Water Basin Exploits based on project type called CD extracts has 5 tasks associated with it.
This would mean that on the edit.asp screen, we would have project Name Water Basin Exploits and the 5 tasks associated with it are laid out on the this page like we have it coded so far.
This would mean that project name Water Basin Exploits would have 5 project_ids each id for each task.
How can I do the insert statement for this to work.
Obviously, the way we have the data model right now, all the inserts will be made into the task table.
How is this possible?
This is the only reason the beautiful edit.asp page you code is not working so far for me.
I have a question somewhere to see if someone can somehow show me how I can make the current database structure work with your asp code but everyone kept saying you need another table to bridge between task/project.
This fact I don't even need to consult with anyone to know it to be true but I did just to be sure.
Ribeyed
05-13-2003, 04:18 PM
hi Janice,
yes dear i get what you are saying, but what you haven't mention, which i think i am getting from you now is that all the projects with the same project type have the same tasks, if this is the case then yes, sure you are correct the last database structure doesn't allow that, but if a task is associated with one project and only one project then the relationship is fine there is no problem. We need to be clear on this Janice, like you have mentioned in your last post yes the database needs to be normalized but you have to make a decision on what the relationship type is. Before we agreed it was a many-to-many situation, but with your client changes and the information you gave me made the relationship one-to-many. Like i said beofre this makes a difference!
janice
05-14-2003, 07:41 AM
hi Ribeyed!
I am glad the fog is finally getting clear.
If you go to page 6 of this threaded discussion, you will see the new data model I attached.
It is called newTrackingerd.
Please ignore a table called TaskRequest. It is put there in error.
There are now a total of 8 tables.
We still have tbltask, tblissues, tblnotes, tblprojects, tblprojecttasks.
But we added 2 new tables:
tblprojectType and tblTypeTask.
tbltasks has all the task information while tblProjectType has all the projectTypes that belong to various projects.
tblTypeTask has various combinations of tasks and project types.
So the relationship between them then is one-to-many between tbltasks and tbltypetask on the one hand, and one-to-many between tblprojectType and tblTypeTasks
because like you said correctly, "all the projects with the same project type have the same tasks".
The only role a project plays now is that a client enters a project name and selects the type from a dropdown.
Once a submission is made, you are taking to projectlist page.
When you select a project to be edited, all tasks associated with that project are displayed on the edit page.
Again this task/project association is based on projectType.
Thank you and I continue to apologize for this heavy burden.
Ribeyed
05-26-2003, 01:21 PM
Hi Janice,
please check your email and get back to me, i have completed your new database structure and edit.asp page.
For anyone who is following this thread i will post the new code soon.
Ribeyed
05-26-2003, 02:55 PM
Hi JAnice,
my email is down at the moment, have a look at this let me know if you need anything changed.
janice
05-26-2003, 09:41 PM
hi Ribeyed!
Once again, my most sincere apologies.
It is 10:38pm us time and just by stroke of luck, I checked this forum.
I had been checking my email all day.
So I am responding late because, as I said, I just checked this forum now just for the sake of it, not expecting anything.
I will download and run and advise.
Again, as I have said many, many times, I can't find any way to express my profound appreciation for your help.
I can't!!!!
Than you!! :):)
Ribeyed
05-27-2003, 06:54 PM
hi Janice here is some code for creating a new project.
Ribeyed
05-31-2003, 03:29 PM
Hi Janice,
here is the new edit.asp page with fixes :)
Ribeyed
05-31-2003, 03:30 PM
dam forgot to attach file:o again!
webdeveloper.com
Copyright Internet.com Inc., All Rights Reserved.