session("oRpt").SQLQueryString

[hodgesp]

I have an ASP page that I constructed that passes values from a form to another ASP page that parces the values into a SQL statement, then passes the SQL statment to a crystal report. The crystal report displays fine within the ActiveX viewer BUT it displays all the records not just the ones selected via SQL. It is as though the Crystal is ignoring my sql query. I had this working but now it seems to ignore my select statments. Here is the code that I use.

<%
ReportName = "Report1.rpt"
%>

<!-- #include Filele="AlwaysRequiredSteps.asp" -->

<%
session("oApp").LogonServer "PDSsql.dll", "Server", "dbname", "uid", "pw"

session("oRpt").SQLQueryString = sqlstring

%>

<!-- #include file = "MoreRequiredSteps.asp" -->
<!-- #include file = "SmartViewerActiveX.asp" -->

 

[LV]

Is your database connection OK, since it's not setting the SQL. Try this:

<%
ReportName = "Report1.rpt" 
%>

<!-- #include Filele="AlwaysRequiredSteps.asp" -->

<%
' comment this out
'session("oApp").LogonServer "PDSsql.dll", "Server", "dbname", "uid", "pw"
   
   set objCRDatabase = session("oRpt").Database
   set objCRTables = objCRDatabase.Tables

   for each objCRTable in objCRTables
     objCRTable.SetLogOnInfo "Server", dbname, uid, "pw"

     ' false - not connected
     'Response.Write objCRTable.TestConnectivity
   next
   session("oRpt").SQLQueryString = sqlstring
   
%>

<!-- #include file = "MoreRequiredSteps.asp" -->
<!-- #include file = "SmartViewerActiveX.asp" -->

 

[hodgesp]

I double checked it and yes, my connection is fiine. Im sure of this because the crystal report returns all the records in the table. I added some record via my data entry page and they were present at the end of the report.

The issue seems to be that the crystal report is not seeing the query string or perhaps the page that calls the crystal reports is not passing the perameters. I have looked up the script required in the 8.5 Crystal reference, an except for some additional error handling, my code is correct. Maybe I need to reconfigure the web site, maybe the IIS has an issue. I just do not see where the problem is. An ASP doesn't really help with debugging much.

Basically, The sql select clause doesn't seem to be getting to the crystal to limit the returned dataset.

It is driving me crazy!

 

[LV]

Make sure that the File...Save Data with Report option is not checked, when you open the report in the Crystal Designer.

 

[hodgesp]

Yes, I checked that. It is an error I have made in the past, however not this time. I double checked it again after your suggestion but I'm not saving the data with the report.
However, I did notice that the Crystal query does have a select statement within it. I assume that the variable "sqlstring" is supposed to overwrite this, maybe it isn't. When I try to remove SQL statement crystal puts it back in.

Possibly this is close to what is causing my issue.

 

[LV]

You can try to export the report from the Viewer and look at its SQL query, to see whether it was overwriteen by the code. Also, you have syntax error:
<!-- #include Filele="AlwaysRequiredSteps.asp" -->
should be
<!-- #include File="AlwaysRequiredSteps.asp" -->
You can try to set the report RecordSelectionFormula, instead of setting the SQLQueryString:

'session("oRpt").SQLQueryString = sqlstring
recordSelection = "{tablename.stringField}='" + stringVariable + "'"
session("oRpt").RecordSelectionFormula = recordSelection

and see if the report reflects it.

 

[hodgesp]

I get errors:

Error in formula <Record_Selection>. '{wippcts2000.Trkgnumber}=2000' This field name is not known.

Or

page cannot be displayed

----------------------------------------------------------
When I restored my original code the report displayed but the same issue returned. The report returns all the records. The query string doesn't seem to be getting overwritten in Crystal and I am having a lot of trouble figuring out why.

-----------------------------------------------------------
Do I need to get new include files for Crystal8.5??
<!-- #include file="AlwaysRequiredSteps.asp" -->
<!-- #include file = "MoreRequiredSteps.asp" -->
<!-- #include file = "SmartViewerActiveX.asp" -->
or upgrade my dll file: PDSsql.dll
-----------------------------------------------------------

 

