Wrong Datasource

[Former Member]

My Connection inside my Crystal Reports is pointing to sqltst01. As long as my "data source" below is pointing to sqltst01 it works great...If I change the "data source" code below to sqldrk03 it doesn't find data b/c it appears its still trying to look at sqltst01.

Any thoughts why it doesn't chnge the reports data source to sqldrk03?

connectionInfo.Add "Provider", "SQLOLEDB.1;"

connectionInfo.Add "Data Source", "sqltst01" //with sqltst01 it works. change to sqldrk03 and it still appears to be using sqltst01

connectionInfo.Add "UserID", "*********"

connectionInfo.Add "Pwd", "**********"

connectionInfo.Add "Initial Catalog", AH#RDP

• Is "Data Source" what I should be using for connectionInfo.Add "Data Source"? When I use:

connectionInfo.Add "DSN" I get an "invalid call or procedure" on this line....If I change it to connectionInfo.Add "Data SourceXXXX" I don't get an errors it just uses the default data source that is set in the report itself.

Edited by: Kacy Reiss on Sep 23, 2008 10:55 PM

[former_member183750 · Accepted solution]

Appears you are using Crystal Reports assemblies for .NET, This forum is for Legacy Application Development SDKs only. Please post to the .NET Development - Crystal Reports forum:

I would also suggest searching that forum for a possible solution. There is a number of threads there that may apply to your query.

Ludek

[Former Member]

Ludek,

Thanks for the reply...the link you sent is very helpful...wish I had it a few months ago w/ I started the conversion from 8.5 to XI. I've been dissappointed I couldn't find more resources on the conversion....basically everything I've done has been trial and error and scrapping a bunch of code together that I thought may have worked...

I've posted my routine that works for me...hopefully saves somebody else some time and trouble....If anyone sees any suggestions please let me know.

'*******************************************************************************************************

Public Sub printIt(rpt As String, dln As String, printIt As Boolean, selectionFormula As String, numCopies As Integer)

On Error GoTo errorHandler

'GET AN INSTANCE

Dim crxApplication As New CRAXDRT.Application

Dim crxReport As CRAXDRT.Report

Dim connectionInfo As CRAXDRT.ConnectionProperties

Dim crxSubreportObject As SubreportObject

Dim crxSubreport As CRAXDRT.Report

Dim crxSection As CRAXDRT.Section

'INTIALIZE

Dim connected As Boolean

Dim numRecords As Integer

connected = False

'GET REPORT

'MsgBox ReportDir + "\" + rpt

Set crxReport = crxApplication.OpenReport(ReportDir + "\" + rpt)

Set crxSections = crxReport.Sections

'SET CONNECTION

If Len(Trim(selectionFormula)) > 0 Then

Set connectionInfo = crxReport.DataBase.Tables(1).ConnectionProperties

connectionInfo("DSN") = SQLSrvr

connectionInfo("Database") = DataBase

connectionInfo("User ID") = "****************

connectionInfo("Password") = "*****************

connected = True

'CHECK FOR SUBREPORTS TO CONNECT TO

For Each crxSection In crxSections

For Each CRXReportObject In crxSection.ReportObjects

If CRXReportObject.Kind = crSubreportObject Then

Set crxSubreportObject = CRXReportObject

Set crxSubreport = crxSubreportObject.OpenSubreport

Set connectionInfo = crxSubreport.DataBase.Tables(1).ConnectionProperties

connectionInfo("DSN") = SQLSrvr

connectionInfo("Database") = DataBase

connectionInfo("User ID") = "*************

connectionInfo("Password") = "*************

End If

Next

Next

End If

' Use crCrystalSyntaxFormula value

crxReport.FormulaSyntax = 0

'SET SELECTION FORMULA

crxReport.RecordSelectionFormula = selectionFormula

crxReport.DiscardSavedData

CrystalActiveXReportViewer1.ReportSource = crxReport

crxReport.ReadRecords

numRecords = crxReport.PrintingStatus.NumberOfRecordSelected

If numRecords > 0 Then

'TO PRINT REPORT WITHOUT BEING PROMPTED

crxReport.PrintOut False, numCopies

Do While crxReport.PrintingStatus.Progress = crPrintingInProgress

DoEvents

Loop

End If

GoSub cleanUp

Exit Sub

cleanUp:

Unload Me

Set crxReport = Nothing

Set crxSubreport = Nothing

Set crxSubreportObject = Nothing

Set crxApplication = Nothing

selectionFormula = vbNullString

If connected = True Then

connectionInfo.DeleteAll

connected = False

End If

Return

errorHandler:

ErrInd = 1

Msg = "Error in form FrmPrintCR procedure printIt() for:" & vbCr & dln & " - " & rpt & vbCr & "Error code " & Err.Number & " - " & Err.Description

MsgButton = vbOKOnly

MsgTitle = "Crystal Report Printing Error"

GoSub cleanUp

'Exit Sub

End Sub

[Former Member]

I was getting the invalid call or procedure error b/c some reports used

Connection String:...... under properties in the "set datasource location" tab inside crystal. When I created a "Create New Connection" using the same database Crystal automatically updated the properties info replacing "Connection String" with "Data Source Name" which was used by the code. Now everything is working fine. These reports were converted from 8.5 to XI which may explain some of the inconsistencies in the reports.

Here is the vb 6 code that i'm using for anybody else having the same problem.

Dim connectionInfo As CRAXDRT.ConnectionProperties

Set connectionInfo = crxReport.DataBase.Tables(1).ConnectionProperties

connectionInfo("DSN") = SQLSrvr

connectionInfo("Database") = DataBase

connectionInfo("User ID") = "*******"

connectionInfo("Password") = "******"

Thanks to all who replied

[former_member183750]

Kacy, apologies. You are correct (not enough coffee this morning). Since you are moving from CR 8.5 to CR XI, the following may be of help:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00635998-751e-2b10-9cba-f50ee1e4...

The .add is a good way of going too, but you need to ensure that you delete the logon info first. E.G.;

rpt.database.tables(1).connectionproperties.deleteAll

rpt.database.tables(1).connectionproperties.add "DSN", "dsn name"

rpt.database.tables(1).connectionproperties.add "User ID", "user's id"

rpt.database.tables(1).connectionproperties.add "PASSWORD", "whatever"

Above is for ODBC, for OLE DB the parameters change to:

data source = server name

initial catalog = database name

password = password

User ID = userID

Now, in your case this may suffice:

report.database.tables.connectionProperties.item("DSN") = SQLSrvr

report.database.tables.connectionProperties.item("User ID") = "*******"

report.database.tables.connectionProperties.item("database) = DataBase

report.database.tables.connectionProperties.item("password") = "******"

The above may even work for subreports as they should essentially piggy back on the main report connection - as long as the same DataSource and type is needed by the subreports.

Ludek

[former_member208657]

I assume that ConnectionInfo is actually supposed to be ConnectionProperty for the RDC?

Instead of adding them all back why don't you just replace the ones that change. If DataSource changes from sqltst01 to sqltst03 you should do this.

connectionInfo("Data Source") = "sqltst03"

You will also need to update the DatabaseTable.Location because that will sometimes change to a new fully qualified name.