Crystal Reports 8 Issues From SQL 7 To SQL 2008 VB6

1
votes

I am having issues with a report after switching the database from SQL Server 7 to SQL Server 2008. The report ran fine on our old server, it was Server 2000, and again was running SQL Server 7. We had to update the DSN driver to point to the new database. Now when someone runs our report, they receive this error:

enter image description here

I have tried to lower the master database compatibility level to SQL Server 2000(80), I thought maybe it had something to do with the user logins. The database that the report is pulling from is running on compatibility level to SQL Server 2000(80).

I have tried to google the report error, but everything that I have seen doesn't relate to my issue. I have tried to reinstall crystal reports, somethings this help fix any issues that I may have, however this time that didn't work.

I have tried to "fix" up the report as well. In crystal you can fix a report to make sure that it is point to the right database. I didn't fix any of the other reports, and they are working fine.

I have tried testing the query against SQL Management Studio, I am pulling the data that I wanted for the report with no errors. I know the query is working, so it can't be the statement.

I Have downloaded and installed the crystal report runtime 8.5, restarted the machine and reran the report. I still get the same results.

So far that I know of, the issue is only this report. We have other reports in our VB6 project that work just fine. Here is the code that runs the report:



Private Sub cmdPrintPo_Click()



Dim result As Variant



repSinglePo.ReportFileName = ReportDirectory + "\singlepo.rpt"
repSinglePo.Destination = crptToPrinter
repSinglePo.CopiesToPrinter = 1
repSinglePo.Connect = "DSN = clearspan;UID = " + glUserName + ";PWD = " + glPassword + _
                    ";DSQ = " & gsDatabaseName
repSinglePo.SQLQuery = _
    "SELECT" + _
        " PO.PO_Num, PO.Supplier, PO.DateOrdered, PO.DateRequired, PO.Terms, PO.Freight, PO.FOB, " + _
        " POItems.PO_Num, POItems.Quantity, POItems.Description, POItems.Item, POItems.Price, POItems.KeyNum " + _
    " From" + _
        " PO PO," + _
        " POItems POItems" + _
    " Where" + _
        " PO.PO_Num = POItems.PO_Num and PO.PO_Num = " + lblPONum.caption
'Print Original Po and then a copy
result = repSinglePo.PrintReport



repSinglePo.ReportFileName = ReportDirectory + "\po-copy.rpt"
result = repSinglePo.PrintReport



If result = 0 Then
    MsgBox "PO has been printed"



Else
    Select Case CLng(result)
        Case 20520
            DisplayErrorCode ("PrintingAlreadyStarted")


    Case Else
        MsgBox "Error while printing PO. Error code: " + str(result) & vbCrLf & repSinglePo.LastErrorString
End Select

End If End Sub

As you can tell in the picture above, the code bombs on at:

Case Else MsgBox "Error while printing PO. Error code: " + str(result) & vbCrLf & repSinglePo.LastErrorString End Select

I have tried to alter the select statement, thinking maybe there was a difference in the way the two versions perceived it. Nothing I did seemed to mater. Can someone point me in the right direction? Thanks for any help ahead of time.

3
sqlsql-serversql-server-2008vb6crystal-reports
Have you tried updating your DSN drivers (ODBC, OLEDB, etc)? – campagnolo_1
@campagnolo_1 Yes I did do that, we had to update the DSN driver to point to the new database. I will add that in my question. Thank you for your input. – nate
Any permissions issues maybe? Even though that would usually give you a different error code. – campagnolo_1
That's good, that narrows it down a bit. Does any other report have the "clearspan" DSN? – campagnolo_1
Do they all alias the tables? "From PO PO, POItems POItems" , try changing that to "FROM PO, POItems", especially since it doesn't look like you're using the alias later on. – campagnolo_1

3 Answers

0
votes

Crystal 8 rpt files keep the data connection information which some times donot get cleared even when you change the details in run time

Following is suggested: Change your rpt file to point to your new database and You need to refresh and preview the report assuring it is fetching data from new database.

Hope it helps ..

0
votes

Is the intent to join the tables PO and POItem that have the same values in their respective PO_Num fields? I believe the preferred syntax is:

" From" + _
"    PO PO " + _
" inner join " + 
"    POItems POItems " + _
" on " +
"    PO.PO_Num = POItems.PO_Num " +
" Where " + _
"    PO.PO_Num = " + lblPONum.caption
0
votes

After weeks and weeks of working on this issue, I found a work around. So far this has been the only way to solve the issue. I had to open the crystal report view outside of Visual Studio. Then I had to open the report that was casing the issue. After that I ran the app and opened the report in the app. The report would open just fine, and could be printed. Then I would live the app open, and then save the report in the viewer (once again out side of the app, and visual studio). After I did this, we never had an issue out of the report again.

Crystal Report Viewer 8