[LV]

All should work with the setup you curently have. The selection formula should be {wippcts2000.Trkgnumber}=2000 without the single quotes. Try hard coding it:

session("oRpt").RecordSelectionFormula = "{wippcts2000.Trkgnumber}=2000"

If you set this selection formula in the Designer, does it filter the records? Is the wippcts2000.Trkgnumber field in the report database?

 

[hodgesp]

Even when I hardcode the value I get the error:
[red]Error in formula Record_Selection>. '{tblComMain.TrkgNumber}= 9542' This field name is not known.
[/red]
(the single ' is displayed in the error this is my exact Code

TN= Request.Form("TrkgNumber")
recordSelection = "{tblComMain.TrkgNumber}=" & TN 
session("oRpt").RecordSelectionFormula = recordSelection

tblComMain is my tablename
TrkgNumber is an Identity field in the SQL database.

I went into Crystal directly and modified the query to select a specific record and when the report returned it returned only the record in the where clause. The issues still seems to be:

"Why doesn't the query get overwritten in Crystal?"

It just returns what ever is in the query attached to crystal and ignores the objects and varables within the ASP page.

 

[LV]

Before getting to the SqlQuery issue, can you open the report in the Designer, in the Menu: go to Report...Edit Selection Formula...Record... enter {tblComMain.TrkgNumber}= 9542 in there, save it and see if the report displays filtered records or you get the same "field name is not known" error. According to the erorr message, the TrkgNumber field is not participating in the report.

 

[hodgesp]

You were right about the field! I was supposed to be using
{tblComView.TrkgNumber}= 9542 NOT {tblComMain.TrkgNumber}= 9542.

When I modified the field name I got the report page to display. However it Still ignored the criteria.

I still think it has something to do with the fact that I upgraded my Crystal from 7 to 8.5 but I'm not sure about that.

This is the code that allowed me to display the report but the report continues to display ALL the records.

session("oRpt").DiscardSavedData
session("oRpt").RecordSelectionFormula = "{tblComView.TrkgNumber} = " & CStr(Request.Form("TrkgNumber"))

I'm missing something. I just can't see what, as yet.
Why does the crystal report ignore the string??

I went into Crystal and modified the SQL query attached to the report and it Does limit the output according to the where clause within a regular Select statement. However the [red]{tblComView.TrkgNumber}= 9542[/red] doesn't stick. When I place that into the crystal query window Crystal just overwrites it with the default query that returns all the records in my view.

Is there a configuration I need to set or a new version of a DLL??? I'm getting closer but I'm not there yet.



By the Way, [red]Thank You[/red] for helping me on this your Great!

 

[LV]

It's real strange. I've upgraded my 7.0 to 8.5 a while ago and never had any problems running the reports through ASP. Though I had to save each report in a new version. Can you run a test: create a simple report from scratch and then try to run it from ASP, setting SqlQuery and/or SelectionFormula and see if it'll work. I assume all the include files are from v8.5? Also, do you have rptserver.asp in the same directory with SmartViewerActiveX.asp? BTW, post this question in Crystal forum as well: forum149

 

[hodgesp]

Well this is even Stranger! The issue is finally resolved . It had to do with a MoreRequiredSteps.asp file.

For some reason the copy I had didn't seem to function correctly. I mean obviously something worked to allow the report to be generated. But when I garnered another copy of the file from a colleague and used it to replace my current one the report started generating the proper return.

Man when they told me to be aware of how Touchy ASP can be they were not kidding.

Anyway I learned that it may be advantages to replace the #include files when debugging because in this case it seem to resolve this issue.

Again thank you for your help. You will probably see me out here again asking more questions and looking for more answers.

 

[LV]

Glad you solved it, though still strange. In fact, I don't use the <include> tags for those two files, I simply have all code from there on one processing page. This way it's easier to debug and see the sequence of events, no need to jump from one include file to another.