Strange behavior Crystal Reports 7 with SQL2000 Stored P. on VB6

Do you know if the VB application is setting the connection too ?

Hi vasto, and thanks for taking time for my question

Yes, the application sets the connection using a UDL file

Both the UDL file and the connection inside the report use the same DB UID

The strange thing is that it worked like that until last week. After I set the password for that UID, it stopped responding. It's like I get an empty dataset whenever I run the report from the application, thing that doesn't happen when I test the report from the CR designer

thanks again

Did you update the UDL file ?
Did you change the password for this user in SQLServer (if you have user login there) ?

Yes, both.

First thing was set a password for the user login in SQL Server

Then I updated the UDL file (all other processes in the application work perfectly)

Then I re-connected the reporte from CR Designer, using the new connection information (user id and password)

Then ran a test from inside CR Designer, and the data is displayed inside the report in the "Preview" pane

But then, finally, when I run the application and trigger the report, no data comes from SQL, and as a consequence the report is not displayed

thanks

Could you please check the place where the report connection is set. If you are setting the connection inside the vb6 program it should not matter if the report is updated to use the new password or not.
Can you show the code where you are setting the crystal report connection ?

John, I probably need to explain what is my guess and you will be able to decide if it is reasonable. First : I never used UDL in crystal and I am not sure how this can be done. My guess is that the vb6 application is using the UDL file to create ado connection and then the data from this connection is send to crystal reports or the data is retrieved in vb6 and pushed to the report. If the report connection is not set correctly you will receive an error cannot connect (not sure for CR7)

Empty result means that the connection was OK but a parameter is changed. Do you set the parameters inside the vb6 app too ?

The thing I would do first is to check with SQLServer Profiler what exactly was send as a request to the database. If there is no request obviously the connection is not working if there is one then you may check the parameter values.

I guess the fact that english is my second language makes this a bit complicated... I may not have explained myself too properly...

Let me clarify. I am creating the application connection via a UDL file, independently from the report's connection (which I set from inside CR Designer, using the tools provided for doing so)
So, when I call the report, I just call the rpt file and push the parameters. (The report was created based on a Stored Procedure with two parameters)

this is the code in VB6

With frm_report.Crystal_rpt
        .Reset      
        .ReportFileName = App.path & "\" & Esp.rpt

'StoreParam is a two item array sent as parameter from main form to this sub
        If IsArray(StoreParam) Then
            For i = LBound(StoreParam) To UBound(StoreParam)
                .StoredProcParam(i) = StoreParam(i)      

            Next
        End If
        If Printout Then
            .Destination = crptToPrinter
            .CopiesToPrinter = Copies
            .PrintReport
            .ReportFileName = ""
            Exit Sub
        Else
            .Destination = crptToWindow
        End If
        .PrintReport
        .PageZoom 92
        .ReportFileName = ""
    End With

Select allOpen in new window

where Crystl_rpt is a plain CrystalReport control (not a CRViewer).

Regardless of having my VB6 connection created and opened via a UDL file, I guess that this report is using it's own connection.

Now... I know that the Stored Procedure works fine with the two parameters sent (cuz they are two) because if I open the report in Designer, and I go to command "Verify Database" in Database menu, I get a dialog box which requests me to input data for the two parameters, and after doing so I get a preview of the report with the data returned from the Stored Procedure.

The problem is when I run the report with the same two parameters from the main app.

If I could find a step by step tutorial on how to create a report with CR7 on VB6 using a Stored Procedure as Datasource, maybe I could fix this. But I can't find one with the level of detail I'm looking for.

Thanks again

John, did you check what was actually send to the database using SQLServer Profiler. I guess this will answer all your questions?
As a possible reason I can see parameter reordering inside the report. This may happened when you updated the report. For example you have 2 integer parameters PaymentID and CustomerID. When the report was created they were ordered PaymentID , CustomerID. When you updated the report they were reordered alphabetically so the new order is CustomerID, PaymentID . Since both parameters are the same type the report will accept the values without error  but the returned result will be empty. Is this possible scenario ?

Check if the order of the parameters in the report are the same as the order of the parameters inside

You may try to change the code to set parameters:
 
                .StoredProcParam("parameterName1") = StoreParam(0)      
                .StoredProcParam("parameterName2") = StoreParam(1)
Just to make sure the right values are send to the right parameters

Again SQLProfiler will show you what was send - I don't think you have issue with the connection.

Thanks for the tip.

I have never before used SQL Profiler, so I gave it a try... what I found is that, for example, when I run the "Verify database" tool from within CR Designer, I can see the connection from my host to the database... but when I run the application from the same host, and I want to print the report, it never connects...

I am completely lost, don't know what else to check. If it was a problem of reordered parameters, I would see the connection, only problem would be no data would return.. right?

If the connection is not correct you will be not able to see activity in SQLProfiler.

Are you running the report in CR Designer from the same computer where the VB6 app is working ?
How did you create the connection inside the report: OLE DB, ODBC ....  ?

Yes.. it's the same computer
Concerning the connection inside the report, I tried both, ODBC and OLEDB.

Weird thing is (at least I think it is weird) everytime I want to test the connection, it asks me to input the db user login password. Maybe it's not saving the password, and that's why it won't connect...

Yes, it is not saving the password. Can you try Integrated security?  This is the only way the report will be able to connect without setting the password from the VB6 app. Make a backup of the report before to switch to Integrated security.

what is the currently active connection ?

if you are using ODBC file you can add PWD=your password ( you may try it with quotes
 PWD="your password") just to check if the password is the problem

Ok... following your advice, I recreated the report's connection using ODBC driver, using trusted connection (I have a System DSN created in the development computer where I am working).
Placed the fields again inside the report, saved it, but I still can't see the connection in SQL Profiler when I call the report.

Now, could the error be caused by the steps I take when saving the report? I mean... once I save it, do I need to recompile my application, connect the report again to the application somehow? I ask this because I know that, for example, in .Net with newer version of Crystal Reports I need to connect the dataset in which the report is based to the report itself while in Runtime mode. Otherwise the report will never be connected to its datasource.

Is this the same in CR7 with VB6?

I decided to share points with vasto due to his willingness to help and multiple tips he provided which helped me to diagnose the problem and solve it

John , points are reward for the cases when somebody helped you. If you believe I helped you somehow it OK to share if not it is OK to not share :) I am glad that you resolved the problem.

Well... as I said, sure you helped... you directed me with several tips in the right path to find the solution... :) Thanks