Help/Guidance with multiple database connections

KevRob

Hi,

I am currently evaluating Fast Reports to incorporate a reporting engine into an application and need some help/guidance please.

The application has a minimum of 3 database connections in a hierarchical structure.
Main Database (Holds global application settings, user information, etc)
Company Database (Holds company information, list of authorised users to the company and company modules)
ProgramModule Database (Holds information for the program module such as jobs, customers, etc and each module has a different database schema)

Just to explain each program module is like a mini application.

There can be many company databases and many program module databases and each database connection at any level can be to either Access or SQL Server.

Due to this the connections needs to be completely dynamic depending on which company the user has logged into and which module the user is accessing.

When a new report is designed I create connections and define the tables and fields in code:-
'***********************************************************************************************
'ADD MAIN APPLICATION DATABASE CONNECTION WITH USERS TABLE.
Try
MainApplicationConn = New FastReport.Data.OleDbDataConnection
MainApplicationConn.ConnectionString = mProgramModule.MainApplication.DatabaseConnection.ConnectionString
MainApplicationConn.Name = My.Application.Info.AssemblyName

'*********************************************************************************************************
'USERS TABLE
ReportTable = New FastReport.Data.TableDataSource
ReportTable.TableName = "Users" : ReportTable.Name = ReportTable.TableName : ReportTable.Alias = ReportTable.TableName
ReportTable.SelectCommand = "SELECT User_ID, First_Name, Last_Name, Telephone_Num, Mobile_Num, Email_Address, Active FROM Users"
rs = New ADODB.Recordset
rs.Open(ReportTable.SelectCommand, mProgramModule.MainApplication.DatabaseConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly)
For Each Field As ADODB.Field In rs.Fields
ReportColumn = New FastReport.Data.Column
ReportColumn.Name = Field.Name
ReportColumn.DataType = Me.ConvertADODBTypeToSystemType(Field.Type)
ReportTable.Columns.Add(ReportColumn)
Next
rs.Close() : rs = Nothing
MainApplicationConn.Tables.Add(ReportTable)

'***ABOVE CODE IS REPEATED FOR ALL TABLES I WANT THE USER TO HAVE ACCESS TOO. ****

'ADD THE MAIN APPLICIATION CONNECTION TO THE REPORTS DICTIONARY
Me.FastReport.Dictionary.Connections.Add(MainApplicationConn)
Catch ex As Exception
MsgBox(ex.Message)
End Try


'***********************************************************************************************
'ADD MODULE DATABASE CONNECTION WITH SITES TABLE.
Try
ModuleConn = New FastReport.Data.OleDbDataConnection
ModuleConn.ConnectionString = mProgramModule.DatabaseConnection.ConnectionString
ModuleConn.Name = mProgramModule.Description

ReportTable = New FastReport.Data.TableDataSource
ReportTable.TableName = "Sites" : ReportTable.Name = ReportTable.TableName : ReportTable.Alias = ReportTable.TableName
ReportTable.SelectCommand = "SELECT * FROM Ref_Sites"
rs = New ADODB.Recordset
rs.Open(ReportTable.SelectCommand, mProgramModule.DatabaseConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly)
For Each Field As ADODB.Field In rs.Fields
ReportColumn = New FastReport.Data.Column
ReportColumn.Name = Field.Name
ReportColumn.DataType = Me.ConvertADODBTypeToSystemType(Field.Type)
ReportTable.Columns.Add(ReportColumn)
Next
rs.Close() : rs = Nothing
ModuleConn.Tables.Add(ReportTable)

'***ABOVE CODE IS REPEATED FOR ALL TABLES I WANT THE USER TO HAVE ACCESS TOO. ****

'ADD THE MODULE CONNECTION TO THE REPORTS DICTIONARY
Me.FastReport.Dictionary.Connections.Add(ModuleConn)
Catch ex As Exception
MsgBox(ex.Message)
End Try


