HELP--Using SELECT statement to pass parameters to report!

[SmileyFace]

Hey guys! Need some help with this...might be simple but I must be overlooking something here coz it doesn't work! I have 2 Crystal reports which I need to pass parameters to from an asp page. Both of them accept the same parameter, so I have a select statement in the Runreport page which picks what selection formula to use for the report according to which report it is. My reports both accept 2 parameters from another asp page....first is the report name and second is the Ordernumber which is called 'Soeno'. Here is the code....

ReportName = Request.QueryString("ReportName&quot & ".rpt"

if Request.QueryString("Debug&quot = "Y" then
Response.Write &quot;ReportName = &quot; & ReportName & &quot;<BR>&quot; & vbcrlf
session(&quot;debug&quot=&quot;Y&quot;
end If

' CREATE THE APPLICATION OBJECT
On error resume next
If Not IsObject(session(&quot;oApp&quot) or session(&quot;oApp&quot is nothing Then
Set session(&quot;oApp&quot = Server.CreateObject(&quot;CrystalRuntime.Application&quot
'session(&quot;oApp&quot.SetMorePrintEngineErrorMessages(True)
End If

' CREATE THE REPORT OBJECT
Path = Request.ServerVariables(&quot;PATH_TRANSLATED&quot
While (Right(Path, 1) <> &quot;\&quot; And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend


'OPEN THE REPORT (but destroy any previous one first)
if session(&quot;debug&quot = &quot;Y&quot; then
Response.Write &quot;exec:Al Path=&quot; & path & &quot;<br>&quot; & vbcrlf
end if

If IsObject(session(&quot;oRpt&quot) then
Set session(&quot;oRpt&quot = nothing
End if

On error resume next
if session(&quot;debug&quot = &quot;Y&quot; then
Response.Write &quot;Report=&quot; & path & reportname & &quot;<br>&quot; & vbcrlf
end if
Set session(&quot;oRpt&quot = session(&quot;oApp&quot.OpenReport(path & reportname, 1)

If Err.Number <> 0 Then
Response.Write &quot;Error Occurred creating Report Object: &quot; & err.number & Err.Description
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
Response.write &quot;<script language=JavaScript>&quot;
Response.write &quot;setTimeout(&quot;&quot;window.close()&quot;&quot;, 1500)&quot;
Response.Write &quot;</script> &quot;
'Session.Abandon
Response.End
End If

session(&quot;oRpt&quot.MorePrintEngineErrorMessages = False
session(&quot;oRpt&quot.EnableParameterPrompting = False
session(&quot;oRpt&quot.DiscardSavedData


OrderNum = Request.QueryString(&quot;soeno&quot
'Building the selection formula...

select case reportname
case &quot;soa.rpt&quot;

SelectionFormula = &quot;{soa_header.soeno}= '&quot; & OrderNum & &quot;'&quot;
session(&quot;oRpt&quot.RecordSelectionFormula = cstr(SelectionFormula)



case &quot;invoice1.rpt&quot;
SelectionFormula = &quot;{t_invoice.sonum}= '&quot; & OrderNum & &quot;'&quot;
session(&quot;oRpt&quot.RecordSelectionFormula = cstr(SelectionFormula)

end select

When I don't use the select statement and just consider one report...like the code shown below, it works fine and accepts the parameter but if I try to use the select statement its as if it doesn't hit that code at all because it will open the reports without passing the selection criteria. Here is the code which works....when I try to open a single report. Any idea why the select statement won't work?

OrderNum = Request.QueryString(&quot;soeno&quot

SelectionFormula = &quot;{soa_header.soeno}= '&quot; & OrderNum & &quot;'&quot;



session(&quot;oRpt&quot.RecordSelectionFormula = cstr(SelectionFormula)


Thanks for any help offered!

 

[synapsevampire]

Why are you using cstr()?

If anything I'd think that you'd want cstr() on the ordernum only, but without knowing data types it's hard to know.

-k

 

[SmileyFace]

But then how come it works when I don't use the Case statement and just try and open a single report? The Ordernumber is of 'nvarchar' type data. I even tried using an If statement and it wouldn't work. When I say it doesn't work I mean it does open the report but doesn't accept the selection criteria so all the records are displayed not just the ones for the order who's ordernumber I pass. If I put a response.write selectionformula it doesn't write it so basically it does not go to the code. But it does write out the selectionformula when I try and open just one report. Please help.....and thanks for replying!

 

[SmileyFace]

Hey Synapsevampire.....I need help!! This isn't making sense! The problem is in this part of the code but I cannot figure out what it is. Even when trying to open just one report if I use an 'If' statement it worn't work....here is what works.....the following code passes an ordernum=soeno to a report called soa.rpt.

ReportName = Request.QueryString(&quot;ReportName&quot & &quot;.rpt&quot;
if Request.QueryString(&quot;Debug&quot = &quot;Y&quot; then
Response.Write &quot;ReportName = &quot; & ReportName & &quot;<BR>&quot; & vbcrlf
session(&quot;debug&quot=&quot;Y&quot;
end If

OrderNum = Request.QueryString(&quot;soeno&quot
SelectionFormula = &quot;{soa_header.soeno}= '&quot; & OrderNum & &quot;'&quot;
session(&quot;oRpt&quot.RecordSelectionFormula = cstr(SelectionFormula)
if Request.QueryString(&quot;Debug&quot = &quot;Y&quot; then
Response.Write &quot;SelectionFormula = &quot; & SelectionFormula & &quot;<BR>&quot; & vbcrlf
end If

Just to test it with an 'If' statement so I could add an else later for use with another report, I tried the following code....basically just added an if-then statement to the same part...and an else end if it doesn't hit the if statement. Here is what it looks like....

ReportName = Request.QueryString(&quot;ReportName&quot & &quot;.rpt&quot;
if Request.QueryString(&quot;Debug&quot = &quot;Y&quot; then
Response.Write &quot;ReportName = &quot; & ReportName & &quot;<BR>&quot; & vbcrlf
session(&quot;debug&quot=&quot;Y&quot;
end If

OrderNum = Request.QueryString(&quot;soeno&quot
If Reportname = &quot;soa.rpt&quot; then

SelectionFormula = &quot;{soa_header.soeno}= '&quot; & OrderNum & &quot;'&quot;
session(&quot;oRpt&quot.RecordSelectionFormula = cstr(SelectionFormula)
else
response.end
end if
if Request.QueryString(&quot;Debug&quot = &quot;Y&quot; then
Response.Write &quot;SelectionFormula = &quot; & SelectionFormula & &quot;<BR>&quot; & vbcrlf
end If

Basically the first code above works fine but the minute I add the 'If' statement it just goes to the else part and ends it. PLEASE HELP! I am getting real confused! I am sure its a problem with the if ReportName = &quot;soa.rpt&quot; then statement but cannot figure out what!! THANKS.

 

[synapsevampire]

To test your theory, reverse the IF to <>.

If that's where it's failing, perhaps there's a naming difference, or the case.

-k