<%@ Language=VBScript %> <% '******************************************************************** 'This section contains the code used to retrieve parameters and 'store them as variables. Ideally, variables should be explicitly 'declared but I've been a bad developer and didn't do that here. '******************************************************************** 'Reset page variables WhereDocNo = "" WhereDocArea = "" WhereDocType = "" WhereRev = "" WhereTitle = "" WhereAuthor = "" WhereStatus = "" WhereComments = "" WhereStartDates = "" WherePubDates = "" '-------------------------------------------------------------------------------------------------- 'User's Doc Number fuzzy search criteria If Request("txtDocNo") = "Any" Then WhereDocNo = "" Else WhereDocNo = "AND (DocNo LIKE " & Chr(39) & "%" & Request("txtDocNo") & "%" & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's Doc Group fuzzy search criteria If Request("selDocGroup") = "0" Then WhereDocGroup = "" Else WhereDocGroup = "AND (DocGroup = (" & Chr(39) & Request("selDocGroup") & Chr(39) & ")) " End If '-------------------------------------------------------------------------------------------------- 'User's DocArea search criteria If Request("selDocArea") = "Any" Then WhereDocArea = "" Else WhereDocArea = "AND (DocArea = " & Chr(39) & Request("selDocArea") & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's DocType search criteria If Request("selDocType") = "Any" Then WhereDocType = "" Else WhereDocArea = "AND (DocType = " & Chr(39) & Request("selDocType") & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's Rev search criteria If Request("txtRev") = "Any" Then WhereRev = "" Else WhereRev = "AND (Rev = " & Chr(39) & Request("txtRev") & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's DocTitle search criteria If Request("txtTitle") = "Any" Then WhereTitle = "" Else WhereTitle = "AND (DocTitle LIKE " & Chr(39) & "%" & Request("txtTitle") & "%" & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's Author search criteria If Request("txtAuthor") = "Any" Then WhereAuthor = "" Else WhereAuthor = "AND (Author LIKE " & Chr(39) & "%" & Request("txtAuthor") & "%" & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's Status search criteria If Request("selStatus") = "Any" Then WhereStatus = "" Else WhereStatus = "AND (Status LIKE " & Chr(39) & "%" & Request("selStatus") & "%" & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's Comments search criteria If Request("txtComments") = "Any" Then WhereComments = "" Else WhereComments = "AND (Comments LIKE " & Chr(39) & "%" & Request("txtComments") & "%" & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's TCP Reference search criteria If Request("txtTCP") = "Any" Then WhereTCP = "" Else WhereTCP = "AND (ETCPRef LIKE " & Chr(39) & "%" & Request("txtTCP") & "%" & Chr(39) & ") " End If '-------------------------------------------------------------------------------------------------- 'User's Start Date search criteria If Request("txtStartDateBegin") = "Any" Then If Request("txtStartDateEnd") = "Any" Then WhereStartDates = "" Else WhereStartDates = "AND (StartDate <= CAST( " & Chr(39) & Request("txtStartDateEnd") & Chr(39) & "AS datetime)) " End If Else If Request("txtStartDateEnd") = "Any" Then WhereStartDates = "AND (StartDate >= CAST( " & Chr(39) & Request("txtStartDateBegin") & Chr(39) & "AS datetime)) " Else WhereStartDates = "AND (StartDate BETWEEN CAST( " & Chr(39) & Request("txtStartDateBegin") & Chr(39) & "AS datetime) AND CAST(" & Chr(39) & Request("txtStartDateEnd") & Chr(39) & "AS datetime)) " End If End If '-------------------------------------------------------------------------------------------------- 'User's Pub Date search criteria If Request("txtPubDateBegin") = "Any" Then If Request("txtPubDateEnd") = "Any" Then WherePubDates = "" Else WherePubDates = "AND (PubDate <= CAST( " & Chr(39) & Request("txtPubDateEnd") & Chr(39) & "AS datetime)) " End If Else If Request("txtStartDateEnd") = "Any" Then WherePubDates = "AND (PubDate >= CAST( " & Chr(39) & Request("txtPubDateBegin") & Chr(39) & "AS datetime)) " Else WherePubDates = "AND (PubDate BETWEEN CAST( " & Chr(39) & Request("txtPubDateBegin") & Chr(39) & "AS datetime) AND CAST(" & Chr(39) & Request("txtPubDateEnd") & Chr(39) & "AS datetime)) " End If End If '-------------------------------------------------------------------------------------------------- %> <% '************************************************************************* 'This section retrieves the recordset based on the variables stored 'in the section above. I've placed it in the body of the page so that 'error recovery will be more graceful - I can echo a custom error message 'back to the user on a page that has the same look and feel as the rest 'of my application rather than having the browser spit out some kind 'of cryptic 505 error. The user shouldn't even be aware that this page 'resides on a different server than the rest of the application. '************************************************************************* 'construct two sql strings - one for exact match, the other for fuzzy search sqlSearchExact = "SELECT DocNo, DocTitle, Author, StartDate FROM dbo.tblDocs "&_ "WHERE (Doc_ID = " & Chr(39) & Request("selDocNo") & Chr(39) & "); " sqlSearchFuzzy = "SELECT DocNo, DocTitle, Author, StartDate FROM dbo.tblDocs "&_ "WHERE (Doc_ID <> '') "&_ WhereDocNo &_ WhereDocGroup &_ WhereDocArea &_ WhereDocType &_ WhereRev &_ WhereTitle &_ WhereAuthor &_ WhereStatus &_ WhereComments &_ WhereTCP &_ WhereStartDates &_ WherePubDates &_ "ORDER BY DocNo;" 'UNCOMMENT LINES BELOW TO ECHO SQL STRINGS AT TOP OF ASP PAGE 'Response.Write("") 'Response.Write(sqlSearchExact) 'Response.Write("

