none
Calling Crystal Reports With Stored procedure

    Question

  •  How do I connect in crystal reports. I'm using stored procedure in crystal reports. When I try to call my report in vb.net 2005 it was sucessfully loaded in crystal report viewer. However if I change the database name of my report connection (In case I change my database name) I was encounter error, the parameters of my stored procedure in crystal report is invalid...Can you give me some example...

     

    Thanks in advance....

    Friday, August 04, 2006 7:49 AM

All replies

  • Does the database you're changing to have the stored procedure in it with the same parameters as the first database?
    Friday, August 04, 2006 7:53 AM
  • yes sir its the same....

     

    Thanks....

    Friday, August 04, 2006 8:41 AM
  • Could you output full details of the error message you're getting...
    Friday, August 04, 2006 8:43 AM
  • Sir this code is working because it connects on the original database name (see SET OF CODE), Howver on the next set of code I was not able to print my reports and  error message appear Unknown parameter...

    (SET OF CODE 1)

    Dim report As New ReportDocument()

    Dim connection As IConnectionInfo

    Dim serverName1 As String = "dellcti"

    Dim userID As String = "sa"

    Dim password As String = "lbcrgc"

    report.Load("C:\Documents and Settings\lesterbc\Desktop\ICR\Reporting System Module\RSM\RSM\icrrptMasterList.rpt")

    ' Set Database Logon to main report

    For Each connection In report.DataSourceConnections

    Select Case connection.ServerName

    Case serverName1

    connection.SetLogon(userID, password)

    End Select

    Next

    ' Set Database Logon to subreport

    Dim subreport As ReportDocument

    For Each subreport In report.Subreports

    For Each connection In subreport.DataSourceConnections

    Select Case connection.ServerName

    Case serverName1

    connection.SetLogon(userID, password)

    End Select

    Next

    Next 

    Dim parameterField As ParameterField

    parameterField = report.ParameterFields("@pdtmReferenceDate")

    ' If the parameter is from a subreport, specify the subreport name

    ' parameterField = report.ParameterFields("@pdtmReferenceDate", subreportName.rpt)

    ' Add all parameters value here

    parameterField.CurrentValues.AddValue("2006-01-01")

     CrystalReportViewer1.ReportSource = report

     

     

    (SET  OF CODE 2) WITH ERROR

    Dim report As New ReportDocument()

    Dim connection As IConnectionInfo

    Dim serverName1 As String = "dellcti"

    Dim userID As String = "sa"

    Dim password As String = "lbcrgc"

    Dim newDatabase As String = "Lester"

    report.Load("C:\Documents and Settings\lesterbc\Desktop\ICR\Reporting System Module\RSM\RSM\icrrptMasterList.rpt")

    ' Set Database Logon to main report

    For Each connection In report.DataSourceConnections

    Select Case connection.ServerName

    Case serverName1

    connection.SetConnection(serverName1, newDatabase, userID, password)

    End Select

    Next

    ' Set Database Logon to subreport

    Dim subreport As ReportDocument

    For Each subreport In report.Subreports

    For Each connection In subreport.DataSourceConnections

    Select Case connection.ServerName

    Case serverName1

    connection.SetConnection(serverName1, newDatabase, userID, password)

    End Select

    Next

    Next

     

     

     

    Dim parameterField As ParameterField

    parameterField = report.ParameterFields("@pdtmReferenceDate")

    ' If the parameter is from a subreport, specify the subreport name

    ' parameterField = report.ParameterFields("@pdtmReferenceDate", subreportName.rpt)

    ' Add all parameters value here

    parameterField.CurrentValues.AddValue("2006-01-01")

     

    CrystalReportViewer1.ReportSource = report

    This field name is not unknown
    Error in file "C:\Documents and Settings\lesterbc\Desktop\ICR\Reporting System Module\RSM\RSM\icrrptMasterList.rpt"
    Error in formula <CDATEReferenceDate>.
    CDATE(@pdtmReferenceDate)

    The field is unknown

    Friday, August 04, 2006 9:05 AM
  • If you change the name of your database connection, you need to go back and reset your crystal report. For Crystal reports 9, on the menu, go to Database->Database expert and change the datasource. Save your report and try viewing it in VB again.
    Friday, August 11, 2006 8:14 PM
  • I'm was having the same problem with VS2005 Team Suite + bundled Crystal Reports. The problem is easy to reproduce and I found by calling VerifyDatabase() after SetConnection() the error disappeared:

     

    using System.Data.SqlClient;
    using System.Configuration;
    using CrystalDecisions.Shared;
    using CrystalDecisions.ReportSource;
    using CrystalDecisions.CrystalReports.Engine;

    .
    .
    .

    protected void SetDataSource (ReportClass report, string connectionString) {

       SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder (connectionString);

     

       report.DataSourceConnections [0].SetConnection (builder.DataSource, builder.InitialCatalog, builder.IntegratedSecurity);

      foreach (ReportDocument item in report.Subreports)

        item.DataSourceConnections [0].SetConnection (builder.DataSource, builder.InitialCatalog,   builder.IntegratedSecurity);

       report.VerifyDatabase ();

    }

     

    (Sorry the code is in C#, but you get the idea...)

    Friday, July 27, 2007 3:15 PM