Why won't this flip the ole db database at runtime

[cjlarue]

I am pasting the code below. I build the report on the same server using DB name EIDev. I then want to flip the DB to EITest. All the logon infor is in web.config and has been changed to the new server. When I run any report it continues to pull data from the server it was created on in development. The only way I have been able to fix it is to change the datasource in the designer to the test server. Can someone look at this code and tell me what I am doing wrong?

Sub PrintCrystalReport
Dim ErrorFound As Boolean = False
Dim PathReport As String
Dim crRepDoc As New ReportDocument
Dim crSubRepDoc As New ReportDocument
Dim crSections As Sections
Dim crSection As Section
Dim crRepObjs As ReportObjects
Dim crRepObj As ReportObject
Dim crSubRepObj As SubreportObject

Dim crParamFieldDef As ParameterFieldDefinition
Dim crParamFieldDefs As ParameterFieldDefinitions
Dim crParamValues As New ParameterValues()
Dim crParamDiscreteValue As New ParameterDiscreteValue()

Dim crExportOptions As ExportOptions
Dim crDiskFileDestinationOptions As DiskFileDestinationOptions
Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crLogOnInfo As TableLogOnInfo
Dim crConnInfo As New ConnectionInfo()
Dim Temp As String
Dim FName As String
dim i
dim j
dim m
Dim PathReportName As String
dim TempReportPath As String
Dim crReportDoc As ReportDocument
Dim crDiskFileDestOptions As DiskFileDestinationOptions
Dim SessionID As String

CrystalErrorMessage = ""
PathReport = CrystalReportPath & CrystalReportName
crRepDoc = New ReportDocument()

If MyContext.Response.IsClientConnected = False then
TerminateCrystalReport()
End If

Try
crRepDoc.Load(PathReport)
'Throw New OutOfLicenseException("testing out of license exception", EngineExceptionErrorID.OutOfLicense)
Catch engEx As OutOfLicenseException
CrystalErrorMessage = "Currently all of the available Crystal Reports Licenses are in use. Please wait a moment and try again."
WriteToReportLog(Crystaleventid, CrystalReportName,"Error Creating Report", engEX.Message)
Exit Sub
Catch engEx As EngineException
CrystalErrorMessage = engEx.Message
WriteToReportLog(Crystaleventid, CrystalReportName,"Error Creating Report", engEX.Message)
Exit Sub
End Try

If MyContext.Response.IsClientConnected = False then
TerminateCrystalReport()
End If

crDatabase = crRepDoc.Database
crTables = crDatabase.Tables

