Hi,
I need to save approval comments into an approvals
table. The approval comments are relative to a
selected project management document.
In the approvals table there are only some approval
records(or none) as not all the documents are approved
at any given one time.
The script which I wrote works when it finds records
already stored in the table that are relative to the
selected document. When it finds no records for the
selected document, then it starts looping while
displaying blank page.
In brief I need a script which updates an existing
record for a selected document (tracked by RevID)
and/or inserts a new record when no records exist for
the selected document.
Also I need to be able to display information (i.e.
RES_NAME in MSP_WEB_RESOURCES and APPROVAL_DATE in
CPS_WEB_DOC_APPROVALS) for those selected documents
which have already records in the approvals table -
this is the second select statement in the script
approval.asp. Now it displays information when it
finds records in the appr.table but, when it doesn't,
it doesn't load the page at all.
Please find approval.asp, approvalcommenta.asp and db
structure below.
This is the action SQL statement -what I say is: if
the DOC_ID in the approvals table (i.e. rsAPPs)
doesn't match the DOC_ID in the documents table (i.e.
rsRisk) then there is no record for that document in
the approvals table and therefore a new record should
be inserted. Otherwise - if it matches -update
existing record.
<script language="JScript">
function FinishBtn_OnClick(nRevID) {
var appr=<%=rsApps("REV_ID")%>;
var risk=<%=rsRisk('REV_ID')%>;
if (appr != risk) {
var sqlAPPR = "insert into [CPS_WEB_DOC_APPROVALS] (
\n" +
"[DOC_ID], \n" +
"[REV_ID], \n" +
"[WRES_ID], \n" +
"[APPROVAL_DATE], \n" +
"[COMMENTS] ) \n" +
"values ( \n" +
"<%=rsRisk('DOC_ID')%>,\n" +
"<%=rsRisk('REV_ID')%>,\n" +
"<%=rsRisk('OWNER_WRES_ID')%>,\n" +
"'" + VTDateToUSDate( new Date ) + "',\n" +
"'" + idForm.idCOMMENTS.value + "' )\n";
}
else {
var sqlAPPR = "UPDATE [CPS_WEB_DOC_APPROVALS] SET \n"
+
"[DOC_ID] = <%=rsRisk('DOC_ID')%>,\n" +
"[REV_ID] = <%=rsRisk('REV_ID')%>,\n" +
"[WRES_ID] = <%=rsRisk('OWNER_WRES_ID')%>, \n" +
"[APPROVAL_DATE] = '" + VTDateToUSDate( new Date ) +
"',\n" +
"[COMMENTS] = '" + idForm.idCOMMENTS.value + "' \n" +
" WHERE [DOC_ID] = <%=rsApps('REV_ID')%>\n";
}
// Set form variables
idForm.sqlAPPR.value = sqlAPPR;
idForm.submit();
}
</script>
-----------------------------------------
THAT'S THE WHOLE SCRIPT - APPROVALCOMMENT.ASP
----------------------------------------
<%@ Language="VBScript" %>
<%
//FIRST SQL SELECT STATEMENT
var sql = "select distinct CPS_WEB_RISKS.*,
CPS_WEB_DOC_REVISIONS.*, CPS_WEB_DOCUMENTS.* from ";
sql += "(CPS_WEB_RISKS inner join ";
sql += " (CPS_WEB_DOC_REVISIONS inner join ";
sql += " (CPS_WEB_DOCUMENTS inner join
MSP_WEB_RESOURCES ";
sql += " on CPS_WEB_DOCUMENTS.[OWNER_WRES_ID]
MSP_WEB_RESOURCES.[WRES_ID]) ";
sql += " on CPS_WEB_DOC_REVISIONS.[DOC_ID]
CPS_WEB_DOCUMENTS.[DOC_ID]) ";
sql += "on CPS_WEB_RISKS.[REV_ID]
CPS_WEB_DOC_REVISIONS.[REV_ID]), ";
sql += "CPS_WEB_DOC_ACCESS, CPS_WEB_DOC_APPROVALS
where ";
sql += "CPS_WEB_DOC_REVISIONS.[CURRENT] = 1";
sql += " and ";
sql += "CPS_WEB_DOCUMENTS.[REV_ID] = ";
sql += Request.QueryString("RevID");
sql += " and ";
sql += "CPS_WEB_DOCUMENTS.OWNER_WRES_ID
MSP_WEB_RESOURCES.WRES_ID";
var rsRisk = oConn.Execute(sql);
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual
Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY LANGUAGE=javascript onload="return
window_onload()">
<FORM ID="idForm" name="RiskForm"
ACTION="ApprovalCommenta.asp" METHOD="post"
STYLE="margin: 0; padding: 0;">
<%
//SECOND SQL SELECT STATEMENT
var sql = "select CPS_WEB_DOC_APPROVALS.*,
CPS_WEB_DOC_REVISIONS.*, [RES_NAME] from ";
sql += "CPS_WEB_DOC_APPROVALS, CPS_WEB_DOC_REVISIONS,
MSP_WEB_RESOURCES where ";
sql += "CPS_WEB_DOC_APPROVALS.[REV_ID]
CPS_WEB_DOC_REVISIONS.[REV_ID] ";
sql += "and CPS_WEB_DOC_APPROVALS.[WRES_ID]
MSP_WEB_RESOURCES.[WRES_ID] ";
sql += "and CPS_WEB_DOC_REVISIONS.[REV_ID] = ";
sql += Request.QueryString("RevID");
sql += " order by [VERSION_ID]";
var rsApps = oConn.Execute( sql );
//while( !rsApps.EOF ) {
%>
<TD>This risk document was approved on:
<%=rsApps("APPROVAL_DATE")%></TD><br>
<TD>by:
<%=rsApps("RES_NAME")%></TD><p><TD></TD>
<!--
COMMENT
----------------
-->
<BR>
<DIV CLASS="SectionHeaderBlank">Provide a short
comment:</DIV>
<DIV STYLE="FONT-SIZE: 70%; MARGIN-LEFT: 12px;
MARGIN-TOP: 12px">
<TABLE CLASS="DisplayText" CELLSPACING="0"
CELLPADDING="0" STYLE="FONT-SIZE: 1em">
<TR>
<TD>
<LABEL FOR="idCOMMENTS"
CLASS="InputLabel" TITLE="" ACCESSKEY="C">Comment:
</LABEL>
</TD>
</TR>
<TR height=3></TR>
<TR>
<TD>
<Textarea class="TextArea" id="idCOMMENTS"
name="COMMENTS" style="FONT-SIZE: 1em; HEIGHT: 125px;
WIDTH: 600px"><%=rsApps("COMMENTS")%></textarea>
</TD>
</TR>
</TABLE>
<P align=center>
<TD>
<BUTTON ID="idFinishBtn" NAME="idFinishBtn"
CLASS="InputButton" STYLE="width:75"
ONCLICK="FinishBtn_OnClick()" TITLE="Finish"
ACCESSKEY="F">Finish</BUTTON>
</TD>
<TD>
<BUTTON id="idCancelBtn" name="idCancelBtn"
class="InputButtonHighlight" STYLE="width:75;"
onclick="CancelBtn_OnClick()" TITLE="Cancel"
ACCESSKEY="C">Cancel</BUTTON>
</TD>
</TR></TABLE> </P>
<!--<INPUT ID="idAPPROVAL_DATE" TYPE="hidden"
NAME="APPROVAL_DATE" >-->
<SCRIPT LANGUAGE="JScript">
<!--idForm.idAPPROVAL_DATE.value = JSDateToUKDate( new
Date );-->
</SCRIPT>
<INPUT ID="sqlAPPR" NAME="sqlAPPR" TYPE="hidden"
style="LEFT: 20px; TOP: 395px"></INPUT>
</FORM>
<script language="JScript">
//action SQL statement as already mentioned
function FinishBtn_OnClick(nDocID) {
var appr=<%=rsApps("DOC_ID")%>;
var risk=<%=rsRisk('DOC_ID')%>;
if (appr != risk) {
var sqlAPPR = "insert into
[CPS_WEB_DOC_APPROVALS] ( \n" +
"[DOC_ID], \n" +
"[REV_ID], \n" +
"[WRES_ID], \n" +
"[APPROVAL_DATE], \n" +
"[COMMENTS] ) \n" +
"values ( \n" +
"<%=rsRisk('DOC_ID')%>,\n" +
"<%=rsRisk('REV_ID')%>,\n" +
"<%=rsRisk('OWNER_WRES_ID')%>,\n" +
"'" + VTDateToUSDate( new
Date ) + "',\n" +
"'" + idForm.idCOMMENTS.value
+ "' )\n";
}
else {
var sqlAPPR = "UPDATE
[CPS_WEB_DOC_APPROVALS] SET \n" +
"[DOC_ID]
<%=rsRisk('DOC_ID')%>,\n" +
"[REV_ID]
<%=rsRisk('REV_ID')%>,\n" +
"[WRES_ID]
<%=rsRisk('OWNER_WRES_ID')%>, \n" +
"[APPROVAL_DATE] = '" +
VTDateToUSDate( new Date ) + "',\n" +
"[COMMENTS] = '" +
idForm.idCOMMENTS.value + "' \n" +
" WHERE [DOC_ID]
<%=rsApps('DOC_ID')%>\n";
}
// Set form variables
idForm.sqlAPPR.value = sqlAPPR;
idForm.submit();
}
</script>
<P> </P>
</BODY>
</HTML>
-------------------------------------------
-------------------------------------------
----------THIS IS THE SCRIPT WHERE IT EXECUTES------
APPROVALCOMMENTA.ASP
<%
function Shell_ActionsPane_GetCurrentItemID() {
return 'idDocsRiskTC';
}
function SubmitRisk() {
var oConn;
var oRset;
var docID;
var revID;
var sqlAPPR = Request.Form("sqlAPPR").Item;
var sqlIDENT = "select @@identity";
try {
// create a connection and open a
transaction
oConn.Open();
oConn.BeginTrans();
//
// Approvals
//
sMsg += "<br>CPS_WEB_DOC_APPROVALS<br>";
sMsg += sqlAPPR;
sMsg += "<br>";
// begin the transaction & create a document
oConn.Execute( sqlAPPR );
// retrieve the ID of the document
oRset = oConn.Execute( sqlIDENT );
docID = oRset.Fields(0).Value;
oRset.Close();
} catch( e ) {
sMsg += "ADO Error: " +
oConn.Errors(0).Description + "<BR>";
oConn.RollbackTrans();
oConn.Close();
oConn = null;
return;
}
// commit and clean up
try {
oConn.CommitTrans();
sMsg += "<br><br>"
sMsg += "Transaction successfully completed";
oConn.Close();
oConn = null;
}
catch( ignore ) { }
Response.Redirect("Risk.asp");
}
SubmitRisk();
%>
<SCRIPT LANGUAGE="JScript">
</SCRIPT>
<DIV ID="idTranReturnCode">
<DIV CLASS="SectionHeaderInfo" STYLE="FONT-SIZE:
0.7em">Transaction results:</DIV>
<BR>
<DIV id="idTranMsg" CLASS="Text" STYLE="FONT-SIZE:
70%; MARGIN-LEFT: 12px; MARGIN-TOP:
8px"><%=sMsg%></DIV>
</DIV>
-------------------------------------------
-------------------------------------------
---------------DATABASE STRUCTURE--------------
if exists
(select * from sysobjects
where id = object_id(N'[PCOwner].[CPS_WEB_RISKS]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [PCOwner].[CPS_WEB_RISKS]
GO
CREATE TABLE [PCOwner].[CPS_WEB_RISKS] (
[RISK_ID] [int] identity ,
[DOC_ID] [int] NOT NULL , -- (FK)
CPS_WEB_DOCUMENTS.DOC_ID
[REV_ID] [int] NOT NULL, -- (FK)
CPS_WEB_DOC_REVISIONS.REV_ID
[RISK_NUM] [varchar] (28) NOT NULL,
[RISK_TYPE] [smallint] NOT NULL default 0 CHECK (
RISK_TYPE IN (0,1,2)) , -- 0=cost, 1=schedule,
2=quality
[DATE_IDENTIFIED] [datetime] NOT NULL,
[DATE_EXPIRES] [datetime] NULL ,
[LIKELIHOOD] [smallint] NOT NULL default 0 CHECK
( LIKELIHOOD IN (0,1,2)) , -- 0=low, 1=medium, 2=high
[SEVERITY] [smallint] NOT NULL default 0 CHECK (
SEVERITY IN (0,1,2)) , -- 0=low, 1=medium, 2=high
[DESCRIPTION] [varchar] (512) NOT NULL,
[COUNTER_MEASURE] [varchar] (512) NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [UC1_WEB_RISKS]
ON [PCOwner].[CPS_WEB_RISKS]([RISK_ID])
ON [PRIMARY]
GO
if exists
(select * from sysobjects
where id
object_id(N'[PCOwner].[CPS_WEB_DOCUMENTS]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [PCOwner].[CPS_WEB_DOCUMENTS]
GO
CREATE TABLE [PCOwner].[CPS_WEB_DOCUMENTS] (
[DOC_ID] [int] identity ,
[WPROJ_ID] [int] NOT NULL , -- (FK)
MSP_WEB_PROJECTS.WPROJ_ID
[WTASK_ID] [int] NULL , -- (FK) to task
table ???? FIXME
[OWNER_WRES_ID] [int] NOT NULL , -- (FK)
MSP_WEB_RESOURCES.WRES_ID
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [UC1_WEB_DOCUMENTS]
ON [PCOwner].[CPS_WEB_DOCUMENTS]([DOC_ID])
ON [PRIMARY]
GO
if exists
(select * from sysobjects
where id
object_id(N'[PCOwner].[CPS_WEB_DOC_ACCESS]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [PCOwner].[CPS_WEB_DOC_ACCESS]
GO
CREATE TABLE [PCOwner].[CPS_WEB_DOC_ACCESS] (
[DOC_ID] [int] NOT NULL , -- (FK)
CPS_WEB_DOCUMENTS.DOC_ID
[WRES_ID] [int] NOT NULL , -- (FK)
MSP_WEB_RESOURCES.WRES_ID
[ACCESS] [smallint] NOT NULL default 0 CHECK (
ACCESS IN (0,1)) -- 0=reader, 1=writer
) ON [PRIMARY]
if exists
(select * from sysobjects
where id
object_id(N'[PCOwner].[CPS_WEB_DOC_REVISIONS]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [PCOwner].[CPS_WEB_DOC_REVISIONS]
GO
CREATE TABLE [PCOwner].[CPS_WEB_DOC_REVISIONS] (
[REV_ID] [int] identity ,
[DOC_ID] [int] NOT NULL , -- (FK)
CPS_WEB_DOCUMENTS.DOC_ID
[CURRENT] [bit] NOT NULL default 1 ,
[VERSION_ID] [int] NOT NULL ,
[REV_DATE] datetime NOT NULL DEFAULT
CURRENT_TIMESTAMP,
[CHANGE_SUMMARY] [varchar] (512) NOT NULL
[DOC_STATUS] [smallint] NOT NULL default 0 CHECK
( DOC_STATUS IN (0,1,2)) , --0=draft,
1=under-review, 2=approved
[DOC_LINK] [varchar] (100) NULL ,
[NEXT_REV_DATE] [datetime] NULL ,
[LAST_DIST_DATE] [datetime] NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [UC1_DOC_REVISIONS]
ON [PCOwner].[CPS_WEB_DOC_REVISIONS]([REV_ID])
ON [PRIMARY]
GO
if exists
(select * from sysobjects
where id
object_id(N'[PCOwner].[CPS_WEB_DOC_APPROVALS]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [PCOwner].[CPS_WEB_DOC_APPROVALS]
GO
CREATE TABLE [PCOwner].[CPS_WEB_DOC_APPROVALS] (
[DOC_ID] [int] NOT NULL , -- (FK)
CPS_WEB_DOCUMENTS.DOC_ID
[REV_ID] [int] NOT NULL , -- (FK)
CPS_WEB_DOC_REVISIONS.REV_ID
[WRES_ID] [int] NOT NULL , -- (FK)
MSP_WEB_RESOURCES.WRES_ID
[APPROVAL_DATE] datetime NOT NULL DEFAULT
CURRENT_TIMESTAMP,
[COMMENTS] [varchar] (512) NULL
) ON [PRIMARY]
-------------------------------------------
-------------------------------------------
best regards,
Max
View Cart