") 'Response.Write(sqlSearchFuzzy) 'Response.Write("

") '*************************************************************************** 'Build ADODB connection string Set cnSearch = Server.CreateObject ("ADODB.Connection") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% '%%%%%%%%%% CONNECTION STRINGS - NOT THE SAME AS FOR SQL SERVERS! %%%%%%%%%% '%%%%%%%%% CRYSTAL SERVER HAS ITS OWN, SEPARATE SECURITY ACCOUNTS! %%%%%%%%% '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 'This connection string is used to connect to the production sql server. 'cnSearch.Open ("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=[userid]; pwd=[password]; Initial Catalog=[database name];Data Source=[production server name];") 'This connection string is used to connect to the development sql server; note that there is no password. cnSearch.Open ("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=[userid];Initial Catalog=[database name];Data Source=[development server name]") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 'Create the recordset Set rsSearch = Server.CreateObject("ADODB.Recordset") 'Open the recordset If Request("rdFilter") = 0 Then rsSearch.Open sqlSearchExact, cnSearch Else rsSearch.Open sqlSearchFuzzy, cnSearch End If If rsSearch.EOF then%> <% Response.End End If '=================================================================================== 'Create the Crystal Reports Objects '=================================================================================== 'This section of code could be saved in a separate page and re-used 'as an include file. 'Assign the variable 'ReportName' to the name of the Crystal Reports report file. reportname = "[reportname].rpt" 'Set the path to where the report resides. strPath = Server.MapPath("[name of this, Crystal-side ASP page].asp") 'Only Create the Crystal Report application object if it does not already 'exist. If Not IsObject (session("oApp")) Then Set session("oApp") = Server.CreateObject("CrystalRuntime.Application") End If 'Again only create the report object if it does not exist If IsObject(session("oRpt")) then Set session("oRpt") = nothing End if 'On error resume next 'Open the report based on the report object and the path where the report lives. 'This is not the virtual path. It is the actual path where report lives on the server. Set session("oRpt") = session("oApp").OpenReport(strpath & reportname, 1) 'If we could not create the Report object, then there is no sense in continuing. 'Shut down and tell the user what happened. This is important to do, since every 'open Crystal session counts as one license in use. If a bunch of bad sessions 'are left hanging open, you could use up all your runtime licenses. If Err.Number <> 0 Then Response.Write "Error Occurred creating Report Object: " & Err.Description Set Session("oRpt") = nothing Set Session("oApp") = nothing Session.Abandon Response.End End If 'Disable the extended error messaging for live report, messages will only 'confuse users. Set these variables to True for testing/debug purposes. session("oRpt").MorePrintEngineErrorMessages = False session("oRpt").EnableParameterPrompting = False session("oRpt").DiscardSavedData 'Push the recordset to the report. session("oRpt").Database.SetDataSource rsSearch, 3, 1 '==================================================================================== ' This next block of code will retrieve the records and create ' the "Page on Demand" engine object '==================================================================================== On Error Resume Next session("oRpt").ReadRecords 'Again, if there's a problem, abandon the session to 'free up runtime licenses. If Err.Number <> 0 Then Response.Write "Error Occurred Reading Records: " & Err.Description Set Session("oRpt") = nothing Set Session("oApp") = nothing Session.Abandon Response.End Else If IsObject(session("oPageEngine")) Then set session("oPageEngine") = nothing End If set session("oPageEngine") = session("oRpt").PageEngine End If 'Now that all of the data has been sent to the Crystal Report, 'determine which Seagate SmartViewer to use to display the 'report to the user. If browser is IE, then provide 'SmartViewerActiveX.asp. For Netscape, provide SmartViewerJava. 'Get browser info. strBrowserType = Request.ServerVariables("HTTP_USER_AGENT") 'Response.Write strBrowserType If instr(strBrowserType, "MSIE") then %> <% else %> <% end if %> <% 'Turn off normal error checking ' On Error Resume Next %>