VB and Crystal Reports problem - Please help

[computergeek]

Hello,

I want to open a Crystal Report in VB without requiring an ODBC DSN connection to be defined for the report. I initially created the Crystal report with an ODBC connection driver and did a Convert Database Driver to use OLE (pdsoledb.dll). (Tried Pdssql.dll did not work very well... lots of errors.) I would like somehow use my existing connection string settings within the VB application to launch the report. (I am using the RDC method within VB for the Crystal Report.) After changing the report from ODBC to OLE it displays a dialog upon execution saying that the "server has not been opened yet"? So... I tried establishing server connection within the code:

Set Report = crxApplication.OpenReport(App.Path & "\Inventory Report.rpt&quot
'Parameter 1 - Inventory Date
Report.ParameterFields(1).AddCurrentValue (CDate(Format(GetSelectedDate, "yyyy/mm/dd 00:00:00&quot))
'Parameter 2 - System
Report.ParameterFields(2).AddCurrentValue ("SYS0" & g_strSystem)

'Establish connection for report using ole
' Report.Database.LogOnServer "pdsoledb.dll", "kcdevsql2k", "PPMS", "ppmstest", "ppmstest"

' Load Page with Crystal Report Viewer Object
Load Form2
Form2.Show


Thanks,
Computergeek

 

[computergeek]

Hi,

Nevermind, figured it out

 

[diwillialms]

I am having a similar problem. How did you get it to work?

 

[computergeek]

Hi,

1. Within my SQL Server 2000 database I created a new login
(Login: ppmstest, psw: ppmstest and granted access to procedures that my Crystal reports access

Note: Say copy of your report before converting...

2. Converted Crystal report to OLE and embedded login/psw. (Database menu, Convert Database Driver to pdsoledb.dll driver... tried using the pdssql.dll driver (sql server direct one) but got errors - this seems to be a Crystal bug. I found some articles on this, but was never able to resolve it so I left driver within Crystal report as the OLE one.) Saved. Then selected Verify Database - should say something like "The database is up to date"

3. Checked to ensure changes took place (see Set Location option...

4. Removed my ODBC DSN

5. Execute in development mode to ensure all ok

6. Modified VB code as follows (uses pdssql driver) - How you can read this ok...(code on clicked event in my application)
----------------------------------------------------
' Report object has been declared within the General Declaration section
If fso.FileExists(App.Path & "\Inventory Report.rpt&quot Then
' Check if user has selected a System to report
If g_strSystem = "All" Or g_strSystem = "" Then
MsgBox "Please select a System Value and try again. Current System: " & g_strSystem, vbExclamation, "Warning"
Else
Set Report = crxApplication.OpenReport(App.Path & "\Inventory Report.rpt&quot

' Next 2 lines has been left for testing purposes
'Report.ParameterFields(1).AddCurrentValue (CDate("2003/01/10 00:00&quot)
'Report.ParameterFields(2).AddCurrentValue ("SYS02&quot
'Parameter 1 - Inventory Date
Report.ParameterFields(1).AddCurrentValue (CDate(Format(GetSelectedDate, "yyyy/mm/dd 00:00:00&quot))
MsgBox ("date parm: " & GetSelectedDate)
'Parameter 2 - System
Report.ParameterFields(2).AddCurrentValue ("SYS0" & g_strSystem)

'Establish connection for report using ole
Report.Database.LogOnServer "p2ssql.dll", "Kcdevsql2k", "PPMS", "ppmstest", "ppmstest"
Report.Database.Tables(1).SetLogOnInfo "Kcdevsql2k", "PPMS", "ppmstest", "ppmstest"

' Check if they want to print or view the report
Dim MyVar
MyVar = MsgBox("Do you wish to just PRINT the report without viewing?", vbYesNo, "Inventory Report&quot
If MyVar = 6 Then
'They selected "YES" - Just Print it (Parms: Prompt user, #copies)
Report.PrintOut False, 1
Else
'T They selected "NO" - Load Page with Crystal Report Viewer Object
Load Form2
Form2.Show
End If
End If

Else
MsgBox "Error encountered while trying to open file Inventory Report.rpt." & vbCrLf & "File Does Not Exist.", vbExclamation, "File Error"
End If
------------------------------

General Declarations:

'****************************************
' Add declarations for Inventory report
'****************************************
Dim crxApplication As New CRAXDRT.Application
Dim crxReport As CRAXDDRT.Report
Public Report As New CRAXDRT.Report
------------------------------------------
Note:
Report.Database.LogOnServer "p2ssql.dll", "Kcdevsql2k", "PPMS", "ppmstest", "ppmstest"

Parms: driver, server, database, login, password


p2ssql is the driver for SQL Server you can use OLE, and there are others available for Sybase, Oracle, DB2 and others...



7. Your install should include:

Crystl32.ocx, mfcans32.dll, olepro32.dll, crpe32.dll, crpaig80.dll, implode.dll, mfc42.dll, msvcirt.dll, msvcp60.dll

Database specific files (these are for SQL Server):
p2ssql.dll, p2lsql.dll We also included the nmplib.dll file (can't remember exact name..)

More info see:

http://support.crystaldecisions.com/library/kbase/articles/c2008905.asp

I hope this helps...

Computergeek

 

[diwillialms]

Thanks! I will give this a try. It looks like I was almost there. Thanks for the fast reply.
Di