Crystal Report over ASP.Net runtime data source change

Question

Got a script to run crystal report over ASP.Net, export and send to email.

If I use the current database without applying logon everything works but if I change datasource (same database structure but different server) at runtime then the issue below.

Crystal 2008 runtime

ERROR:System.Runtime.InteropServices.COMException (0x80042018): The table %1 does not exist in the document. at CrystalDecisions.ReportAppServer.Controllers.DatabaseControllerClass.VerifyTableConnectivity(Object Table) at CrystalDecisions.CrystalReports.Engine.Table.TestConnectivity() at ScriptCodeClass.ApplyLogon(ReportDocument cr, ConnectionInfo ci) at ScriptCodeClass.Logon(ReportDocument cr, String server, String db, String id, String pass) at ScriptCodeClass.FunCreatePDFView(String lsHeader, String lsReportType, String msDatabaseUserId, String msDatabasePassword)

this code can change authentication but not data source/server, wondering if need a reference or to import.

Imports System.Collections
Imports System.Data
Imports T1.Tb.Data
Imports System.IO
Imports System.Net
Imports System.Net.Mail
Imports T1.Tb
Imports T1.TB.Public
Imports CrystalDecisions.CrystalReports.Engine.ReportDocument
Imports CrystalDecisions.ReportSource
Imports System.Configuration
Imports System.Data.SqlClient


Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared


References CrystalDecisions.CrystalReports.Engine
References CrystalDecisions.Shared
References System.Web.Services
References System.Data
References T1.Tb.dll
References T1.TB.Public
References T1.P1.dll
References T1.P1.Public
References T1.Tb.Fun


public shared function Logon(cr as ReportDocument, server as string, db as string, id as string, pass as string)  as Boolean
      'Use this to change the database logon info for a crystal report
      dim ci as ConnectionInfo = new ConnectionInfo()
      dim subObj as SubreportObject


      ci.ServerName = server
      ci.DatabaseName = db
      ci.UserID = id
      ci.Password = pass


      if ApplyLogon(cr, ci) then
        for each obj as ReportObject in cr.ReportDefinition.ReportObjects
           If (obj.Kind = ReportObjectKind.SubreportObject) Then
        //  if typeof obj.Kind.GetType() is CrystalDecisions.Shared.ReportObjectKind then
            subObj = ctype(obj, SubreportObject)
            if  not ApplyLogon(cr.OpenSubreport(subObj.SubreportName), ci) then
               return(false)
            end if
          end if
        next
        Logon = True
      end if
    end function


    private shared function ApplyLogon(cr as ReportDocument, ci as ConnectionInfo ) as Boolean


      dim li as TableLogOnInfo
      dim success as Boolean


      for each tbl as Table in cr.Database.Tables
        li = tbl.LogOnInfo
        li.ConnectionInfo = ci
        tbl.ApplyLogOnInfo(li)
        'check if logon was successful
        'if TestConnectivity returns false, check logon credentials
        if tbl.TestConnectivity() then
          'drop fully qualified table location
          if tbl.Location.IndexOf(".") > 0 then
            tbl.Location = tbl.Location.Substring(tbl.Location.LastIndexOf(".") + 1)
          else
            tbl.Location = tbl.Location     'THIS IS LINE LEFT OUT IN ALL SAMPLES I SAW
          end if
        else
          success = false
          exit for
        end if
        success = True
      next
    end function

Answer 1

try to build the query as string in code first then pass its results to crystal reports to have the report and send it by email.

steps: 1) build a query string. 2) execute that string and fill a datatable inside a dataset with the results 3) use that dataset / datatable to generate the report in crystal reports

Answer 2

You have several choices: Add Key(s) in web.config as follow :

  <add key="ServerName" value=""/> Name Or IP Address
  <add key="DataBaseName" value=""/> Database Name
  <add key="DatabaseUser" value=""/>User Name
  <add key="DatabasePassword" value=""/>Password

and call these keys in your reportviewer on load or on your event as :

    Dim SERVER_NAME As String = ConfigurationManager.AppSettings("ServerName").ToString()
    Dim DATABASE_NAME As String = ConfigurationManager.AppSettings("DataBaseName").ToString()
    Dim DatabaseUser As String = ConfigurationManager.AppSettings("DatabaseUser").ToString()
    Dim DatabasePassword As String = ConfigurationManager.AppSettings("DatabasePassword").ToString()

add your code and login to DB

   CrystalReportViewer.SetDatabaseLogon(DatabaseUser, DatabasePassword, SERVER_NAME, DATABASE_NAME)

then add your datasource:

CrystalReportViewer.SetDataSource

Or you can pass it directly through your viewer as follow:

    CrystalReportViewer.SetDatabaseLogon("sa", "123", "Your_Server", "YourDB")