Visual Basic Crystal Reports - Problem with changing server for stored procedure in Crystal Reports

Author

Problem with changing server for stored procedure in Crystal Reports

Gordan A Ziza

2005-11-12, 7:58 am

Hello all,

We are developing a WinForms application including a number of Crystal
reports with it in VS2005. There are two servers in our network with two
identical databases (one development, one used on a daily basis).

Basically, our problem is that when we create a report using a parametarised
stored procedure from the database on one server it works fine. When we try
to run it agains the other server we get a dialog box with this error
message:

Failed to opean a rowset.
Details: ADO Error Code: 0x
Source: Microsoft OLD DB Provider for SQL Server
Description: Procedure or Function 'Facts_Print' expects parameter '@ser',
which was not supplied.
SQL State: 42000
Native Error:
Failed to open a rowset.
Error in file: C:\DOCUME~1\gordon\.........rpt
Failed to open a rowset.

It is obvious that parameters for the stored procedure (passed from code)
are not getting to the stored procedure but we have no idea why (code we use
to show the report is at the end of the post). For as long as we keep the
server and the database the same everything is ok. The moment we change
either the ConnectionInfo.ServerName or the ConnectionInfo.DatabaseName we
get the error.

We are truly at the loss here since changing ConnectionInfo works perfectly
when a report is connected to a table in the database. Everything stops
working when we create a report based on a stored procedure.

We tried both MSDN and Bussines Objects web sites but could not get any
information.

Any help appreciated.

BR,

GAZ

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
CustomersReport = New CrystalReport1

Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

myConnectionInfo.AllowCustomConnection = True
myConnectionInfo.ServerName = "iapetus"
myConnectionInfo.DatabaseName = "treasury35"
myConnectionInfo.IntegratedSecurity = True

Dim parValue1 As ParameterValues = New ParameterValues()
Dim disValue1 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue1.Value = 1
parValue1.Add(disValue1)

Dim parValue2 As ParameterValues = New ParameterValues()
Dim disValue2 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue2.Value = 1
parValue2.Add(disValue2)

Dim parValue3 As ParameterValues = New ParameterValues()
Dim disValue3 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue3.Value = 2005
parValue3.Add(disValue3)

Dim parValue4 As ParameterValues = New ParameterValues()
Dim disValue4 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue4.Value = 1
parValue4.Add(disValue4)

Dim parValue5 As ParameterValues = New ParameterValues()
Dim disValue5 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue5.Value = 1
parValue5.Add(disValue5)

Me.CustomersReport.DataDefinition.ParameterFields("@ser").ApplyCurrentValues(parValue1)
Me.CustomersReport.DataDefinition.ParameterFields("@facility").ApplyCurrentValues(parValue2)
Me.CustomersReport.DataDefinition.ParameterFields("@year").ApplyCurrentValues(parValue3)
Me.CustomersReport.DataDefinition.ParameterFields("@company").ApplyCurrentValues(parValue4)
Me.CustomersReport.DataDefinition.ParameterFields("@rate").ApplyCurrentValues(parValue5)

SetDBLogonForReport(myConnectionInfo, CustomersReport)

Params()

MsgBox("drugi params")

myCrystalReportViewer.ReportSource = CustomersReport

End Sub

Private 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.ApplyLogOnInfo(myTableLogonInfo)
Next
End Sub

Gordan A Ziza

2005-11-15, 7:57 am

Got it. I had to add the following line of code:

myTable.Location = myConnectionInfo.DatabaseName & ".dbo." &
myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)

between the lines:

myTable.ApplyLogOnInfo(myTableLogonInfo)

myTable.LogOnInfo.ConnectionInfo.ServerName = myConnectionInfo.ServerName

in the SetDBLogonForReport sub (in ref to code in my previous post). Dead
easy. Who would've thought of it?

BR everyone.

GAZ

Alex

2005-11-16, 7:05 pm

Can you post the full code, I have the same problem...
'
Thanks,

Alex

"Gordan A Ziza" <gziza@newsgroup.nospam> wrote in message
news:%23c49cqd6FHA.3648@tk2msftngp13.phx.gbl...
> Got it. I had to add the following line of code:
>
> myTable.Location = myConnectionInfo.DatabaseName & ".dbo." &
> myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)
>
>
> between the lines:
>
> myTable.ApplyLogOnInfo(myTableLogonInfo)
>
> myTable.LogOnInfo.ConnectionInfo.ServerName = myConnectionInfo.ServerName
>
>
> in the SetDBLogonForReport sub (in ref to code in my previous post). Dead
> easy. Who would've thought of it?
>
> BR everyone.
>
> GAZ
>
>
>
>

