Crystal 8.5 DLL problems

I have read through many threads but am still having a tough time finding a particular answer. I need to connect to a Sql server and print out some reports in PDF.


Dim appl As CRAXDRT.Application
Dim rep As CRAXDRT.Report
Dim crxTable As CRAXDDRT.DatabaseTable
Set appl = New CRAXDRT.Application
Set rep = appl.OpenReport(ReportPath & rptName)


For Each crxTable In rep.Database.Tables

crxTable.SetLogOnInfo Server, DB, UserID, PWord

Next ' loops through fine - no prob

' this is where is crashes
Call rep.Database.LogOnServer("p2sodbc.dll", Server, DB, UserID, PWord)
rep.RecordSelectionFormula = (RptSelectFormula) 'added
rep.ExportOptions.DiskFileName = fileName
rep.ExportOptions.DestinationType = crEDTDiskFile '1

Select Case FileType

Case ".doc"
rep.ExportOptions.FormatType = crEFTWordForWindows
Case ".xls"
rep.ExportOptions.FormatType = crEFTExcel50
Case ".csv"
rep.ExportOptions.FormatType = crEFTCharSeparatedValues
Case ".txt"
rep.ExportOptions.FormatType = crEFTText
Case ".pdf"
rep.ExportOptions.FormatType = crEFTPortableDocFormat
End Select

rep.Export False


I have p2sodbc.dll on my dev box and it still crashes there with "Server has not yet been opened"

I think its a .dll issue as the code seems to work fine. Any ideas??

Why are you using a CALL statement, you don't need it. The proper syntax is:

report.logonServer dll, server, db, user, pw.

And, don't even use this method. Create your report off of the active Data driver (ADO). Then, open a standard ADO connection object. You need to keep the setLogonInfo command, and this syntax looks correct.

The parenthesis was why I had to use the call, which like you said is not necessary.

With ADO, I assume your referring to http://www.visualbasicforum.com/showthread.php?t=140256&highlight=p2sodbc.dll
#8 post that you posted before.
What would I need for a SQL server?

Also I'm calling alot of my reports with stored procedres using multiple tables.
Should I just call the stored procedure in this line from that post


rs.Open "SELECT * FROM myTable", cn, adOpenKeyset, adLockoptimistic, adcmdtext


Thanks for you help!

Ok, are you using this stored procedure to pass in parameters to the report? I don't see anywhere in your initial code piece where you are using stored procedure values (such as "@mkoslof") to populate parameter fields. So, how are you using your stored procedure for this report?

I took out the parameter field initially b/c that wasn't my original question.

crptFormula = "{spTest;1.ID}"
Parameter = @Testing
are being passed in from another function.



If Not Parameter = "" Then
rep.ParameterFields(0) = Parameter
End If
rep.RecordSelectionFormula = crptFormula


So would I just call the SP in the recordset.open call instead of putting it in the RecordSelectionFormula?

Yes, you could do it that way.

I will give it a shot. Thanks for the help!

No problem, good luck. Remember, treat this like any ADO database connection. I assume you run this stored procedure from your VB application. Just do the same steps..create your ADODB Connection and then open it. Build your recordset normally. Then, use the SetLogoInfo call to loop all tables with the CRAXDRT Report object. Loop all parameter fields and assign them the corresponding value you need. Use Set DataSource to bind the report to to the new data source.

One last question for now. What would be the easiest way to call the stored procedure and put it into the RS b/c I can't seem to just open it like so


RS.Open "{spTesting}"

Everything seems to work until it calls rep.Export false. At this point it gives me the error: Server has not yet been opened. Any ideas?



Dim appl As CRAXDRT.Application
Dim rep As CRAXDRT.Report
Dim cmd As ADODB.Command
Dim curRS As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim crxTable As CRAXDDRT.DatabaseTable

Set cn = New ADODB.Connection
cn.Provider = strDBProvider
cn.ConnectionString = strConnectionString
cn.Open

Set cmd = New ADODB.Command
Set appl = New CRAXDRT.Application
Set rep = appl.OpenReport(Report & rptName)
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spTesting"

Set curRS = cmd.Execute


For Each crxTable In rep.Database.Tables

crxTable.SetLogOnInfo Server, DB, User, PW

Next

If Not Parameter = "" Then
rep.ParameterFields(0) = Parameter
End If

rep.Database.SetDataSource curRS
rep.RecordSelectionFormula = (RptSelectFormula)
rep.ExportOptions.DiskFileName = fileName
rep.ExportOptions.DestinationType = crEDTDiskFile '1

rep.ExportOptions.FormatType = crEFTCharSeparatedValues

End Select

rep.Export False
curRS.Close
cn.Close

OK, sorry I have been busy, work related stuff. The reason the code dies there is because this is the first time you actually instantiate the report. If you had a line of code above this such as rep.PrintOut it would die there first. Or, if you were using using the CRViewer, when you got to the viewReport line it would die there.

Hmm...stored procedures can be tricky, try this:



'modify your connection code like so
'all othe CRAXDRT object code looks ok
'make sure your initial DIMS are correct

Dim cn as ADODB.connection
Dim rs as ADODB.recordset
Dim cmd as ADODB.Command

Set cn = New adodb.Connection
Set rs = New adodb.Recordset
Set cmd = New adodb.Command

'your connection code, make sure all of this is correct,

cn.Provider = strDBProvider
cn.ConnectionString = strConnectionString
cn.Open

'looks good, keep this line
Set cmd.ActiveConnection = cn

'pass in the dataSource and the proper type parameter

Cmd.CommandText = myDataSource
Cmd.CommandType = adCmdStoredProc

'if you need to add parameters, do it now, such as:

If Not IsMissing(sParam1) Then
Set adoParam1 = New adodb.Parameter
Select Case sParam1Type
Case "DateTime"
adoParam1.Type = adDate
Case "Varchar"
adoParam1.Type = adVarChar
Case "Numeric"
adoParam1.Type = adNumeric
Case Else
End Select
adoParam1.Value = sParam1

cmd.Parameters.Append adoParam1

End If

cmd.CommandTimeout = 600

'fill the recordset by executing the procedure

Set rs = cmd.Execute()

'let's assume the Report object has been opened

crxReport.discardsavedData

'Now set the dataSource
'As a test, if you have only ONE table, try passing it directly like so:

CrxReport.Database.Tables(1).SetDataSource rs, 3