SQLQueryString and SQL Server from Vb6 using CR9 RDC

[Former Member]

Hi All,

Below is a snippet of my code:

myReportObj.Database.LogOnServer "crdb_odbc.dll", "myODBC-DSN", "myMSSQL-DB", "mySQLServerUserID", "mySQLServerPWD"

myReportObj.SQLQueryString = mySQLString

My Questions:

-

---

Why is Crystal (Vb6 with CR 9.0 RDC) generating the report with all the records in the myMSSQL-DB.table INSTEAD of ONLY the records that are restricted with the WHERE clause in the above (mySQLString).

The records are being pulled from a predefined VIEW on the SQL Server 2005.

The SQL Server 2005 VIEW is made up of two tables.

mySQLString is something like: SELECT name, amount, street FROM myMSSQL-DB.table WHERE name="john".

However, when the report gets generated, I get the records for John and ALSO those for Paul on the report.

MORE INFO:

When I tell the program/report to use MS ACCESS database instead of SQL Server 2005, everything work just fine. The report generates with ONLY records for John like it should.

Where should I begin to look to have this resolved? Doesn't SQLQueryString work with SQL Server 2005?

I have lots of CR Report (VB .DSR) that have always worked fine using MS ACCESS, I am now try to migrate to SQL Server 2005 as datasource (hopefully with as MINIMAL changes as possible), but just have lots of problems here and there!

Thanks All in advance.

[former_member183750 · Accepted solution]

.LogOnServer has been deprecated since version 9 of Crystal Reports. I believe this is documented in the developer help file.

Use the connection properties bag as documented [here|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00635998-751e-2b10-9cba-f50ee1e4ef81].

Also, consult the developer help file for your version of Crystal Reports for more details.

Some basic code snippets:

(ODBC)
report.database.tables(1).connectionProperties("DSN") = "the DSN" 
report.database.tables(1).connectionProperties("user ID") = "the user ID" 
report.database.tables(1).connectionProperties("password") = "the password" 

(if using OLE DB) 
report.database.tables(1).connectionProperties("data source") = "the server name" 
report.database.tables(1).connectionProperties("user ID") = "the user ID" 
report.database.tables(1).connectionProperties("password") = "the password" 

(if using native connection) 
report.database.tables(1).connectionProperties("server:) = "the server name" 
report.database.tables(1).connectionProperties("user ID") = "the user ID" 
report.database.tables(1).connectionProperties("password") = "the password"

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

[Former Member · Accepted solution]

Hi Ludek,

I am using Crystal 9.0 RDC with VB6.

Is my use of ".LogOnServer" to connect to the SQL Server database any reason why one of my reports is returning more recordss than it should. In fact, it seems like the WHERE clause passed as part of the SQL string is ignored.

Are there any alternatives to ".SQLQueryString"? I am using ".SQLQueryString" to pass SQL statements to the database via the crystal form (i.e. formObj.SQLQueryString "mySQLString" )...?

Please provide me some code snippet of another method(s) I can use besides ".SQLQueryString ".

Thanks for your reply.

Best regards,

Felix

[former_member183750 · Accepted solution]

Until you move away from LogOnServer and start using the connecting properties, irrespective of it working with some reports, there is nothing I can do.

Let's continue this once you are using tested and supported way of doing things.

Ludek

[former_member11696]

Moved to Legacy Development Forum

[Former Member]

This is a WAG at best, but have you tried to Verify Database on the report after pointing it to MS SQL?

HTH,

Carl

[Former Member]

Hi Carl,

Yes, I have tried to "Verify Database" and when I do, database is up to date.

Now, like I explained in my original post, the reports are .DSR under Vb6 DLL. So, once generated, I took the .RPT and opened it up in Crystal Reports 9.0.

I did "Verify Database" and when I do, database is up to date.

HOWEVER, when I do: "Show SQL Query...":

the SELECT statement that show does NOT have the WHERE clause!

Also, the ORDER BY shows a different field than the one I originally sent thru "myreport.SQLQueryString"

Thanks in advance.

Best regards,

Felix

[Former Member]

First off, let me say that I have not coded in your environment, so my comments are somewhere between a WAG and an programming-experience-based (i.e., somewhat informed) guess...

Um... I wouldn't really expect updating the SQL string to actually change the report definition. I would simply expect that it would change the results when that instance of the report object that is in the program's memory is actually run. I would imagine that the SQL string is not parsed to determine the select expression or record sorting options in the definition. So when you do the Show SQL, the SQL is being regenerated based on the definition in the report (stored somewhere else besides the SQL string).

Now, if you save the report after verifying the database in CR 9.0, then use the code in the original post to run the report, I'd expect correct results.

On the other hand, maybe the SQL string is regenerated based on the report definition at run time, so changing it would have no effect. (But if that were the case, I wonder why it would even be exposed for modification...) Perhaps there are other parts of the report object that should be used to set sort order and record selection.

As I said at the outset, this is firmly in the "guess" category of responses...

HTH,

Carl