SQLQueryString Property

[deedar]

I am using Visual Basic 6, Crystal Reports 10 and SQL Server 2000. In crystal reports, I used Add Command and entered an SQL Query for the report. This query retrieves all transactions of a year.

In visual basic, I created a selection form, in which user enters selection criteria for Post Entry Date, Transaction Number and Journal Code. I initially used Report1.RecordSelectionFormula, but it filters the record on client side and retrieving all year transactions from server takes a lot of time.

I then decided to use Report1.SQLQueryString. To test it, I entered sample transaction range, post entry date range and journal code range in SQL Query and run this query in SQL Server 2000. Then I copied that query to SQLQueryString:

Report1.SQLQueryString = QueryTestedOnSQLServer

But when I watched the contents of Report1.SQLQueryString in Immediate Window, after executing the above statement, it showed the same SQL Query that was originally written in Crystal Reports Add Command.

 

[genomon]

Don't use CR, but if I follow correctly, you may need to refresh the connection to the report between queries to reflect changes, as is often the case with the VB DataReport designer. Just a shot in the dark...
Also VB6 SP6 is good to have.

"Rome did not create a great empire by having meetings, they did it by killing all those who opposed them."

 

[deedar]

I figured out the problem. But still I am unable to solve the problem and the report is being run with Report1.RecordSelectionFormula.

For testing, I created a sample database db1.mdb having 1 table t1 with fields f1,f2 and f3. I created a sample report myrpt.rpt. In Database Expert, I shifted table t1 in Selected Tables list. Then I placed f1,f2 and f3 on the report.

Form containing the Crystal Report Viewer control has the following code:

Dim Application As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report

Private Sub Form_Load()

Screen.MousePointer = vbHourglass
Set Report = Application.OpenReport("C:\test\myrpt.rpt")
Report.SQLQueryString = "Select * From t1 where f1 between '1' and '3'"
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

I have 5 records in table t1. Code executed successfully and report showed 3 records; 1,2, and 3.

Then I opened myrpt.rpt in Crystal Reports. In the Database Expert, I removed table t1 from Selected Tables list. Then I double-clicked Add Command in Current Connections and entered the following SQL Query:

SELECT T1.f1, T1.f2, T1.f3
FROM T1;

Then I placed f1,f2 and f3 on the report.

Now when I executed the VB Code, it returned all 5 records instead of 3 records. Also, in the immediate window, Report.SQLQueryString did not show WHERE clause.

So, SQLQueryString is not working when Add Command option is used in Crystal Reports for entering SQL Query

 

[deedar]

I used ParameterFields as a work-around to this problem. The following code executed successfully:

Dim Application As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report

Private Sub Form_Load()

Screen.MousePointer = vbHourglass
Set Report = Application.OpenReport("C:\test\myrpt.rpt")
Report.ParameterFields(1).AddCurrentValue ("1")
Report.ParameterFields(2).AddCurrentValue ("3")
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

In myrpt.rpt file, Modify Command window has the following SQL query:

SELECT T1.f1, T1.f2, T1.f3
FROM T1
where f1 between '{?b.emp}' and '{?e.emp}';