Gordan A Ziza

2005-11-17, 7:58 am

Here comes the full code. Make note of imports, it won't work without them.
I kept all the code in the form, but you can easiliy change
SetDBLogonForReport and Params to server all your reports. And make note of
'myTable.Location = ' in the SetDBLogonForReport sub. If you miss that one
it will not work whatever you do.

One cannot but to comment on how difficult BusinessObject made all of this.
Strange! Would it not be somewhat easier to have properties
StoredProcedureName, ServerName, DatabaseName and Parameters collection
rather then going through all the code below. And setting datasource for
each table in a report separately (try it on for size with subreports). I
don't know. Maybe Microsoft will improve the SQL ReportsService and we can
all move away from Crystal. It is getting far too tedious to work with them.

Best regards,

GAZ

Imports System.Collections
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

myConnectionInfo.AllowCustomConnection = True
myConnectionInfo.ServerName = "athens"
myConnectionInfo.DatabaseName = "asdtreasury35"
myConnectionInfo.IntegratedSecurity = True
myConnectionInfo.Type = ConnectionInfoType.SQL

Params()

SetDBLogonForReport(myConnectionInfo, crystalreport11)

myCrystalReportViewer.ReportSource = crystalReport11
End Sub

Private 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.ApplyLogOnInfo(myTableLogonInfo)
myTable.Location = myConnectionInfo.DatabaseName & ".dbo." &
myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)
myTable.LogOnInfo.ConnectionInfo.ServerName =
myConnectionInfo.ServerName
Next
End Sub

Private Sub Params()
Dim parValue1 As ParameterValues = New ParameterValues()
Dim disValue1 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue1.Value = 55
parValue1.Add(disValue1)

Dim parValue2 As ParameterValues = New ParameterValues()
Dim disValue2 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue2.Value = 1
parValue2.Add(disValue2)

Dim parValue3 As ParameterValues = New ParameterValues()
Dim disValue3 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue3.Value = 2005
parValue3.Add(disValue3)

Dim parValue4 As ParameterValues = New ParameterValues()
Dim disValue4 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue4.Value = 1
parValue4.Add(disValue4)

Dim parValue5 As ParameterValues = New ParameterValues()
Dim disValue5 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue5.Value = 1
parValue5.Add(disValue5)

Me.crystalReport11.DataDefinition.ParameterFields("@ser").ApplyCurrentValues(parValue1)
Me.crystalReport11.DataDefinition.ParameterFields("@facility").ApplyCurrentValues(parValue2)
Me.crystalReport11.DataDefinition.ParameterFields("@year").ApplyCurrentValues(parValue3)
Me.crystalReport11.DataDefinition.ParameterFields("@company").ApplyCurrentValues(parValue4)
Me.crystalReport11.DataDefinition.ParameterFields("@rate").ApplyCurrentValues(parValue5)
End Sub
End Class

gwarnes

2006-05-07, 2:16 pm

Hi Gordon,

Thanks for that great post! I was having the same problem and a heck of a time tracking it down.

Here is a modified version of your solution that worked for me:

TableLogOnInfo tblLogonInfo = myTable.LogOnInfo; tblLogonInfo.ConnectionInfo.ServerName = dbSvrName;
tblLogonInfo.ConnectionInfo.DatabaseName = dbName;
tblLogonInfo.ConnectionInfo.UserID = "";
tblLogonInfo.ConnectionInfo.Password = "";
myTable.ApplyLogOnInfo(tblLogonInfo);
myTable.Location = dbName + ".dbo." + myTable.Name;

.....and, as you pointed out, the same must be done for each table in each subreport.

-gwarnes

jimship

2007-04-24, 10:53 am

Thanks!!! This worked for me. What a simple fix for a pain in the but problem.

Crystal support (Businiss Objects) was no help after searching their site. Found this answer through MSDN communities. Nice job MS.

Jim

ACalderon

2007-05-03, 1:05 pm

HI, can you help me with the previous step of the parameter sending?

I don't know how to design the report from an stored procedure, I add the crystal report to the solution and the expert prompts for the data, then I add the stored procedure from and ole db connection in the data tab, then I click next and all the buttons excepts the cancell are disabled !!!

I am stocked there, please I need your help urgently

Best regards

Alfonso Calderon

shrikale13

2007-11-20, 6:27 am

hi,

is this same process to apply login info for only main report?
if no then
how to apply login info for subreport?

regards,
shrihari

Sponsored Links