Allcodes--ASPCallCrystalReportwithStoreProcedure
’SimpleStoredProcParam.asp
<%@ LANGUAGE="VBSCRIPT" %>
<title>Crystal Reports ASP Example - Using Stored Procedure Parameters</title>
<%
’==============================================================================
’ WORKING WITH THE REPORT DESIGNER COMPONENT AND ASP TO USE STORED PROCEDURES
’ AND PASS VALUES TO STORED PROCEDURE PARAMETERS
’==============================================================================
’
’ CONCEPT
’ The Application object (oApp) is needed so that we can create the
’ report object.
’ Once we have created the report object (oRpt), we can then
’ gain access to such things the "DatabaseParameters" in that
’ report.
’
’ ALWAYS REQUIRED STEPS (contained in AlwaysRequiredSteps.asp)
’ - create the application object
’ - create the report object
’ - open the report
’
’ WORK WITH STORED PROCEDURE PARAMETERS
’ - get the database in the report
’ - get the database’s Stored Procedure Parameters
’ - get the specific Store Procedure Parameter
’ - save the new value to the Stored Proc Param
’
’ MORE ALWAYS REQUIRED STEPS
’ - retrieve the records
’ - create the page engine
’
’ DISPLAY THE REPORT
’ - display the report using a smart viewer
’ = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
’==================================================================
’==================================================================
’ ALWAYS REQUIRED STEPS
’
’ Include the file AlwaysRequiredSteps.asp which contains the code
’ for steps:
’ - create the application object
’ - create the report object and open the report
%>
<%
’ This is the name of the report being used in this example.
’ This variable is being used in the AlwaysRequiredSteps.asp
’ To use a different report, change it here.
reportname = "SimpleStoredProcParam.rpt"
%>
<!-- #include file="AlwaysRequiredSteps.asp" -->
<%
’ If it is easier to understand, simply delete the line above,
’ and replace it with the entire contents of the file
’ AlwaysRequiredSteps.asp
’==================================================================
’==================================================================
’ LOG ONTO THE EXAMPLES SYTEM DSN AGAINST MS SQL SERVER "PUBS" SAMPLE DATABASE
userid = "sa"
Password= ""
session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False
set crtable = session("oRpt").Database.Tables.Item(1)
crtable.SetLogonInfo "Automation", "pubs", cstr(userid),cstr(Password)
’Create a System DSN called "Automation" pointing to the "pubs" database on SQL Server.
’ Add your database’s username and password to the SimpleStoredProcParam.asp page where indicated.
’==================================================================
’==================================================================
’ WORK WITH STORED PROCEDURE PARAMETERS
’ - get the database’s Stored Procedure Parameters
’ Create a variable and point it to the Stored Procedure Parameter
’ in the report
set StoredProcParamCollection = Session("oRpt").ParameterFields
’ - get the specific Store Procedure Parameter
’ Create a variable and point it to the specific stored procedure
’ that we want to work on
Set ThisParam = StoredProcParamCollection.item(1)
Set ThisParam2 = StoredProcParamCollection.item(2)
Set ThisParam3 = StoredProcParamCollection.item(3)
’ - save the new value to the Stored Proc Param
’ Create a variable and store the new value for the Stored Procedure
’ in it
NewParamValue = "10"
NewParamValue2 = "50"
NewParamValue3 = ("’"&a&"’")
’ Save the new value for the Stored Procedure in the Store Procedure
ThisParam.SetCurrentValue cint(NewParamValue), 7
ThisParam2.SetCurrentValue cint(NewParamValue2), 7
ThisParam3.SetCurrentValue cstr(NewParamValue3), 12
’==================================================================
’==================================================================
’
’ MORE ALWAYS REQUIRED STEPS
’ - retrieve the records
’ - create the page engine
’ - create the smart viewer and point it to rptserver.asp
’
%>
<!-- #include file="MoreRequiredSteps.asp" -->
<%
’ If it is easier to understand, simply delete the line above,
’ and replace it with the entire contents of the file
’ MoreRequiredSteps.asp
’==================================================================
’==================================================================
’ INSTANTIATE THE REPORT VIEWER
’
’When using the Crystal Reports in an ASP environment, we use
’the same page-on-demand Report Viewers used with the Crystal Web Component Server.
’There are six Report Viewers:
’
’1. Report Viewer for ActiveX
’2. Report Viewer for Java using Browser JVM
’3. Report Viewer for Standard HTML with Frames
’4. Report Viewer for Standard HTML
’5. Report Viewer for Java Using Java Plugin
’6. Report Viewer for Netscape Plug-in (ActiveX)
’
’The Report Viewer that you use will based on the browser’s display capablities.
’For Example, you would not want to instantiate one of the Java viewers if the browser
’did not support Java applets. For purposes on this demo, we have chosen to
’define a viewer. You can through code determine the support capabilities of
’the requesting browser. However that functionality is inherent in the Crystal
’Reports RDC and is beyond the scope of this demonstration app.
’
’We have chosen to leverage the server side include functionality of ASP
’for simplicity sake. So you can use the SmartViewer*.asp files to instantiate
’the smart viewer that you wish to send to the browser. Simply replace the line
’below with the Smart Viewer asp file you wish to use.
’
’The choices are SmartViewerActiveX.asp, SmartViewerJava.asp, JavaPluginViewer.asp,
’ActiveXPluginViewer.asp. SmartViewerHTMLFrame.asp, and SmartViewerHTMLPAge.asp.
’Note that to use this include you must have the appropriate .asp file in the
’same virtual directory as the main ASP page.
’
’*NOTE* For SmartViewerHTMLFrame and SmartViewerHTMLPage, you must also have
’the files framepage.asp and toolbar.asp in your virtual directory.
’=============================================================================
’ DISPLAY THE REPORT
’ - display the report using a smart viewer
’
’ Include one of the Smart Viewers.
’ - Report Viewer for ActiveX = SmartViewerActiveX.asp
’ - Report Viewer for Java using Browser JVM = SmartViewerJAVA.asp
’ - Report Viewer for Standard HTML = SmartViewerHTMLPage.asp
’ - Report Viewer for Standard HTML w/ Frames = SmartViewerHTMLFrame.asp
’ - Report Viewer for Java Using Java Plugin = JavaPluginViewer.asp
’ - Report Viewer for Netscape Plug-in = ActiveXPluginViewer.asp
’=============================================================================
%>
<!-- #include file="SmartViewerActiveX.asp" -->
<%
’==================================================================
’ If it easier for you to conceptualize this code by seeing it all
’ contained in a single file, simply delete the line above, and
’ replace it with the entire contents of the file being included.
’==================================================================
%>
