Changing Server at Runtime - crystal reports for vb.net

[Former Member]

While I am developing my application I am using a SQL server 2005 database on my local machine, my crystal report (version that ships with vb.net 2005) accesses 2 stored procedures that are in the database.

When I run this on another PC (at run time) and point at a different server by changing myConnectionInfo.ServerName = "MyNewServer" I get an error "Procedure sp_mystoredproc expects parameter @Client_ID which was not supplied." As you can see in the code below, it is supplied and works perfectly on my development PC.

The report works using:

myConnectionInfo.ServerName = "MyServer"

But not using

myConnectionInfo.ServerName = "MyOtherServer"

Databases on both servers identical. Code posted below - any ideas?

Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

myConnectionInfo.DatabaseName = "myDatabase"

myConnectionInfo.ServerName = "MyNewServer"

myConnectionInfo.IntegratedSecurity = True

Dim d1 As String

Dim d2 As String

d1 = DateTimePicker1.Value.Date.ToString("dd MMMM yyyy")

d2 = DateTimePicker2.Value.Date.ToString("dd MMMM yyyy")

Dim strReportName As String = "C:\myreport.rpt"

Dim cryRpt As New ReportDocument

cryRpt.Load(strReportName)

SetDBLogonForReport(myConnectionInfo, cryRpt) '**SEE BELOW

Dim crParameterFieldDefinitions As ParameterFieldDefinitions

Dim crParameterFieldDefinition As ParameterFieldDefinition

Dim crParameterValues As New ParameterValues

Dim crParameterDiscreteValue As New ParameterDiscreteValue

crParameterValues.Clear()

crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields

crParameterDiscreteValue.Value = "{" & CurrentClientID(CurrentIndex) & "}"

crParameterFieldDefinition = crParameterFieldDefinitions.Item("@Client_ID")

crParameterValues = crParameterFieldDefinition.CurrentValues

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

crParameterDiscreteValue.Value = CurrentBranch(CurrentIndex)

crParameterFieldDefinition = crParameterFieldDefinitions.Item("@Branch_ID")

crParameterValues = crParameterFieldDefinition.CurrentValues

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

crParameterDiscreteValue.Value = d1

crParameterFieldDefinition = crParameterFieldDefinitions.Item("@FromDate")

crParameterValues = crParameterFieldDefinition.CurrentValues

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

crParameterDiscreteValue.Value = d2

crParameterFieldDefinition = crParameterFieldDefinitions.Item("@ToDate")

crParameterValues = crParameterFieldDefinition.CurrentValues

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

crParameterDiscreteValue.Value = CurrentOperatorID

crParameterFieldDefinition = crParameterFieldDefinitions.Item("@User_ID")

crParameterValues = crParameterFieldDefinition.CurrentValues

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

CrystalReportViewer1.ReportSource = cryRpt

'**

Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)

Dim myTables As Tables = myReportDocument.Database.Tables

For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables

Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo

myTableLogonInfo.ConnectionInfo = myConnectionInfo

myTable.Location = myConnectionInfo.DatabaseName & ".dbo." & myTable.Name

myTable.ApplyLogOnInfo(myTableLogonInfo)

Next

End Sub

Edited by: Justine King on Sep 18, 2008 10:07 AM

[Former Member · Accepted solution]

What if you use the ip adress instead of the server name (netbios name)?

[Former Member · Accepted solution]

No difference.

I have now tried removing the stored procs and linking to a table - this works ok and I can change server fine.

As soon as I link to a stored proc, I get the parameter error when my server changes. From googling this is looking like a bug - surely it change have such a fundamental flaw though?

[former_member183750 · Accepted solution]

Also, have a look at this thread:

Ludek

[Former Member · Accepted solution]

Thanks - I'm downloading the update in point 1) above - that is the right version for the crystal reports that ships with .NET 2005?

I'm currently trying a work around by using a temporary table to hold just the data I need - this works OK and I can pass a parameter to my report fine when I switch the database instance, just not when it is connecting to a stored proc it seems.

[former_member183750 · Accepted solution]

Thanks - I'm downloading the update in point 1) above - that is the right version for the crystal reports that ships with .NET 2005?

Good question. No it is not the same version. You are using CR 10.2 that bundled with .NET 2005. I am getting you to download CR XI r2. But that is OK as the two will not interfere on the same box and we will be able to run the report out of your app and see what Crystal reports tells us / how it behaves. We can than make a number of assumptions / conclusions about the app and CR 10.2.

Ludek

[former_member183750]

Three suggestions:

1) Ensure you are working with the latest updates. SP 1 for CR 10.2 is here:

https://smpdl.sap-ag.de/~sapidp/012002523100006007872008E/crvs05sp1.exe

2) The most recent runtime files can be found here;

https://smpdl.sap-ag.de/~sapidp/012002523100005853292008E/cr_net_2005_mm_mlb_x86.zip

make sure this is the runtime on the client system

3) If none of the above helps, for testing purposes, I'd suggest installing CR XI r2 on that client and see if you can change the server in the CR designer. An eval of CR XI r2 can be downloaded from here:

http://www.businessobjects.com/products/reporting/crystalreports/eval.asp

As onerous as point (3) above may sound, it will probably be the fastest way to gather information to help us troubleshoot the issue.

Ludek