Skip to Content
0
Former Member
Oct 30, 2013 at 02:47 PM

Updating stored procedure name in a crystal report

363 Views

visual studio 2010 which has the crystal reports

crystal report for .net framework 4.0

I want to change the name of the store procedure its runs. I have 100 to change and don;t want to have to open each one and manually attach it.

The is software you can buy rpt.inspector that does it. But since i can read it in the code i put in I want to be able to update it running a dot .net application.

I created a .net application code that reads though 1000 crystal reports and writes info to data base tables sql server 2008

I get things like table names /store procedure names /number of items in the report

parameters /formulafield /groups really great stuff code below/subreports


what i want to do now is update info in the crystal reports

it would be the stored procedure names to start

ie here is getting the stored procedure name

loadstoredprocedure = CRpt.Database.Tables.Item(reportnameloop).Location.ToString


Imports System.Data

Imports System.Net.NetworkInformation

Imports System.Runtime.InteropServices

Imports System.IO.File

Imports System.Configuration

Imports Registration.Exams

Imports System.Data.SqlClient

Imports System.IO

Imports System.Xml

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Public Class ProcessCRfiles

Public Sub read_file_for_cr_logs()

'open file

Using sr As New StreamReader(filepathtoprocess + filenametoprocess)

Dim reportline As String

'read first line in file

reportline = sr.ReadLine

reportnametoprocess = reportline

Do Until (reportline Is Nothing)

'delete from tables

CrystalReportDelete(reportnametoprocess)

'load name and table name

loadtablenameandtableinfo()

'load rest of tables

loadrestoftables()

'read next report

reportline = sr.ReadLine

'get path and report name

reportnametoprocess = reportline

reportpathtoprocess = filepathtoprocess

Loop

sr.Dispose()

End Using

End Sub

Public Sub Process_one_report()

End Sub

'Private Sub xxx()

' 'The connection properties associated with a report can be retrieved by using the following code in Visual Studio .NET.

' Dim crTable As Table

' Dim crLogOnInfo As TableLogOnInfo

' Dim crConnectionInfo As ConnectionInfo

' Dim i As Integer

' Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument

' crReportDocument.Load("c:\a\reports\zjpm.rpt")

' For Each crTable In crReportDocument.Database.Tables

' crLogOnInfo = crTable.LogOnInfo

' crConnectionInfo = crLogOnInfo.ConnectionInfo

' Dim a, b, c, d, e, f, g, n As String

' a = crTable.LogOnInfo.TableName

' b = crTable.LogOnInfo.ReportName

' c = crTable.Fields(0).TableName

' d = crTable.Name

' e = crTable.Fields(0).Name 'field name

' f = crTable.Fields.Count ' how many paramters

' g = crReportDocument.DataDefinition.ParameterFields.Count

' n = crReportDocument.Subreports.Count

' For i = 0 To crTable.Fields.Count - 1

' Dim z As String

' z = crTable.Fields(i).Name

' MsgBox("fields " & z)

' Next

' Next

'End Sub

Private Sub loadtablenameandtableinfo()

'loads 2 of the tables

Dim CTableLogInfo As CrystalDecisions.Shared.TableLogOnInfo

Dim ConnInfo As CrystalDecisions.Shared.ConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo()

Dim CRpt As New ReportDocument

'Dim pathname As String = "OURPATHNAME"

Dim pathname As String = reportpathtoprocess

Dim filename1 As String

filename1 = filepathtoprocess & reportnametoprocess

' //here crpt is a sample report document

ConnInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE

ConnInfo.ServerName = "OURSEVERNAME"

ConnInfo.DatabaseName = "OUTDBBNAME"

ConnInfo.UserID = "Admin"

ConnInfo.AllowCustomConnection = True

ConnInfo.IntegratedSecurity = True

CRpt.Load(filename1)

Dim reportnameloop As Integer = 0

' if a cr has mutliples reports - called tables we add 1 report for the main report and n records for the other tables see (CDELNationalCounts) as a example

For Each CTable As Table In CRpt.Database.Tables

CTable.LogOnInfo.ConnectionInfo = ConnInfo

CTableLogInfo = CTable.LogOnInfo

CTableLogInfo.ReportName = CRpt.Name

CTableLogInfo.TableName = CTable.Name

CTable.ApplyLogOnInfo(CTableLogInfo)

Dim loadtablename, loadstoredprocedure As String

'add name to table

'CrystalReportsInsert(reportnametoprocess)

Dim fieldcounts, TableServer, TableDataBase As String

'c = CTable.Fields.Count

'b = CTable.Location.ToString 'was

If holdcrystalreportname = reportnametoprocess Then

