Crystal Report & SQL Server, Basic NewB Question

[JamesM]

I can make reports with access dbs using CR8.

I tried to connect to make a report with SQL Server, I create and test the connection using the report expert, then go on to create the report.

When I try to open the report from vb, I get this error:
'Server has not yet been opened'

Any idea what I have missed?

[crosbj]

I'm not sure if your using the RDC but here is what Crystal's kb has to say:

Run-time error -2147192184: 'Server has not yet been opened' when using OLE DB

The information in the article refers to:
Seagate Crystal Reports 8

Applies to:

Reported version and lower
OLE DB Connection
Connecting through LogonServer to a report using OLE DB
Error: "Server has not yet been opened."


Synopsis

A Microsoft Visual Basic application uses the Report Designer Component as the reporting development tool.

Using the LogOnServer method of the Database object to connect to a report created with an OLE DB connection produces the error:

Run-time error -2147192184(80047288): 'Server has not yet been opened'.


Solution

This error message is a result of using the LogonServer method with OLE DB. This issue has been tracked and given Track ID 15254.

The workaround is to use the SetLogonInfo method from the Tables object rather than the LogonServer method.

'Sample Code for SetLogonInfo:

Report.Database.Tables(1).SetLogOnInfo "server", "database", "UID", "PWD"

Note===========

This issue is not unique to the RDC. If you use the equivalent of the LogonServer method and OLE DB with other Crystal Report Development tools, you will also receive an error.

To successfully connect to OLE DB:

· For Crystal Report Print Engine calls (Crpe32.dll),
use PESetNthTableLogonInfo.

· Crystal Active X Control (Crystl32.ocx), use Logoninfo.

· Crystal Report Automation Server (Cpeaut32.dll), use Setlogoninfo.

· Crystal Visual Component Library (VCL), use Logoninfo.

================



--------------------------------------------------------------------------------

Category:
Development

Subject:
Report Designer Component (RDC)

Topic:
Database Issues

Keywords:
2147192184, OLE DB , SERVER HAS NOT YET BEEN OPENED, LOGONSERVER

[JamesM]

Thanks for the information crosbj. The only thing is, I have no idea where:

Report.Database.Tables(1).SetLogOnInfo "server", "database", "UID", "PWD"

is supposed to go, having just picked up the package this afternoon?

Yes I am using the RDC.

I simply use the report expert to create a connection to the database using ado and ole db, test the connection, then create the most basic report on a single table.

[crosbj]

The RDC is not the best but its better than the vb reporter if you need subreports etc. When you create a report with the RDC you tell it what to use for data source etc. When it is first created it asks you if you would like to have a form for showing or something to this effect. What it does is creates a form with the crystal viewer activeX component and the code to show it. You then refer to that form to set your data source (again) in code. This is the only way that I could get the server not opened error to go away. Below is the syntax from the main form where I get my query criteria it is attached to the 'print' button and I am leaving out the validation for my report criteria here it is.

'show report
            If AllowPrint = True And .rsLaborDailyDetail_Grouping.RecordCount >= 1 Then
               Screen.MousePointer = vbDefault
               frmLaborDailyDetailRpt.Show 1 '<--THIS IS THE CRYSTAL FORM
            End If

You need to go into whatever you called your crystal report form and put in some code like this:

Screen.MousePointer = vbHourglass
    'data source
    Report.Database.SetDataSource _ '<--SET DATA SOURCE AGAIN
    DE1.rsLaborDailyDetail_Grouping, 3
    
     'report title
    Report.ReportTitle = "From: " & frmLaborDailyDetail.cboStartDate & _
                         "  To: " & frmLaborDailyDetail.cboEndDate & _
                         "  For: " & frmLaborDailyDetail.cboArea
    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport
    
    Screen.MousePointer = vbDefault

Most of this code is already generated for you by crystal when it creates the form for the viewer. I had to change the database source again as you can see from above. There are a couple of things that you probably noticed from above:

• I am using the Data Environment Command for my report data source 'DE1.rsLaborDailyDetail_Grouping'. I'm not sure how you would do it for just a database table etc. I use the Data Environment Commands because they work so well for stuff like this.
• You basically get a print preview for the report when you use this method. I almost always use a print preview for my reports because the crystal viewer allows the end user several options for exporting and viewing report data. Also if you open the report form in modal it forces the user to close the form and thus the report connection also. I've had problems with printing reports and not having the connection close. Sometimes this causes problems if the printing is not completely spooled or just trying to refer to the connection in code. I had instances of the app 'closing' but the report connection open still?

These may be unacceptable limitations for you but I didn't go much further than this for what I do so you may find better and easier methods for what you need. Hopefully this will get you somewhat started. Also check out crystal kb I used it quite a bit when I was struggling with this evil add-in!

[crosbj]

The second part of the code on crystal viewer form is attached to the form open event. I forgot to show that.

[JamesM]

ah, I see:

I added the line of code in the form with the crystal viewer activeX component to:

Private Sub Form_Load()
[b]Report.Database.Tables(1).SetLogOnInfo "NUCLEUS_SERVER", "citigym_sqlsvrdb", "sa", ""[/b]
Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault
 
End Sub

And now it works like a charm, thanks dude!

If the RDC is so much trouble, what is the best tool to use to create reports with then?

Also where did you get the info on the error, it looks like a MSDN reference for crystal reports?

[crosbj]

Glad it worked out! You can go to the Crystal Reports Homepage and they have all sorts of stuff there. They have their own knowlege base that is similar to M$ and it works fairly well. I don't have the link but if you type in Crstyal Reports or something you will hit it pretty fast.

[crosbj]

(By the way the RDC is about the best I've found for vb but if you find something better let me know. I have a feeling that .net may have some better reporting more Accessish but I don't know. Good Luck)