Any time a saved report is then accessed I remap the connections.
Try
Me.FastReport.Load("C:\TestReport.frx")
CType(Me.FastReport.Dictionary.FindByName(mProgramModule.Description), FastReport.Data.OleDbDataConnection).ConnectionString = mProgramModule.DatabaseConnection.ConnectionString
CType(Me.FastReport.Dictionary.FindByName(My.Application.Info.AssemblyName), FastReport.Data.OleDbDataConnection).ConnectionString = mProgramModule.MainApplication.DatabaseConnection.ConnectionString
Me.FastReport.Show()
Catch ex As SystemException
MsgBox(ex.Message)
End Try

Is this method ok? I have tried different databases and the report is populating with the correct data but want to check as the programmers manual says to use the DatabaseLogin event of the EnvironmentSettings component to pass the connection string (only seems to cater for one database connection though) and the other method of a report parameter seems to cater for mutiple connections but I do prefer the above.

Also I tried using the EnvironmentSettings component so I could control the Save Dialog but none of the events are firing:( Looked for a property on the Repot component (in case you have to set it like the Preview component) but cant find nothing. Any ideas?

Cheers
Kev

AlexTZ

Hello,

The simpler way to add a connection along with all its tables:

      MsAccessDataConnection conn = new MsAccessDataConnection();
      conn.Name = "MyConnection";
      conn.DataSource = @"c:\test.mdb";
      report1.Dictionary.Connections.Add(conn);

      // create FR datasources for all connection tables/views
      conn.CreateAllTables();

      // enable tables so they all appear in the "Data" window under the "MyConnection" node
      foreach (TableDataSource ds in conn.Tables)
      {
        ds.Enabled = true;
      }

The code you use to dynamically change the connection string is ok; the other way is to use DatabaseLogin event, it fires for each connection in a report.

To control the Open/Save dialogs, use the EnvironmentSettings component as described in the programmer's manual, "Working with Windows.Forms/Replacing the "Open" and "Save" dialogs".

KevRob

Hi and cheers for the quick reply.

I did try using the CreateAllTables() method but I dont want to add all the tables and some of the tables are SQL Queries so I didnt see the point. Would be nice if there was a method that would define all the columns though? The hope is I can prevent the users from editing/viewing the connection information and also limit them to only the tables and fields I want them to have access to. Still want them to see the Data window just with limited funtionality, is this possible? I have seen the DontEditData property but this seems to be an all or nothing scenario where as I would like to allow them to add there own connections etc but not to be able to modify any connections, tables, fields that was set by the application.

As I mentioned I tried the EnvironmentSetting control on the form as it says in the programmers manual with some message boxes in the events but none of the events are firing? Tried designing, showing and saving the report and no message boxes are displayed. I'm probably doing something wrong or haven't set something somewhere so added the code from a test form. Any ideas?

Public Class Form1
    Private Sub EnvironmentSettings_CustomSaveDialog(ByVal sender As Object, ByVal e As FastReport.Design.OpenSaveDialogEventArgs) Handles EnvironmentSettings.CustomSaveDialog
        MsgBox("EnvironmentSettings_CustomSaveDialog")
    End Sub

    Private Sub EnvironmentSettings1_CustomSaveReport(ByVal sender As Object, ByVal e As FastReport.Design.OpenSaveReportEventArgs) Handles EnvironmentSettings.CustomSaveReport
        MsgBox("EnvironmentSettings1_CustomSaveReport")
    End Sub

    Private Sub EnvironmentSettings1_DatabaseLogin(ByVal sender As Object, ByVal e As FastReport.DatabaseLoginEventArgs) Handles EnvironmentSettings.DatabaseLogin
        MsgBox("EnvironmentSettings1_DatabaseLogin")
    End Sub

    Private Sub DesignReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DesignReport.Click
        Try
            Me.Report.Clear()
            Dim conn As New FastReport.Data.OleDbDataConnection
            conn.Name = "Property Services"
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Property Services.mdb;Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None"
            ''''            conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Property Services;Data Source=kev-laptop\sqlserver"
            Me.Report.Dictionary.Connections.Add(conn)
            conn.CreateAllTables()
            For Each ds As FastReport.Data.TableDataSource In conn.Tables
                ds.Enabled = True
            Next
            Me.Report.Design()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ShowReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ShowReport.Click
        Try
            Me.Report.Clear()
            Me.Report.Load("C:\TestReport.frx")
            Me.Report.Show()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

AlexTZ

wrote:

Would be nice if there was a method that would define all the columns though?

Use InitSchema method to init all columns:

      MsAccessDataConnection conn = new MsAccessDataConnection();
      conn.Name = "MyConnection";
      conn.DataSource = @"c:\test.mdb";
      report1.Dictionary.Connections.Add(conn);

      TableDataSource table = new TableDataSource();
      conn.Tables.Add(table);
      table.Name = "CustomersTable";
      table.TableName = "Customers";
      table.InitSchema();
      table.Enabled = true;

wrote:

Still want them to see the Data window just with limited funtionality, is this possible?

I can try to use the existing Restriction property for this purpose. Now it is not used by data items.

wrote:

As I mentioned I tried the EnvironmentSetting control on the form as it says in the programmers manual with some message boxes in the events but none of the events are firing?

Strange, it works well for me (I use C#). I will try to reproduce the bug.

AlexTZ

Looks like VB "Handles" keyword is not working correctly with EnvironmentSettings, the event is not fired. Use AddHandler keyword to wire events manually.

KevRob

Hi,

AddHandler sorted the problem with the EnvironmentSetting component. Thanks.

Have noticed that the DatabaseLogin event is firing more than once for each connection. When designing a new report using the connections as below it fires 7 times for the first connection and then 4 times for each subsequent connection. Any idea why? Also when designing an existing report the DatabaseLogin event does not fire at all which could cause me a problem as the connection string information in the report might not be valid. It does fire for Preview(), Show() and Print() though.

There seems to be a problem with InitSchema() when using an OleDbDataConnection to SQL; the columns collection is not being populated.
I'm using the OleDbDataConnection object as each database within the application can be either Access or SQL and we plan to put support in for MySQL as well. Test code below: -

Cheers for the help.

            'Colums collection OK.
            Dim connSQL As New FastReport.Data.MsSqlDataConnection
            connSQL.Name = "Workplace Suite SQL"
            connSQL.ConnectionString = "Data Source=CODE-SERVER;Initial Catalog=Workplace Suite;Integrated Security=True;Persist Security Info=True"
            Me.FastReport.Dictionary.Connections.Add(connSQL)
            connSQL.CreateAllTables()
            For Each ds As FastReport.Data.TableDataSource In connSQL.Tables
                ds.InitSchema()
                ds.Enabled = True
            Next

            'Colums collection OK.
            Dim connAccess As New FastReport.Data.MsAccessDataConnection
            connAccess.Name = "Workplace Suite Access"
            connAccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Workplace Suite.mdb;User ID=Admin"
            Me.FastReport.Dictionary.Connections.Add(connAccess)
            connAccess.CreateAllTables()
            For Each ds As FastReport.Data.TableDataSource In connAccess.Tables
                ds.InitSchema()
                ds.Enabled = True
            Next

            'Colums collection is NOT populated using the InitSchema() method
            Dim connOleSQL As New FastReport.Data.OleDbDataConnection
            connOleSQL.Name = "Workplace Suite OleDB SQL"
            connOleSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Workplace Suite;Data Source=code-server"
            Me.FastReport.Dictionary.Connections.Add(connOleSQL)
            connOleSQL.CreateAllTables()
            For Each ds As FastReport.Data.TableDataSource In connOleSQL.Tables
                ds.InitSchema()
                ds.Enabled = True
            Next

            'Colums collection OK.
            Dim connOleAccess As New FastReport.Data.OleDbDataConnection
            connOleAccess.Name = "Workplace Suite OleDB Access"
            connOleAccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Workplace Suite.mdb;Persist Security Info=False"
            Me.FastReport.Dictionary.Connections.Add(connOleAccess)
            connOleAccess.CreateAllTables()
            For Each ds As FastReport.Data.TableDataSource In connOleAccess.Tables
                ds.InitSchema()
                ds.Enabled = True
            Next

AlexTZ

Hello,

wrote:

Have noticed that the DatabaseLogin event is firing more than once for each connection.

It fires every time when connection is about to open. If you have 5 tables in a connection, the connection will be opened 5 times, because FR uses the following code to populate a table with data:

      using (DbConnection conn = GetConnection())
      {
        conn.Open();
        
        // read the table
        using (DbDataAdapter adapter = GetAdapter(selectCommand, conn, parameters))
        {
          adapter.Fill(table);
        }
      }

I've noticed in your code that you use both CreateAllTables and InitSchema. This is not correct because CreateAllTables also creates the schema. There may be a problem with your connection string in case when columns aren't populated?

KevRob

The columns are not populated when using the OleDbDataConnection with SQL (unfortunately this is the connection type I am testing/evaluating). If I use OleDbDataConnection with Access, MsAccessDataConnection or MsSqlDataConnection all columns as you say are populated when the CreateAllTables() is called.

            Dim connOleSQL As New FastReport.Data.OleDbDataConnection
            connOleSQL.Name = "Workplace Suite OleDB SQL"
            connOleSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Workplace Suite;Data Source=code-server"
            Me.FastReport.Dictionary.Connections.Add(connOleSQL)
            connOleSQL.CreateAllTables()
            For Each ds As FastReport.Data.TableDataSource In connOleSQL.Tables
                '*****************************************
                'HERE the ds.Columns.Count = 0                 

                ds.InitSchema()

                '*****************************************
                'AND ALSO HERE the ds.Columns.Count = 0  which is why i tried the InitSchema() method aswell.

                ds.Enabled = True
            Next

I have got a get around by declaring the connection as a FastReport.Data.DataConnectionBase , check the database type I need to connect to, set the connection to either a MsAccessDataConnection or MsSqlDataConnection, setting the connection string, thus keeping the table and column code the same. Only problem is when we put MySQL support into the application I could have problems again?

Also mentioned that the DatabaseLogin event is not firing when I design an existing report. I.e. Load("C:\TestReport.frx) followed by Design(). Should it be firing? If not should I modify the connection string directly via the connection object like i did before?

AlexTZ

wrote:

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Workplace Suite;Data Source=code-server"

Does this connection string work if you create a new datasource with this connection string in FR designer (Data|Add Data Source... menu)?

wrote:

Also mentioned that the DatabaseLogin event is not firing when I design an existing report. I.e. Load("C:\TestReport.frx) followed by Design(). Should it be firing?

No. it should not. This event is fired when the connection is about to open. This may happen if you trying to view table data in the "Data" window, or when you run a report. When you run the designer, it does not open any connections. So your initial solution is better in this case.

KevRob

Hi,

The connection string works in the FR designer, all tables are visible to select but there is no + synmbol next to the tables to expand to see the fields. Only way I can get a list of fields is to re-edit the table once the connection and tables been added to in FR designer, enter a select statement and then the fields are visible. (Can only think this is because I am entered the SELECTCOMMAND on the table object?)

I also tried building the connection string within FR Designer and get the same results as above.

However, if i use the MsSqlDataConnection all tables are visible to select and the + sign is next to the tables so i can expand to see the fields. (I didnt know it did this which is why I put all that code in to add the tables and columns in my original post).

Can send screenshots if you want.

Cheers
Kev

AlexTZ

Now I see the problem.
When you create a new connection and view its tables in the "Data Wizard" window, FR sends select command to the connection, to get the columns of a table. The command is simple:

select * from tablename

The problem is that "tablename" may have spaces or some other "bad" symbols inside. FR uses escape symbols, and the command would be

select * from 'tablename'

The bad thing that escape symbols are connection-specific. For example, MS Access uses '' or []; MS SQL uses "" and so on. FR uses appropriate escapes for each connection type (such as MsAccessConnection, MsSqlConnection). The OleDbConnection which you use is generic connection - you may connect to different datasources with it. But it uses escapes specific to MS Access which may not work with other datasources.

To overcome this restriction, use the specific connection type. At this moment, FR supports several connections - MS Access, MS SQL, Oracle, Firebird, MySQL, Postgres, VistaDB.

KevRob

I see what your saying about the escape symbols being dependent on the database type especially if the Access escape symbols are always used for OleDB. Do you think this is a problem with the underlying OleDB Provider for SQL Server? Would of thought they would have catered for this since the provider is specific to SQL Server? Will change my code not to use OleDB anyway to get it all working, just didnt want to code specific to the database type, hence was using OleDB.

Also found it alot easier to set the connection string directly in the report dictionary rather than using the DatabaseLogin event especially with multiple database connections which could be of different types potentially in the same report. Just wondering if there is any specific reason for using the DatabaseLogin event?

Only thing left now is to be able to restrict what the users can do in the Data window of the report designer. You did mention this should be possible using the Restrictions property but any idea on when this could be available?

Once again cheers for all the help. Hopefully the post will stop someone else pulling there hair out if their using the OLEDB connection to SQL.

AlexTZ

- The problem is in FastReport - it needs a common way to obtain the table schema, but it is not possible with OleDbConnection. You can use this connection type to connect to Access or SQL server, and they are using different SQL dialects. That's why I recommend to use the specific connector for each database.

- DatabaseLogin event was designed to easily switch between two environments (for example, development and production). Of course you can use the code to manipulate the connection object directly, but in this case you will need to hardcode the connection name (that may not exist in a report). Not in your case, though.

- I've added the Restriction property to all data items such as connection, datasource, column. The property has set of flags, you may use the following (any combination of it):
- DontEdit - disables editing
- DontModify - disables modifying the properties in the "Properties" window
- DontDelete - disables deleting.

You may set this property when you first register the data objects.

KevRob

Ok. You have converted me from using the OleDbConnection:)


Nice one for putting this feature in so quickly.

AlexTZ wrote: »

- I've added the Restriction property to all data items such as connection, datasource, column. The property has set of flags, you may use the following (any combination of it):
- DontEdit - disables editing
- DontModify - disables modifying the properties in the "Properties" window
- DontDelete - disables deleting.

You may set this property when you first register the data objects.

Had a quick look and it seems to be just what I need apart from the restrictions can be changed in the designer itself. Any chance this can be disabled aswell? Hiding all properties for the datasource would be perfect as I want to be able to lock the datasource down so they can just create any relationships from my datatasources to any datasources they have added.

Did notice on the Choose Report Data screen you could unselect the restricted datasource but it didnt remove it anyway.

Also noticed that if the report is opened in another designer (I.e the FR Demo) the restrictions are no longer applied. I could encrypt the report files so only my application can read them but just thought I would mention it.

Sorry if this the wrong place to ask, but just we are not sure which edition to go for, it's between WinForms and Professional as we dont need the Web Forms components but the Professional edition says it includes source code?

Cheers again.

AlexTZ

wrote:

Had a quick look and it seems to be just what I need apart from the restrictions can be changed in the designer itself. Any chance this can be disabled aswell?

This is just a simple way to restrict the "dumb" user from doing something unwanted. I will try to add a new flag in the Restriction that can't be set from the designer, and hides all properties completely.

wrote:

Also noticed that if the report is opened in another designer (I.e the FR Demo) the restrictions are no longer applied.

Strange, it works wel for me. The Restriction property is written in the .frx file.

wrote:

Sorry if this the wrong place to ask, but just we are not sure which edition to go for, it's between WinForms and Professional as we dont need the Web Forms components but the Professional edition says it includes source code?

You may choose the Win edition, it should be sufficient for your needs. If you will need source you may upgrade at any time.

KevRob

wrote:

This is just a simple way to restrict the "dumb" user from doing something unwanted. I will try to add a new flag in the Restriction that can't be set from the designer, and hides all properties completely

Brilliant.

wrote:

Also noticed that if the report is opened in another designer (I.e the FR Demo) the restrictions are no longer applied.

Strange, it works wel for me. The Restriction property is written in the .frx file..

I have tried this with the FR Designer (V1.1.16) to eliminate any application code, added a datasource, set the restrictions on the datasource, saved it, re-opened it and the restrictions property is set to none on the connection. If I add a table restrictions however these are retained just not the connection restrictions.

Also checked the restriction information in the .frx file and the table ones are there but nothing against the connection. Will probably need to encrypt the report files to stop them manually removing the restrictions from the frx file as well.

Cheers

AlexTZ

I've fixed the connection, thanks.

KevRob

Hi,

Im trying to get the table schema for an existing report to update using the code below but any new columns I add to the database are not visible in the report designer. If I edit the table within the report designer and then go through the wizard screens the new field apears.

'Ref_Regions
                ReportTable = CType(ReportConnection.FindObject("Ref_Regions"), FastReport.Data.TableDataSource)
                If ReportTable Is Nothing Then
                    ReportTable = New FastReport.Data.TableDataSource : ReportConnection.Tables.Add(ReportTable)
                    ReportTable.TableName = "Ref_Regions" : ReportTable.Name = ReportTable.TableName : ReportTable.Alias = ReportTable.TableName
                End If
                ReportTable.SelectCommand = "SELECT * FROM " & ReportTable.TableName
                ReportTable.InitSchema() : ReportTable.Enabled = True

Also tried it like this by changing the SelectCommand on the table object but the new field is still not visible until i edit the table, etc, etc

'Ref_Regions
                ReportTable = CType(ReportConnection.FindObject("Ref_Regions"), FastReport.Data.TableDataSource)
                If ReportTable Is Nothing Then
                    ReportTable = New FastReport.Data.TableDataSource : ReportConnection.Tables.Add(ReportTable)
                    ReportTable.TableName = "Ref_Regions" : ReportTable.Name = ReportTable.TableName : ReportTable.Alias = ReportTable.TableName
                End If
                ReportTable.SelectCommand = "SELECT Region_ID, Description, 'SomeNewField' AS Region_New_Field FROM " & ReportTable.TableName
                ReportTable.InitSchema() : ReportTable.Enabled = True

The report is loaded before the code above is executed and new reports which run through the same code have the new field available. Any ideas?

AlexTZ

Hello,

I will make visible the table.RefreshTable method in the next build.

KevRob

Cheers. Will this affect any calculated columns that the user may of added?

AlexTZ wrote: »

Hello,

I will make visible the table.RefreshTable method in the next build.

Did you manage to put an extra Restriction on the data objects so I can prevent the users from changing the restrictions within the designer?

Also, Im having trouble with a master/detail report where the parent record does not have any child records. I.e Category does not have any products. I want the report to print all the Categories regardless of how many products they have but the report seems to skip/not show these Categorise all together? Tried this using the Demo program after modifying the nwind.xml file so all products with the "Produce" category had a different category.

AlexTZ

- No, calculated columns will stay untouched.

- I've added Restrictions.HideAllProperties member - you can use it in your code right now.

- To display master data row regardless of its details, set the master data band's PrintIfDetailEmpty property to true.

KevRob

Cheers for the RefreshTable() change.

Just want to check if there is any reason I shouldnt use the RefreshTable() method to add new report tables aswell as updating existing ones; instead of InitSchema()? Just saves me having both methods in my SetupReportData method() and it looks like the RefreshTable() method works in both scenarios.

AlexTZ wrote: »

Hello,

I will make visible the table.RefreshTable method in the next build.

AlexTZ

Since RefreshTable deletes obsolete columns and creates new ones, you may use it instead of InitSchema.

KevRob

Sorry, encountered problem:(

All our report files need to be able to run on any database type. These could be standard reports we create or ones the user creates.
Original I was using the OleDBConnection to cater for the different database type but due to problems when retrieving the table schemas it was suggested to use the specific connection type.

Problem is just changing the connection string does not work as the syntax might not match the specific connection type.
To get around this I create a new connection object, copy all the child objects to the new connection and then replace the original connection in the report.
This seems to work but just worried that something might get missed. I tried the AssignAll() method but this replaced the connection string and was concerned that another property or object could also get replaced after calling this method.

Can someone confirm if the above solution is ok, should I do it another way (business objects) or should I revert back to OleDBConnection? If I should revert back can the InitSchema()/RefreshTable be fixed to cater for the different database providers?

Might be a thought for the DatabaseLogin() event if a report is being processed against another database type.

Cheers again.

Test routine below

Public Sub SetupReportDataTEST(ByVal Report As FastReport.Report)
            Dim ReportConnection As FastReport.Data.DataConnectionBase = Nothing
            Dim OriginalReportConnection As FastReport.Data.DataConnectionBase = Nothing
            Dim ReportTable As FastReport.Data.TableDataSource = Nothing

            Dim bSQLServerConnection As Boolean = True

            'Find the TestDatabase Connection in the Report Dictionary.
            OriginalReportConnection = CType(Report.Dictionary.FindByName("TestDatabase"), FastReport.Data.DataConnectionBase)

            'Check current connection type and force the new connection to change from SQL to Access and Vice Versa.
            If Not OriginalReportConnection Is Nothing Then
                bSQLServerConnection = Not TypeOf OriginalReportConnection Is FastReport.Data.MsSqlDataConnection

                'Remove the original connection.
                Report.Dictionary.Connections.Remove(OriginalReportConnection)
            End If

            If bSQLServerConnection Then
                ReportConnection = New FastReport.Data.MsSqlDataConnection
                ReportConnection.ConnectionString = "Data Source=KEV-DEV-PC2\SQLEXPRESS;Initial Catalog=TestDatabase;Integrated Security=True;Persist Security Info=False"
            Else
                ReportConnection = New FastReport.Data.MsAccessDataConnection
                ReportConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestDatabase.mdb"
            End If
            ReportConnection.Name = "TestDatabase"
            Report.Dictionary.Connections.Add(ReportConnection)

            'Test the connection
            Try
                ReportConnection.TestConnection()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

            'Add each child object from the original connection to the new connection.
            If Not OriginalReportConnection Is Nothing Then
                'ReportConnection.AssignAll(OriginalReportConnection)  'Didnt use this method as it interfers with the new connection type (Copies the original connection string)
                For Each ChildObject As FastReport.Base In OriginalReportConnection.ChildObjects
                    ReportConnection.AddChild(ChildObject)
                Next
            End If

            '***************************************************************************
            'Add a table to the report.
            'Customers
            ReportTable = CType(ReportConnection.FindObject("Customers"), FastReport.Data.TableDataSource)
            If ReportTable Is Nothing Then
                ReportTable = New FastReport.Data.TableDataSource : ReportConnection.Tables.Add(ReportTable)
                ReportTable.TableName = "Customers" : ReportTable.Name = ReportTable.TableName : ReportTable.Alias = ReportTable.TableName
            End If
            ReportTable.SelectCommand = "SELECT Customer_ID, Customer_Name FROM " & ReportTable.TableName
            ReportTable.RefreshTable() : ReportTable.Enabled = True
        End Sub

AlexTZ

I will try to use more generic [] symbols as quotation chars for OleDB. The fixed version (1.1.29) will be available tomorrow.

KevRob

Ok, cheers.

Could you not check the provider of the connection to determine what symbols to use?

I dont mind replacing the connection object as a work around but just wanted to know if you thought it was ok or not? I know what the connection names will always be it was just the thought some objects/properties could get lost during this process.

AlexTZ wrote: »

I will try to use more generic [] symbols as quotation chars for OleDB. The fixed version (1.1.29) will be available tomorrow.

AlexTZ

Your solution should be ok, unless you use SQL with parameters. In this case you cannot just replace the connection object. In fact, it will be almost impossible to do:
- different connections use different parameter types. OleDbType, SqlDbType, MySqlDbType etc. If you use SQL parameters and want to change the connection, you'll have to replace parameter types as well.
- parameters may be handled differently. For example, MS SQL uses @ symbol to denote a parameter. MS Access does not support named parameters at all - you need to use ? symbol.

However, if updated OleDb connection will do the work, it will be more straightforward solution, just to replace the connection string.