Crystal Reports - .SQLQueryString Problem

tdoman
10-19-2004, 09:08 AM
I have a crystal report I'm opening in VB. Before the report opens, I created a form that will allow the user to filter the results based on the criteria they select. I originally wrote an SQL command in the crystal report that returns all the records with no filter. Then once the user selects their filter criteria, I want to change the original SQL command so it will query with the user's filter. I've done it in the past using the Report.RecordSelectionFormula = [filter] method but the problem with this is that the server will return all of the records to the client and then crystal reports will filter the results as the report is loading. This method is way to slow when I'm querying 230,000+ records. So I need a way to modify the SQL command on the fly and have it run the new query on the server before it is sent to the client. So I tried using the Report.SQLQueryString method. Using this method I can see the original SQL command I wrote, but if I try to pass it the exact same "SELECT" and "FROM" string with a modified "WHERE" clause, it will not change it, nor does it give me an error.

For example, if I run...

debug.print Report.SQLQueryString

before the filter is set, it would return something like this...

SELECT * FROM employee

Then after the filter is set, I would pass in a modified query string like so...

Report.SQLQueryString = "SELECT * FROM employee WHERE employee_id ='25'"

then I would print out the query string to see if it was modified....

debug.print Report.SQLQueryString

instead of returning the new query string, it prints the original...

SELECT * FROM employee

MKoslof
10-19-2004, 06:36 PM
Is Employee_Id a numeric field, if so, you sql string should look like so:



"SELECT * FROM Employee WHERE Employee_ID=25"



Remove the single quotes. And instead of using the .sqlquerystring method, to change the data source of a report at runtime, use the Set Datasource method (passing a complete ADO recordset), or the .recordselectionformula/.selectionformula methods (passing a filter string to an existing Crystal recordset)

tdoman
10-20-2004, 12:30 PM
Ok, I tried the setdatasource method where strQuery contains an SQL query with my new selection criteria in the WHERE clause...

rstTemp.Open strQuery, cnn, adOpenStatic, adLockOptimistic
Report.Database.SetDataSource rstTemp
rstTemp.close

Then when the report is trying to load, crystal reports returns the message "This field name is not known". I didn't change any of the fields returned in the original query, so I don't know why it's giving me this message. Also, how am I supposed to know which field it is referring to?

So then I tried using this command instead...

rstTemp.Open strQuery, cnn, adOpenStatic, adLockOptimistic
Report.Database.AddADOCommand cnn, rstTemp.ActiveCommand
rstTemp.close

and this just returns the same criteria that the original query was based off of instead of returning the new results. Am I doing something wrong here?

MKoslof
10-20-2004, 07:00 PM
What is the value of strQuery, what is the recordset you are trying to open? Are you sure the syntax is correct in your sql statement? If you run this in the Access query designer does it return any records?

"SELECT * FROM employee WHERE employee_id =25"

tdoman
10-21-2004, 09:03 AM
I'm positive the sql statement is correct because I copied it and ran it directly on the database and it returned the expected records with no errors.

MKoslof
10-21-2004, 08:15 PM
Review this example...there are tons of others as well if you do a search of the forum :)

http://www.visualbasicforum.com/showthread.php?t=139098

matkimj
10-28-2009, 12:06 AM
Sorry I know this is not a same thread, but I am new with this forum and just want to see if anybody in this thread can help.

I am writing an impact analysis utility using excel VB, and one of the goals is to determine if a particular column is used in a report. I have no problems retrieving tables used, I thought it is probably much easier to read SQLQueryString to handle both.

However with the code below, I can get the RecordSelectionFormula but not the SQLQueryString. The error returned "automation error" with error code -2147189176. I wonder if this has anything to do with the fact that I do not "connect" properly. Note, I tried to connect as well but it still fails. My issue with using the connect statement below is the report uses 4 tables, and "rvs_memb_company" is just one of them. So by doing the below statement, it does not make sense to me that it works to begin with. It did connect however.

Dim crxapp As New CRAXDRT.Application
Dim CrxRep As Report

connectionstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false;Initial Catalog=ARCHP;Data Source=EZCAP1"
CrxRep.Database.AddOLEDBSource connectionstr, "rvs_memb_company"


fileName = "C:\Documents and Settings\manguyen\My Documents\Projects\RRQ Reports\000034 - Paid Report Dual Elig.rpt"

Set CrxRep = crxapp.OpenReport(fileName)

Debug.Print CrxRep.RecordSelectionFormula
Debug.Print CrxRep.SQLQueryString

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum