problem with Crystal Report and ADO command object [RESOLVED]

Hello;

I have been asked to modify an application as such:

At present, each time the data is accessed, it is done with recordsets only (ie. recset.Open sql....etc). In order to improve performance on our Oracle database, I am changing all the queries and replacing the record sets with parameterized command objects. The following code is the way it was before I changed it:

VB Code:

Set rsQuery = New ADODB.Recordset
 rsQuery.Open ("SELECT a.garage_id, TO_NUMBER(SUBSTR(a.workorder_no,21,10)) workorder_no, a.lessee, a.license, a.status, a.opendate, a.closedate, a.down_open, a.down_close, a.problem, a.total, a.credit, a.collator, a.vin, a.radio, a.vehicle_year, a.make, a.model, a.wo_odometer, a.odometer, a.unit_no, a.vehicle_no, a.suffix, lessee_data_a, a.credit_expiry, b.address1 FROM work_order a, garage_view b WHERE a.garage_id = b.garage_id AND a.workorder_no = '" & selectedWorkorder & "'"), DBConnection
mainReport.Database.SetDataSource rsQuery
 
...

The above is a snippet from a block that prints a Crystal Report, and when the code is as above, it prints just fine. Here is the modififed code:

VB Code:

sql = "SELECT a.garage_id, TO_NUMBER(SUBSTR(a.workorder_no,21,10)) workorder_no, a.lessee, " _
    & "a.license, a.status, a.opendate, a.closedate, a.down_open, a.down_close, a.problem, " _
    & "a.total, a.credit, a.collator, a.vin, a.radio, a.vehicle_year, a.make, a.model, " _
    & "a.wo_odometer, a.odometer, a.unit_no, a.vehicle_no, a.suffix, lessee_data_a, " _
    & "a.credit_expiry, b.address1 FROM work_order a, garage_view b WHERE a.garage_id = " _
    & "b.garage_id AND a.workorder_no=?"
    
    Dim testCommand As New ADODB.Command
    Dim testPara As New ADODB.Parameter
    testCommand.ActiveConnection = DBConnection
    testCommand.CommandType = adCmdText
    
    Set testPara = testCommand.CreateParameter("workorder_no", adVarChar, adParamInput, 50, selectedWorkorder)
    testCommand.Parameters.Append testPara
    
    testCommand.CommandText = sql
    
    
    Set rsQuery = testCommand.Execute
    mainReport.Database.SetDataSource rsQuery
 
....

With the above code, no error is raised whatsoever....the dialog for number of copies to print pops up, the print icon then appears in my system tray briefly after I click OK, AND NOTHING COMES OUT OF THE PRINTER!!

Being new to Crystal Reports, I am wondering if I should know something about using a paramterized command object to open the record set rather than using the recordset.Open method, or perhaps something else I am missing! Any thoughts would be much appreciated

It should not matter to Crystal how the recordset is generated.

The only difference I see is the ActiveConnection.

The Recordset.Open method is using an existing connection while the Command.Execute is opening a new connection.

Try this instead (there is a difference)

Set testCommand.ActiveConnection = DBConnection

Other than that - are you sure the recordset contains records.

Is there a difference where the recordset object is declared, closed, set to nothing etc.?

I have been at it for 4 hours now....I have displayed the contents of each record set (generated each way) and they are identical. I even checked the cursor types and locations - they also are the same (as they should be) ADO is clearly doing what it should be doing...I even passed the wrong data type to the parameter to see if it crashed - it did...I have stepped thru about a hundred times and there is never an error (when the parameter is set properly) and the record set returns the exact row it should. When I try to print, the dialog appears, the print icon shows, - everything you would expect when printing....no error appears...and the printer remains idle. Go back into the code, and open the record set using the open method, it prints right away as it should. I have repeated this all day (argh!!!)....we are using Crystal Reports 7.0 (maybe we should use Active Reports instead!!)

Oops! forgot to mention that I tried the previous suggestions...I have declared the record set in different ways...and tried using 'set' when assigning the active connection to the command object...I even tried passing different parameter constants (adChar, adVarchar, adVariant) - STILL NOTHING!! Thanks for your suggestions, any others would be more than welcome cause I think I have exhausted my imagination.

I created a quick test application and everything works for me, the only difference - I am using Crystal 8.5.

What happens if you preview the report to screen?

OK...now we are making some progress - I can actually see an error being displayed...when the viewer launches, a message box displays "SQL server error"....now I know what you are thinking but I swear, the record set is fine....I displayed each value from each field right before opening the viewer:

VB Code:

For i = 0 To rsQuery.Fields.Count - 1
 
        If IsNull(rsQuery.Fields(i).Value) = False Then
            'j = j + 1
            MsgBox rsQuery.Fields(i).Value
        End If
 
    Next i

So every field (that was not null) displayed from the record set, and just to make sure, I reopened it with the Open method and it displayed in the viewer with the exact same info I saw from the first recordset. For some reason, Crystal does not like the record set I created from the command object. The only difference really is the SQL which has a '?' for the parameter. But that should not make any difference as each way, the end result is a valid recordset - the only difference being, one throws the SQL error. I think this may be a bug with 7.0 (yeah I know I'm grasping a bit, but what else can I do?!) Thanks very much by the way for taking the time to test in your environment.


ps - I also checked the lock type on each record set - they are identical

I have just been on Google and have found two articles (one from Seagate) that admit there are some bugs with the dll P2SSQL in Crystal Reports 7.0 when accessing an Oracle database using a native Oracle driver. It suggests using ODBC, and since that is absolutely out of the question, my boss has agreed to leave it for now, and convert the reports down the road to a different vendor. Just thought I would append that in case there are any other souls out there experiencing the same scenario.

I am able to duplicate your error if I set the CursorLocation property of the Connection object to adUseServer. It needs to be adUseClient.

Crystal 8.5 has the following properties - If they exist in 7.0 give try them out and see if you get more (better) error information.

Application.SetMorePrintEngineErrorMessages
Report.MorePrintEngineErrorMessages

YOU THE MAN!!!! Thanks very much - indeed moving the cursor over to the client side did the trick - thanks for going the distance!