reportnameloop = reportnameloop + 1

Else

reportnameloop = 0

holdcrystalreportname = reportnametoprocess

'load main report here

CrystalReportsInsert(reportnametoprocess)

End If

'to get table name when more then 1 table

loadtablename = CRpt.Database.Tables.Item(reportnameloop).Name.ToString

loadstoredprocedure = CRpt.Database.Tables.Item(reportnameloop).Location.ToString

fieldcounts = CTable.Fields.Count

TableServer = CTable.LogOnInfo.ConnectionInfo.ServerName.ToString

TableDataBase = CTable.LogOnInfo.ConnectionInfo.DatabaseName.ToString

'insert table info

Dim filecreatetime As String = System.IO.File.GetCreationTime(filename1)

Dim filemodifytime As String = System.IO.File.GetLastWriteTime(filename1)

CrystalReportsTableInsert(reportnametoprocess, loadtablename, loadstoredprocedure, fieldcounts, filecreatetime, filemodifytime, TableServer, TableDataBase)

Next

CRpt.Close()

End Sub

Private Sub loadrestoftables()

Dim i As Integer

Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument

Dim filename1 As String

'strcrname = "printperson.rpt"

filename1 = filepathtoprocess & reportnametoprocess

crReportDocument.Load(filename1)

' load parameterfields

For i = 0 To crReportDocument.DataDefinition.ParameterFields.Count - 1

Dim a As String

a = crReportDocument.DataDefinition.ParameterFields.Item(i).Name

'ONLY LOAD main report paramters since suBreport parameters will be under subreport name

If crReportDocument.DataDefinition.ParameterFields.Item(i).ReportName = "" Then

CrystalReportsParameterInsert(reportnametoprocess, a)

End If

Next

' get group names

For i = 0 To crReportDocument.DataDefinition.GroupNameFields.Count - 1 'these 2

Dim GroupName, GroupFieldname As String

Dim GroupUseCount As Integer

'get fields to insert

GroupFieldname = crReportDocument.DataDefinition.GroupNameFields.Item(i).Group.ConditionField.Name.ToString

GroupName = crReportDocument.DataDefinition.GroupNameFields.Item(i).GroupNameFieldName

GroupUseCount = crReportDocument.DataDefinition.GroupNameFields.Item(i).Group.ConditionField.UseCount

'insert record

CrystalReportsGroupNameInsert(reportnametoprocess, GroupName, GroupFieldname, GroupUseCount)

Next

'build formulafield table start

' if we have one tbale or more do it

If crReportDocument.DataDefinition.FormulaFields.Count > 0 Then

Dim i1 As Integer = 0

For i1 = 0 To crReportDocument.DataDefinition.FormulaFields.Count - 1

Dim FormulafieldName, FormulaName, FormulaText As String

Dim FieldUseCount As Integer

'get name

FormulaName = crReportDocument.DataDefinition.FormulaFields.Item(i1).Name.ToString

'get times field is used on report

FieldUseCount = crReportDocument.DataDefinition.FormulaFields.Item(i1).UseCount

'if text field is nothing put spaces in it

If crReportDocument.DataDefinition.FormulaFields.Item(i1).Text <> Nothing Then

FormulaText = crReportDocument.DataDefinition.FormulaFields.Item(i1).Text.ToString

Else

FormulaText = ""

End If

FormulafieldName = crReportDocument.DataDefinition.FormulaFields.Item(i1).FormulaName.ToString

FieldUseCount = crReportDocument.DataDefinition.FormulaFields.Item(i1).UseCount

'inser record

CrystalReportsFormulaFieldInsert(reportnametoprocess, FormulafieldName, FormulaName, FormulaText, FieldUseCount)

Next

End If

'build field table

For i = 0 To crReportDocument.Database.Tables.Count - 1

Dim i1 As Integer = 0

' since you can have more then one table loop

For i1 = 0 To crReportDocument.Database.Tables.Item(i).Fields.Count - 1

Dim TableFieldName, TableName As String

Dim TableFieldUseCount As Integer

'get fields to write

TableFieldName = crReportDocument.Database.Tables.Item(i).Fields(i1).Name

TableName = crReportDocument.Database.Tables.Item(i).Fields(i1).TableName

TableFieldUseCount = crReportDocument.Database.Tables.Item(i).Fields(i1).UseCount

'wrtie table field file

CrystalReportsTableFieldInsert(reportnametoprocess, TableName, TableFieldName, TableFieldUseCount)

'wrtie table field file

'CrystalReportsTableFieldInsert(reportnametoprocess, TableName, TableFieldName, TableFieldUseCount, "a", "B")

Next

Next

'load sub rereport

