ASP Call Crystal Report with Store Procedure(4)
'SPString.SQL
if exists (select name from sysobjects where name = 'SPROCString')
drop proc SPROCString
GO
CREATE PROCEDURE SPROCString
@pTitle varchar(80)
AS
SET NOCOUNT ON
SELECT
*
FROM
TitleView
WHERE
Title = @pTitle
or @pTitle = '*'
'toolbar.asp
<%
' 05/02/98
' Added the following features:
' Tab Query String Parameter
' - This is the selected tab's tabArray index value.
' Page Expiry Time
' - The page will expire when downloaded by browser so that user is insured that all data
' will be current.
' DrillDown Tabs
' - Added in the session("tabArray") object to keep track of the drill down tabs.
' Search
' - Added javascript window.alert function call to indicate when text is not found in rpt view.
' Goto Page Text Box
' - Added textbox and filenew.gif so user can enter and request desired page number.
' NOTE: Netscape 2.0 browsers do not call the on submit event handler when the image is selected.
' Thus, the user will not be warned when incorrect data is entered into the goto page box.
' This problem does not happen when the user selects return.
Response.Expires = 0
' Viewer Tab images
drilld = "<img border=0 src='/viewer/images/toolbar/pdrilld.gif' alt = 'Parent Group'>"
drillu = "<img border=0 src='/viewer/images/toolbar/cdrillu.gif' alt = 'Current Group'>"
previewu = "<img border=0 src='/viewer/images/toolbar/pviewu.gif' alt = 'Preview'>"
previewd = "<img border=0 src='/viewer/images/toolbar/pviewd.gif' alt = 'Preview'>"
' Set the correct numbers on the paging buttons
brch = request.querystring("BRCH")
if brch <> "" then
brch = "&" & "brch=" & brch
basepage = "<a href=" & chr(34) & "javascript:parent.parent.location='rptserver.asp?init=html_frame&page=1'" & chr(34) & ">"
end if
getPageCommand = "rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe" & brch
searchFound = request.querystring("SEARCHFOUND")
if searchFound <> "" then
if Cint(searchFound) = 0 then
messageText = "onLoad = " & chr(34) & "window.alert('Text Not Found in the Report');" & chr(34)
end if
end if
CurrentPageNumber = CStr(session("CurrentPageNumber"))
lastknownpage = CStr(session("lastknownpage"))
LastPageNumber = CStr(session("LastPageNumber"))
if CurrentPageNumber = "" then
CurrentPageNumber = "1"
end if
if lastknownpage = "" then
lastknownpage = "0"
end if
if LastPageNumber <> "" and (CurrentPageNumber = LastPageNumber) then
lastknownpage = CurrentPageNumber
' remember the last known page
session("lastknownpage") = CurrentPageNumber
nextlink = ""
lastlink = ""
if CInt(CurrentPageNumber) > 1 then
previouspage = CInt(CurrentPageNumber) - 1
previouslink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=" & previouspage & brch & "'" & chr(34) & ">"
firstlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=1" & brch & "'" & chr(34) & ">"
else
previouslink = ""
firstlink = ""
end if
else
if (CInt(lastknownpage) < CInt(CurrentPageNumber)) and LastPageNumber = "" then
' remember the last known page
session("lastknownpage") = CurrentPageNumber
lastknownpage = CurrentPageNumber & "+"
else
if lastknownpage <> LastPageNumber then
lastknownpage = lastknownpage & "+"
end if
end if
if CInt(CurrentPageNumber) > 1 then
previouspage = CInt(CurrentPageNumber) -1
previouslink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=" & previouspage & brch & "'" & chr(34) & ">"
firstlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=1" & brch & "'" & chr(34) & ">"
else
previouslink = ""
firstlink = ""
previouspage = 1
end if
nextpage = CInt(CurrentPageNumber) + 1
nextlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=" & nextpage & brch & "'" & chr(34) & ">"
lastlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=32756" & brch & "'" & chr(34) & ">"
end if
%>
<html>
<script language="javascript">
function ValidateNumber(val, msg)
{
if (val == "")
{
alert("Please enter a value for the " + msg + " field.");
return (false);
}
var checkOK = "0123456789";
var checkStr = val;
var allValid = true;
var decPoints = 0;
var allNum = "";
for (i = 0; i < checkStr.length; i++)
{
ch = checkStr.charAt(i);
for (j = 0; j < checkOK.length; j++)
if (ch == checkOK.charAt(j))
break;
if (j == checkOK.length)
{
allValid = false;
break;
}
allNum += ch;
}
if (!allValid)
{
alert("Please enter only digit characters in the " + msg + " field.");
return (false);
}
var chkVal = allNum;
var prsVal = parseInt(allNum);
if (chkVal != "" && !(prsVal >= "1"))
{
alert("Please enter a value greater than \"0\" in the " + msg + " field.");
return (false);
}
return (true);
}
var currentValue = "<% response.write CurrentPageNumber %>";
function checkValue(){
var pageNumber = document.forms[0].elements[0].value;
if(!ValidateNumber(pageNumber, "Goto Page Number")){
document.forms[0].elements[0].value = currentValue;
parent.status = "Please Enter a Positive Numeric Value. NO SPACES";
return false;
}
else
// a new page will be downloaded with the next page number
return true;
}
</script>
<body background="/viewer/images/toolbar/toolbg.gif" topmargin=0 leftmargin=0 <% response.write messageText%>>
<form method="POST" name=getPg target = "CrystalViewerPageFrame" action = <% response.write getPageCommand %> onSubmit="return checkValue();">
<table border=0 width=100% cellpadding=0 cellspacing=0 height=100%><tr nowrap>
<td nowrap align=right width=10%><% response.write firstlink %><img border=0 src="/viewer/images/toolbar/first.gif" alt="First page"><% response.write previouslink %><img border=0 src="/viewer/images/toolbar/prev.gif" alt="Previous page"></td>
<td nowrap valign=center align=center width=10%> <b> <%response.write CurrentPageNumber %> </b> of <%response.write lastknownpage %></td>
<td nowrap align=left width=10%><%response.write nextlink %><img border=0 src="/viewer/images/toolbar/next.gif" alt="Next page"></a><%response.write lastlink %><img border=0 src="/viewer/images/toolbar/last.gif" alt="Last page"></a></td>
<td align=left width=5%><input type=text value = "<%response.write CurrentPageNumber %>" size=4 maxlength=5 name=PAGE alt = "Goto Page" ></td>
<td align=left width=5%> <input type=image src="/viewer/images/toolbar/filenew.gif" alt="Goto Page"></td>
</form>
<form method="POST" name=pf target="CrystalViewerPageFrame" action="rptserver.asp?cmd=srch&viewer=html%5Fframe&vfmt=html_frame&page=<%response.write CurrentPageNumber %>&dir=FOR&case=0<% response.write brch%>">
<comment><td nowrap align=center width=15%><a href="javascript:parent.parent.location='rptserver.asp?cmd=rfsh&viewer=html%5Fframe&vfmt=html%5Fframe&page=<%response.write CurrentPageNumber %>'"><img border=0 src="/viewer/images/toolbar/refresh.gif" alt="Refresh"></a></td></comment>
<td align=right width=15%><input type=text size=10 maxlength=255 name=text></td>
<td align=left width=5%><input type=image src="/viewer/images/toolbar/search.gif"></td>
<td nowrap valign=bottom align=right width=20%>
<%
dim counter
dim tmpArray
dim upperBound
tmpArray = session("tabArray")
counter = Int(UBound(tmpArray) / 5)
if tmpArray(0) <> "EMPTY" then
response.write drillu
if counter > 0 then
response.write "<a href=" & chr(34) & "javascript:parent.parent.location = 'htmstart.asp?tab=" & (counter * 5) & "'" & chr(34) & ">"
response.write drilld & "</a>"
End If
response.write "<a href=" & chr(34) & "javascript:parent.parent.location = 'htmstart.asp?tab=" & 0 & "'" & chr(34) & ">"
response.write previewd & "</a>"
else
response.write previewu
end if%>
</td>
</tr></table>
</form>
</body>
</html>
'SmartViewerHTMLFrame.asp
<%
'=================================================================
' This file contains the ASP code for the Smart Viewer HTML Page
'=================================================================
response.redirect ("htmstart.asp")
%>
'SmartViewerHTMLPage.asp
<%
'=================================================================
' This file contains the ASP code for the Smart Viewer HTML Page
'=================================================================
response.redirect ("rptserver.asp")
%>
'SmartViewerJava.asp
<%
'This file contains the HTML code to instantiate the Smart Viewer Java.
'
'You will notice that the Report Name parameter references the rptserver.asp file.
'This is because the report pages are actually created by rptserver.asp.
'Rptserver.asp accesses session("oApp"), session("oRpt") and session("oPageEngine")
'to create the report pages that will be rendered by the ActiveX Smart Viewer.
'
%>
<html>
<head>
<title>Seagate Java Viewer using Browser's JVM</title>
</head>
<body bgcolor=C6C6C6>
<SCRIPT LANGUAGE="JavaScript"><!--
var _ns3 = false;
var _ns4 = false;
//--></SCRIPT>
<COMMENT><SCRIPT LANGUAGE="JavaScript1.1"><!--
var _info = navigator.userAgent;
var _ns3 = (navigator.appName.indexOf("Netscape") >= 0 && _info.indexOf("Win16") < 0 && _info.indexOf("Mozilla/3") >= 0);
var _ns4 = (navigator.appName.indexOf("Netscape") >= 0 && _info.indexOf("Win16") < 0 && _info.indexOf("Mozilla/4") >= 0 );
//--></SCRIPT></COMMENT>
<SCRIPT LANGUAGE="JavaScript"><!--
if(_ns3==true)
document.writeln( '<applet code=com.seagatesoftware.img.ReportViewer.ReportViewer codebase="/viewer/JavaViewer" id=ReportViewer width=100% height=100% archive="/viewer/JavaViewer/ReportViewer.zip">' );
else if (_ns4 == true)
document.writeln( '<applet code=com.seagatesoftware.img.ReportViewer.ReportViewer codebase="/viewer/JavaViewer" id=ReportViewer width=100% height=100% archive="/viewer/JavaViewer/ReportViewer.jar">' );
else
document.writeln( '<applet code=com.seagatesoftware.img.ReportViewer.ReportViewer codebase="/viewer/JavaViewer" id=ReportViewer width=100% height=100% >' );
//--></SCRIPT>
<param name=ReportName value="rptserver.asp">
<param name=HasGroupTree value=true>
<param name=ShowGroupTree value=true>
<param name=HasRefreshButton value=false>
<param name=HasPrintButton value=true>
<param name=HasExportButton value=true>
<param name=cabbase value="/viewer/JavaViewer/ReportViewer.cab">
</applet>
</body>
</html>
if exists (select name from sysobjects where name = 'SPROCString')
drop proc SPROCString
GO
CREATE PROCEDURE SPROCString
@pTitle varchar(80)
AS
SET NOCOUNT ON
SELECT
*
FROM
TitleView
WHERE
Title = @pTitle
or @pTitle = '*'
'toolbar.asp
<%
' 05/02/98
' Added the following features:
' Tab Query String Parameter
' - This is the selected tab's tabArray index value.
' Page Expiry Time
' - The page will expire when downloaded by browser so that user is insured that all data
' will be current.
' DrillDown Tabs
' - Added in the session("tabArray") object to keep track of the drill down tabs.
' Search
' - Added javascript window.alert function call to indicate when text is not found in rpt view.
' Goto Page Text Box
' - Added textbox and filenew.gif so user can enter and request desired page number.
' NOTE: Netscape 2.0 browsers do not call the on submit event handler when the image is selected.
' Thus, the user will not be warned when incorrect data is entered into the goto page box.
' This problem does not happen when the user selects return.
Response.Expires = 0
' Viewer Tab images
drilld = "<img border=0 src='/viewer/images/toolbar/pdrilld.gif' alt = 'Parent Group'>"
drillu = "<img border=0 src='/viewer/images/toolbar/cdrillu.gif' alt = 'Current Group'>"
previewu = "<img border=0 src='/viewer/images/toolbar/pviewu.gif' alt = 'Preview'>"
previewd = "<img border=0 src='/viewer/images/toolbar/pviewd.gif' alt = 'Preview'>"
' Set the correct numbers on the paging buttons
brch = request.querystring("BRCH")
if brch <> "" then
brch = "&" & "brch=" & brch
basepage = "<a href=" & chr(34) & "javascript:parent.parent.location='rptserver.asp?init=html_frame&page=1'" & chr(34) & ">"
end if
getPageCommand = "rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe" & brch
searchFound = request.querystring("SEARCHFOUND")
if searchFound <> "" then
if Cint(searchFound) = 0 then
messageText = "onLoad = " & chr(34) & "window.alert('Text Not Found in the Report');" & chr(34)
end if
end if
CurrentPageNumber = CStr(session("CurrentPageNumber"))
lastknownpage = CStr(session("lastknownpage"))
LastPageNumber = CStr(session("LastPageNumber"))
if CurrentPageNumber = "" then
CurrentPageNumber = "1"
end if
if lastknownpage = "" then
lastknownpage = "0"
end if
if LastPageNumber <> "" and (CurrentPageNumber = LastPageNumber) then
lastknownpage = CurrentPageNumber
' remember the last known page
session("lastknownpage") = CurrentPageNumber
nextlink = ""
lastlink = ""
if CInt(CurrentPageNumber) > 1 then
previouspage = CInt(CurrentPageNumber) - 1
previouslink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=" & previouspage & brch & "'" & chr(34) & ">"
firstlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=1" & brch & "'" & chr(34) & ">"
else
previouslink = ""
firstlink = ""
end if
else
if (CInt(lastknownpage) < CInt(CurrentPageNumber)) and LastPageNumber = "" then
' remember the last known page
session("lastknownpage") = CurrentPageNumber
lastknownpage = CurrentPageNumber & "+"
else
if lastknownpage <> LastPageNumber then
lastknownpage = lastknownpage & "+"
end if
end if
if CInt(CurrentPageNumber) > 1 then
previouspage = CInt(CurrentPageNumber) -1
previouslink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=" & previouspage & brch & "'" & chr(34) & ">"
firstlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=1" & brch & "'" & chr(34) & ">"
else
previouslink = ""
firstlink = ""
previouspage = 1
end if
nextpage = CInt(CurrentPageNumber) + 1
nextlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=" & nextpage & brch & "'" & chr(34) & ">"
lastlink = "<a href=" & chr(34) & "javascript:parent.location='rptserver.asp?cmd=toolbar%5Fpage&viewer=html%5Fframe&vfmt=html%5Fframe&page=32756" & brch & "'" & chr(34) & ">"
end if
%>
<html>
<script language="javascript">
function ValidateNumber(val, msg)
{
if (val == "")
{
alert("Please enter a value for the " + msg + " field.");
return (false);
}
var checkOK = "0123456789";
var checkStr = val;
var allValid = true;
var decPoints = 0;
var allNum = "";
for (i = 0; i < checkStr.length; i++)
{
ch = checkStr.charAt(i);
for (j = 0; j < checkOK.length; j++)
if (ch == checkOK.charAt(j))
break;
if (j == checkOK.length)
{
allValid = false;
break;
}
allNum += ch;
}
if (!allValid)
{
alert("Please enter only digit characters in the " + msg + " field.");
return (false);
}
var chkVal = allNum;
var prsVal = parseInt(allNum);
if (chkVal != "" && !(prsVal >= "1"))
{
alert("Please enter a value greater than \"0\" in the " + msg + " field.");
return (false);
}
return (true);
}
var currentValue = "<% response.write CurrentPageNumber %>";
function checkValue(){
var pageNumber = document.forms[0].elements[0].value;
if(!ValidateNumber(pageNumber, "Goto Page Number")){
document.forms[0].elements[0].value = currentValue;
parent.status = "Please Enter a Positive Numeric Value. NO SPACES";
return false;
}
else
// a new page will be downloaded with the next page number
return true;
}
</script>
<body background="/viewer/images/toolbar/toolbg.gif" topmargin=0 leftmargin=0 <% response.write messageText%>>
<form method="POST" name=getPg target = "CrystalViewerPageFrame" action = <% response.write getPageCommand %> onSubmit="return checkValue();">
<table border=0 width=100% cellpadding=0 cellspacing=0 height=100%><tr nowrap>
<td nowrap align=right width=10%><% response.write firstlink %><img border=0 src="/viewer/images/toolbar/first.gif" alt="First page"><% response.write previouslink %><img border=0 src="/viewer/images/toolbar/prev.gif" alt="Previous page"></td>
<td nowrap valign=center align=center width=10%> <b> <%response.write CurrentPageNumber %> </b> of <%response.write lastknownpage %></td>
<td nowrap align=left width=10%><%response.write nextlink %><img border=0 src="/viewer/images/toolbar/next.gif" alt="Next page"></a><%response.write lastlink %><img border=0 src="/viewer/images/toolbar/last.gif" alt="Last page"></a></td>
<td align=left width=5%><input type=text value = "<%response.write CurrentPageNumber %>" size=4 maxlength=5 name=PAGE alt = "Goto Page" ></td>
<td align=left width=5%> <input type=image src="/viewer/images/toolbar/filenew.gif" alt="Goto Page"></td>
</form>
<form method="POST" name=pf target="CrystalViewerPageFrame" action="rptserver.asp?cmd=srch&viewer=html%5Fframe&vfmt=html_frame&page=<%response.write CurrentPageNumber %>&dir=FOR&case=0<% response.write brch%>">
<comment><td nowrap align=center width=15%><a href="javascript:parent.parent.location='rptserver.asp?cmd=rfsh&viewer=html%5Fframe&vfmt=html%5Fframe&page=<%response.write CurrentPageNumber %>'"><img border=0 src="/viewer/images/toolbar/refresh.gif" alt="Refresh"></a></td></comment>
<td align=right width=15%><input type=text size=10 maxlength=255 name=text></td>
<td align=left width=5%><input type=image src="/viewer/images/toolbar/search.gif"></td>
<td nowrap valign=bottom align=right width=20%>
<%
dim counter
dim tmpArray
dim upperBound
tmpArray = session("tabArray")
counter = Int(UBound(tmpArray) / 5)
if tmpArray(0) <> "EMPTY" then
response.write drillu
if counter > 0 then
response.write "<a href=" & chr(34) & "javascript:parent.parent.location = 'htmstart.asp?tab=" & (counter * 5) & "'" & chr(34) & ">"
response.write drilld & "</a>"
End If
response.write "<a href=" & chr(34) & "javascript:parent.parent.location = 'htmstart.asp?tab=" & 0 & "'" & chr(34) & ">"
response.write previewd & "</a>"
else
response.write previewu
end if%>
</td>
</tr></table>
</form>
</body>
</html>
'SmartViewerHTMLFrame.asp
<%
'=================================================================
' This file contains the ASP code for the Smart Viewer HTML Page
'=================================================================
response.redirect ("htmstart.asp")
%>
'SmartViewerHTMLPage.asp
<%
'=================================================================
' This file contains the ASP code for the Smart Viewer HTML Page
'=================================================================
response.redirect ("rptserver.asp")
%>
'SmartViewerJava.asp
<%
'This file contains the HTML code to instantiate the Smart Viewer Java.
'
'You will notice that the Report Name parameter references the rptserver.asp file.
'This is because the report pages are actually created by rptserver.asp.
'Rptserver.asp accesses session("oApp"), session("oRpt") and session("oPageEngine")
'to create the report pages that will be rendered by the ActiveX Smart Viewer.
'
%>
<html>
<head>
<title>Seagate Java Viewer using Browser's JVM</title>
</head>
<body bgcolor=C6C6C6>
<SCRIPT LANGUAGE="JavaScript"><!--
var _ns3 = false;
var _ns4 = false;
//--></SCRIPT>
<COMMENT><SCRIPT LANGUAGE="JavaScript1.1"><!--
var _info = navigator.userAgent;
var _ns3 = (navigator.appName.indexOf("Netscape") >= 0 && _info.indexOf("Win16") < 0 && _info.indexOf("Mozilla/3") >= 0);
var _ns4 = (navigator.appName.indexOf("Netscape") >= 0 && _info.indexOf("Win16") < 0 && _info.indexOf("Mozilla/4") >= 0 );
//--></SCRIPT></COMMENT>
<SCRIPT LANGUAGE="JavaScript"><!--
if(_ns3==true)
document.writeln( '<applet code=com.seagatesoftware.img.ReportViewer.ReportViewer codebase="/viewer/JavaViewer" id=ReportViewer width=100% height=100% archive="/viewer/JavaViewer/ReportViewer.zip">' );
else if (_ns4 == true)
document.writeln( '<applet code=com.seagatesoftware.img.ReportViewer.ReportViewer codebase="/viewer/JavaViewer" id=ReportViewer width=100% height=100% archive="/viewer/JavaViewer/ReportViewer.jar">' );
else
document.writeln( '<applet code=com.seagatesoftware.img.ReportViewer.ReportViewer codebase="/viewer/JavaViewer" id=ReportViewer width=100% height=100% >' );
//--></SCRIPT>
<param name=ReportName value="rptserver.asp">
<param name=HasGroupTree value=true>
<param name=ShowGroupTree value=true>
<param name=HasRefreshButton value=false>
<param name=HasPrintButton value=true>
<param name=HasExportButton value=true>
<param name=cabbase value="/viewer/JavaViewer/ReportViewer.cab">
</applet>
</body>
</html>