For Each crTable In crTables
With crConnInfo
If UseRptServer = True Then
.ServerName = ConfigurationSettings.AppSettings("ReportServerName&quot
.DatabaseName = ConfigurationSettings.AppSettings("ReportDatabaseName&quot
.UserID = ConfigurationSettings.AppSettings("ReportUserID&quot
.Password = ConfigurationSettings.AppSettings("ReportPassWord&quot
Else
.ServerName = ConfigurationSettings.AppSettings("OLEDBServerName&quot
.DatabaseName = ConfigurationSettings.AppSettings("OLEDBDatabaseName&quot
.UserID = ConfigurationSettings.AppSettings("OLEDBUserID&quot
.Password = ConfigurationSettings.AppSettings("OLEDBPassWord&quot
End If
End With
crLogOnInfo = crTable.LogOnInfo
crLogOnInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crLogOnInfo)
Next

crSections = crRepDoc.ReportDefinition.Sections
For Each crSection In crSections
crRepObjs = crSection.ReportObjects
For Each crRepObj In crRepObjs
If crRepObj.Kind = ReportObjectKind.SubreportObject Then
crSubRepObj = CType(crRepObj, SubreportObject)
Try
crSubRepDoc = crSubRepObj.OpenSubreport(crSubRepObj.SubreportName)
Catch engEx As OutOfLicenseException
CrystalErrorMessage = "Currently all of the available Crystal Reports Licenses are in use. Please wait a moment and try again."
WriteToReportLog(CrystalEventID, CrystalReportName, "Error Creating SubReport", engEX.Message)
Exit Sub
Catch engEx As EngineException
CrystalErrorMessage = engEx.Message
WriteToReportLog(CrystalEventID, CrystalReportName, "Error Creating SubReport", engEX.Message)
Exit Sub
End Try
crDatabase = crSubRepDoc.Database
crTables = crDatabase.Tables
For Each crTable In crTables
With crConnInfo
If UseRptServer = True Then
.ServerName = ConfigurationSettings.AppSettings("ReportServerName&quot
.DatabaseName = ConfigurationSettings.AppSettings("ReportDatabaseName&quot
.UserID = ConfigurationSettings.AppSettings("ReportUserID&quot
.Password = ConfigurationSettings.AppSettings("ReportPassWord&quot
Else
.ServerName = ConfigurationSettings.AppSettings("OLEDBServerName&quot
.DatabaseName = ConfigurationSettings.AppSettings("OLEDBDatabaseName&quot
.UserID = ConfigurationSettings.AppSettings("OLEDBUserID&quot
.Password = ConfigurationSettings.AppSettings("OLEDBPassWord&quot
End If
End With
crLogOnInfo = crTable.LogOnInfo
crLogOnInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crLogOnInfo)
Next
End If
Next
Next

i = 0
crParamFieldDefs = crRepDoc.DataDefinition.ParameterFields
For Each crParamFieldDef In crParamFieldDefs
If crParamFieldDef.ParameterType = ParameterType.StoreProcedureParameter And _
crParamFieldDef.IsLinked = False Then
i = i + 1
If UBound(CrystalParms) >= i Then
Temp = CrystalParms(i)
End If

Select Case crParamFieldDef.ValueType
Case FieldValueType.NumberField
If Trim(Temp) = "" Then
Temp = 0
End If
crParamDiscreteValue.Value = Convert.ToInt32(Temp)
crParamValues = crParamFieldDef.CurrentValues
crParamValues.Add(crParamDiscreteValue)
crParamFieldDef.ApplyCurrentValues(crParamValues)

Case FieldValueType.DateField, FieldValueType.DateTimeField
Temp = Trim(Temp)
If InStr(Temp, &quot;/&quot < 4 Then
Dim TempDate
Dim strTemp
TempDate = Left(Temp, InStr(Temp, &quot; &quot)
If Len(CStr(Month(TempDate))) < 2 Then
Temp = CStr(Year(TempDate)) & &quot;/0&quot; & CStr(Month(TempDate))
Else
Temp = CStr(Year(TempDate)) & &quot;/&quot; & CStr(Month(TempDate))
End If
If Len(CStr(Day(TempDate))) < 2 Then
Temp = Temp & &quot;/0&quot; & CStr(Day(TempDate))
Else
Temp = Temp & &quot;/&quot; & CStr(Day(TempDate))
End If

'add time if type DateTime, otherwise leave as just date
If crParamFieldDef.ValueType = FieldValueType.DateTimeField Then
Temp = Temp & &quot; 00:00:00.000&quot;
End If
End If

Temp = Mid(Temp, 6, 2) & &quot;/&quot; & _
Mid(Temp, 9, 2) & &quot;/&quot; & _
Left(Temp, 4)

crParamDiscreteValue.Value = CDate(Temp)
crParamValues = crParamFieldDef.CurrentValues
crParamValues.Add(crParamDiscreteValue)
crParamFieldDef.ApplyCurrentValues(crParamValues)

Case Else
crParamDiscreteValue.Value = CStr(Temp)
crParamValues = crParamFieldDef.CurrentValues
crParamValues.Add(crParamDiscreteValue)
crParamFieldDef.ApplyCurrentValues(crParamValues)
End Select
Else
'Item.SetCurrentValue Item.DefaultValue,Item.ValueType
End If
Next

TempReportPath = ConfigurationSettings.AppSettings(&quot;TempReportPath&quot

If RptViewerType = &quot;WORD&quot; Then
FName = TempReportPath & CrystalEventID & &quot;.doc&quot;
Else 'default to Adobe Acrobat Reader
FName = TempReportPath & CrystalEventID & &quot;.pdf&quot;
End If

crDiskFileDestOptions = New DiskFileDestinationOptions()
crDiskFileDestOptions.DiskFileName = FName
crExportOptions = crRepDoc.ExportOptions
With crExportOptions
.DestinationOptions = crDiskFileDestOptions
.ExportDestinationType = ExportDestinationType.DiskFile
If RptViewerType = &quot;WORD&quot; Then
.ExportFormatType = ExportFormatType.WordForWindows
Else 'default to Adobe Acrobat Reader
.ExportFormatType = ExportFormatType.PortableDocFormat
End If

End With

If myContext.Response.IsClientConnected = False Then
TerminateCrystalReport()
End If

Try
crRepDoc.Export()

Catch engEx As OutOfLicenseException
CrystalErrorMessage = &quot;Currently all of the available Crystal Reports Licenses are in use. Please wait a moment and try again.&quot;
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error During Export&quot;, engEX.Message)
Exit Sub
Catch engEx As EngineException
CrystalErrorMessage = engEx.Message
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error During Export&quot;, engEX.Message)
Exit Sub
Catch e As Exception
CrystalErrorMessage = e.Message
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error During Export&quot;, e.Message)
Exit Sub
End Try

If myContext.Response.IsClientConnected = False Then
TerminateCrystalReport()
End If

crRepDoc.Close()
crSubRepDoc.Close()
crRepDoc = Nothing
crSubRepDoc = Nothing

myContext.Response.ClearContent()
myContext.Response.ClearHeaders()
If RptViewerType = &quot;WORD&quot; Then
myContext.Response.ContentType = &quot;application/msword&quot;
Else 'default to Adobe Acrobat Reader
myContext.Response.ContentType = &quot;application/pdf&quot;
End If


Try
myContext.Response.WriteFile(FName)
Catch e As System.IO.FileNotFoundException
ErrorFound = True
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error writing temp file to browser&quot;, e.message)
Catch e As System.IO.IOException
ErrorFound = True
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error writing temp file&quot;, e.message)
Catch e As Exception
ErrorFound = True
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error writing temp file&quot;, e.message)
End Try

myContext.Response.Flush()
myContext.Response.Close()

Try
System.IO.File.Delete(FName)
Catch e As System.IO.IOException
ErrorFound = True
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error deleting temp file&quot;, e.message)
Catch e As Exception
ErrorFound = True
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error deleting temp file&quot;, e.message)

End Try

If ErrorFound = False Then
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Successful Report Processing&quot;, &quot;No Error&quot
End If

CrystalErrorMessage = &quot;&quot;
End Sub

Sub TerminateCrystalReport()
WriteToReportLog(CrystalEventID, CrystalReportName, &quot;Error Creating Report&quot;, &quot;Client is no longer connected&quot
myContext.Response.End()
End Sub

Sub WriteToReportLog(ByVal eventID As String, _
ByVal ReportName As String, _
ByVal StatusCode As String, _
ByVal errorCode As String)

Dim eventTime As DateTime = Now()

' Call the stored procedure that will update buReportLog
SQL = &quot;exec dbo.rpInsertReportLog2 &quot;
SQL = SQL & &quot;'&quot; & eventID & &quot;', &quot;
SQL = SQL & &quot;'&quot; & ReportName & &quot;', &quot;
SQL = SQL & &quot;'&quot; & StatusCode & &quot;', &quot;
SQL = SQL & &quot;'&quot; & errorCode & &quot;', &quot;
SQL = SQL & &quot;'&quot; & FixQuotes(myContext.Session(&quot;Staff_Name&quot) & &quot;', &quot;
SQL = SQL & &quot;'&quot; & eventTime & &quot;'&quot;

' Execute the query to update the report log if logging is enabled
If ConfigurationSettings.AppSettings(&quot;EnableReportLog&quot = True Then
oSqlService.RunSql(SQL)
End If

End Sub


Regards
Chuck LaRue
ADRS Computer Services

 

[morriskc]

Chuck,

Do your Crystal reports contain the database name when saved? Check by going to Database & seeing if the server name is displayed.

You may want to try creating the reports against an alias database name. If you have a seperate web server per environment, you will not need to change anything to go from one environment to the next. Your dev web server would only need to point the alias to the dev database. When on test web server, you point the test database, etc.
If you don't have a seperate web server per environment, then only the web server file will need to be changed, not the reports.

 

[cjlarue]

Thanks morrisKc
Yes the report is saving the database name. When I show sql it will show eitest.dbo.procName. I will try the alias. Little control over how the environments DB names get set up so this sound like the best path. I notice that in older reports that have been upgraded from CR 8 and CR 8.5 ODBC connections., I only see the table name and not the DB and owner. Can't find how to build the new reports with OLE DB that do not use the DB and Owner.


Regards
Chuck LaRue
ADRS Computer Services

 

[cjlarue]

Looks like I found the answer as CD if you believe that.

Take a look at.

http://support.crystaldecisions.com/library/kbase/articles/c2011464.asp

Regards
Chuck LaRue
ADRS Computer Services