For i = 0 To crReportDocument.Subreports.Count - 1

Dim subreportname As String

'get subreport name

subreportname = crReportDocument.Subreports.Item(i).Name.ToString

'inser table

CrystalReportsSubReportInsert(reportnametoprocess, subreportname)

Next

crReportDocument.Close()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

'Me.Close()

End Sub

Private Sub btnExit_Click(sender As System.Object, e As System.EventArgs) Handles btnExit.Click

Me.Close()

End Sub

Private Sub select_one_report(sender As System.Object, e As System.EventArgs) Handles btnSelectOneReport.Click

Dim myStream As Stream = Nothing

Dim openFileDialog1 As New OpenFileDialog()

holdcrystalreportname = ""

openFileDialog1.InitialDirectory = "c:\"

openFileDialog1.Filter = "Rpt files (*.rpt)|*.rpt" ' |All files (*.*)|*.*"

openFileDialog1.FilterIndex = 2

openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Try

myStream = openFileDialog1.OpenFile()

If (myStream IsNot Nothing) Then

Dim a As String

a = DirectCast(myStream, System.IO.FileStream).Name

Select Case MessageBox.Show("Do you want to Prcoess the " & a & " report?", _

"Crystal Report Load", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

Case DialogResult.Yes

'set to nothing

reportnametoprocess = Nothing

Dim namelength As Integer

Dim fieldtofind As String = "\"

namelength = InStrRev(DirectCast(myStream, System.IO.FileStream).Name, fieldtofind, -1, -1)

Dim getfieldlenght As Integer

getfieldlenght = DirectCast(myStream, System.IO.FileStream).Name.Length

'get file name string

reportnametoprocess = Microsoft.VisualBasic.Right(DirectCast(myStream, System.IO.FileStream).Name, (getfieldlenght - namelength))

'get path

filepathtoprocess = Microsoft.VisualBasic.Left(DirectCast(myStream, System.IO.FileStream).Name, namelength)

'delete from tables

CrystalReportDelete(reportnametoprocess)

'load first 2 tables

loadtablenameandtableinfo()

'load rest

loadrestoftables()

'exit

MessageBox.Show("Single Report Completed", _

"Crystal Report Load", MessageBoxButtons.OK, MessageBoxIcon.Information)

Case DialogResult.No

'clear and reset

' the process won't get run

Exit Sub

End Select

' Insert code to read the stream here.

End If

Catch Ex As Exception

MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)

Finally

' Check this again, since we need to make sure we didn't throw an exception on open.

If (myStream IsNot Nothing) Then

myStream.Close()

End If

End Try

End If

End Sub

Private Sub brnSelectFiletoProcess_Click(sender As System.Object, e As System.EventArgs) Handles brnSelectFiletoProcess.Click

Dim myStream As Stream = Nothing

Dim openFileDialog1 As New OpenFileDialog()

holdcrystalreportname = ""

openFileDialog1.InitialDirectory = "c:\"

openFileDialog1.Filter = "txt files (*.txt)|*.txt" ' |All files (*.*)|*.*"

openFileDialog1.FilterIndex = 2

openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Try

'declare string

myStream = openFileDialog1.OpenFile()

If (myStream IsNot Nothing) Then

Dim a As String

'get name selected

a = DirectCast(myStream, System.IO.FileStream).Name

Select Case MessageBox.Show("Do you want to Prcoess the batch " & a & " file?", _

"Crystal Report Load", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

Case DialogResult.Yes

filenametoprocess = Nothing

reportnametoprocess = a

Dim namelength As Integer

Dim fieldtofind As String = "\"

namelength = InStrRev(DirectCast(myStream, System.IO.FileStream).Name, fieldtofind, -1, -1)

Dim getfieldlenght As Integer

getfieldlenght = DirectCast(myStream, System.IO.FileStream).Name.Length

'get file name string

filenametoprocess = Microsoft.VisualBasic.Right(DirectCast(myStream, System.IO.FileStream).Name, (getfieldlenght - namelength))

filepathtoprocess = Microsoft.VisualBasic.Left(DirectCast(myStream, System.IO.FileStream).Name, namelength)

read_file_for_cr_logs()

MessageBox.Show("Multiple Reports Completed", _

"Crystal Report Load", MessageBoxButtons.OK, MessageBoxIcon.Information)

Exit Sub

Case DialogResult.No

'clear and reset

Exit Sub

End Select

' Insert code to read the stream here.

End If

Catch Ex As Exception

MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)

Finally

' Check this again, since we need to make sure we didn't throw an exception on open.

If (myStream IsNot Nothing) Then

myStream.Close()

End If

End Try

End If

End Sub

End Class