Skip to Content
avatar image
Former Member

How can I modify the SQLQueryString at runtime? (VB6 Application, CR XI)

I have created a report in Crystal Reports XI which uses a stored procedure to retrieve data. The report was created using our test database (AS/400 DB2) as its datasource. In our Visual Basic 6 application we want to be able to run against the production database. In the code a CRAXDRT.Report object is instantiated and the .rpt file is specified in the OpenReport command. I can see that the SQLQueryString property is {CALL "TESTSERV"."ABCLIB"."RPT_ONE_SP"}, where TESTSERV is the test machine, ABCLIB is the library (Default Collection property value in the connection properties which can be modified) and "RPT_ONE_SP" is the stored procedure name. If a change to the SQLQueryString is attempted an error is raised. Even if all connection properties are changed the stored procedure call still defers to what is specified in the call (from the .rpt file). Is there a workaround for this situation?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 17, 2008 at 08:44 PM

    Hello Dan,

    If you're trying to change databases at runtime in your VB6 application using the RDC you shouldn't try to edit the SQL query in the report. It won't work.

    You can change databases at runtime using the ConnectionProperties method. There's a Connection Properties document on the SAP website that explains how to change database connections at runtime.

    Here's the link: [Connection Properties|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00635998-751e-2b10-9cba-f50ee1e4ef81] with the RDC.

    The document was written for CR9, but the ConnectionProperties method is the same for CR 10, CR XI (v11.0), and CR XI R2 (v11.5).

    Sincerely,

    Dan Kelleher

    Add comment
    10|10000 characters needed characters exceeded

    • To achieve the same or similar results in CR 2008 and .NET, you have to create an ADO .NET dataset off of your SQL statement. Then pass that to the report:

      explicitly:

      crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE"))

      implicitly:

      crReportDocument.SetDataSource(dataSet) or crReportDocument.SetDataSource(dataSe.Tables(0))

      Ludek

  • avatar image
    Former Member
    Nov 05, 2008 at 10:17 PM

    Thank you all for your responses. Unfortunately I don't have a solution to my problem. First, to Don, I am not trying to alter the SQL of a stored procedure. I am trying to alter the "CALL ..." of the stored procedure that is part of the .rpt file. I want to do this so that I can dictate from my application which database (production or test, for example) the stored procedure being called is part of. At design time the "CALL ..." statement is saved with the database the report was created/saved with. As you can see in my first post this (catalog/library name) is part of the "CALL...". One possible work around is similar to what someone here suggested (just a different environment). I can create an ADO.Recordset and use it as the data source of the report object. The record set would be created by calling the appropriate stored procedure with an ADO.Command object. I have not had time to try this yet.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Daniel,

      You can't change the info directly in the SQL you have to use the .Location method. This will update the SQL and re-direct from your original server to the new server.

      Please read the info in the link Dan attached to is first reply. It will show you how to do this and it does work.

      Once you set all the logon info then use the .Verify method or Testconnectivity API to confirm it's going to the new Server. Then export the report to RPT format to confirm the new logon info is saved.

      Changing to SQL Server (OLE DB) at Runtime

      'Declare a Connection Info Object

      Dim ConnectionInfo As CRAXDRT.ConnectionProperties

      'Set the Connection Info to Connection Properties of u2018the table object

      Set ConnectionInfo = Report.Database.Tables(1).ConnectionProperties

      'Set the DLL name

      Report.Database.Tables(1).DLLName = u201Ccrdb_ado.dllu201D

      'Clear the ConnectionProperties collection

      ConnectionInfo.DeleteAll

      'Add the OLE DB Provider

      ConnectionInfo.Add u201CProvideru201D, u201CSQLOLEDBu201D

      'Add the physical server name

      ConnectionInfo.Add u201CData Sourceu201D, u201CServer nameu201D

      'Add the database name

      ConnectionInfo.Add u201CInitial Catalogu201D, u201CDatabase nameu201D

      'Add the user name

      ConnectionInfo.Add u201CUser IDu201D, u201CUser nameu201D

      'Add the password

      ConnectionInfo.Add u201CPasswordu201D, u201CPasswordu201D

      'Set the fully qualified table name if different from u2018the original data source

      Report.Database.Tables(1).Location = u201Cdatabase.owner.tablenameu201D

      Thank you

      Don

  • avatar image
    Former Member
    Nov 30, 2008 at 06:25 PM

    I too use the SQLQueryString all the time and I too need to use it like I used to in CR9.

    I have a report that is all set and working properly. However some reports need farily complicated extra things added to the SQL before it runs the SQL.

    In our application we have about 500 reports in total and about 50 of them have extra info needed for the SQL string.

    Why is is this parameter not available any more?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Mark,

      Changing the SQL Query String was never supported by Business Objects / Crystal Decisions. Even though it used to work in earlier versions of Crystal Reports it was never an official part of the product's functionality. With the release of CR XI / XIR2 the ability to edit the SQL Query was finally locked down.

      Please see the other posts in this thread for possible work arounds.

      Sincerely,

      Dan Kelleher