Updating stored procedure name in a crystal report

[Former Member]

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

[former_member183750 · Accepted solution]

See the blog When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET

I'd also recommend using the utility attached to this KBA:

Run one report you modified manually in the above utility, see the code needed to change the SP. Remember you can not change from two tables to one SP.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Am i head down the right path?

As in my code i can open a CR and examine all the data but can i updated and save changing

the stored procedure name in the crystal?

I know there is software that does but  if i could update this or any other it be great doing making mass changes

Thanks

[former_member183750 · Accepted solution]

Yes as far as I can tell. As I understand the issue, you are trying to replace a table with a stored procedure. Stored procedure being just another table, point #4 in the blog When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET

would apply.

But, it must be a one to one relationship. E.g.; you can not replace two tables with one stored procedure. This can not be done even in the designer, thus doubly so using the SDK.

- Ludek

[Former Member · Accepted solution]

i am sure replacing one with another

I have rewritten like 100 store procedures

What i want to do is install on having to open the Cr

go to the stored procedure and update it.

I was hoping to be able as in my .net application i read thought the CR gather INfo

and write that to a data base info table.

I was hoping that somehow i can  by changing  that one name which is really the name to the Stored procedure I  would have have to manually do the work.

Also down the road to be about to do a mass update other items in  a CR

[Former Member · Accepted solution]

HI

Sorry but at this point I just am lost.

question?

Do you know about a product call rpt.inspector?

I see that product can update crystal reports

What I like to know is can I updated crystal reports using the application I wrote as a starting point.

What it does is get info about the crystal report and puts them in a few data base tables sql server 2008 or do I have to go about it another way

I looked at the link you gave me and I see i am basically doing a lot of the same things

AT this point i will admit I am  wil to admit I am not sure whats going on.

can you shed any light on where I have to go to learn to updated crystal reports?

Thanks ahead of time for helping me.

Any help is welcomed

john

[former_member183750 · Accepted solution]

Hi John

Sorry, I don't know anything about rpt inspector. I know Ido often recommends this and other apps from ken hammady web site. Maybe you can search for Ido on these forums and send him a private message(?). Ido is a really nice, helpful guy and smart to boot , lots of experience with CR.

As far as the app. Maybe simplifying things a bit will help. I'd do the following:

1) Create a new report - one table, one field.

2) Create and SP off of the above table.

3) Run the code writing utility on the report created in (1).

4) Copy the code that the utility gives you.

5) Open the report created in (1) in the CR designer.

6) Re-point it at the SP created in (2). Save - as some other name.

7) Run the report from (6) in the code writing utility.

😎 Save the code.

9) Create a new project. Load the report from (1). Use the code from (8). This should give you the same report as you got in (6).

E.g.; the report should be converted to use the SP now. And if it does, build on this with your reports.

- Ludek