Something wrong when changing the ConnectionString ?

[STB]

Hello together

following short question about changing the connection string on a report:

The goal to achive is to give a connection-string from the application to all database objects in the dictionary. This should give the ability to design a report independent from given datasources in the application and simply create them in the report properly and then, when loading the report in the application to establish a data-connection with the right parameter (user, password, database and so on) directly.

For this i wrote some little lines of code which redeclares all database entries in the dictionary with new database items with a new connection string.

This looks like that:

string LocationReport = Application.StartupPath + @"\report\" + this.sPrintReport;
string[] theDatabases = null;

if (this.lCheckIfReportExists()) {
this.oReport.Load(LocationReport);
theDatabases = new string[this.oReport.Dictionary.Databases.Count];
for(int i = 0; i < this.oReport.Dictionary.Databases.Count; i++) {
theDatabases = this.oReport.Dictionary.Databases.Name;
}
this.oReport.Dictionary.Databases.Clear();
for(int i = 0; i < theDatabases.Length; i++) {
this.oReport.Dictionary.Databases.Add(new StiSqlDatabase(theDatabases,this.oConn.DBConnectionString));
}
this.oReport.Render(true);
this.oReport.Print(true);
} else {
MessageBox.Show("Der Report unter \""+LocationReport+"\" konnte nicht geladen werden.","Fehler", MessageBoxButtons.OK,MessageBoxIcon.Error);
}

As you see, new StiSQLDatabases are created through the DBConnectionString of my application.

This always produces the error (sorry, it's in german: "System.ArgumentException: Schlusselwort wird nicht unterstutzt: 'provider'.", which must sound in english like keyword is not supported "provider").

After this, i tried to replace the Connection string with a hard coded value which i copy&pasted from the Report-Designer where you could build new connections to simply get it working, this looks like:

for(int i = 0; i < theDatabases.Length; i++) {
this.oReport.Dictionary.Databases.Add(new StiSqlDatabase(theDatabases,@"Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User ID=xxx;Initial Catalog=OXStundenzettel;Data Source=w2ka"));
}

This brings up the same error (System.ArgumentException: Schlusselwort wird nicht unterstutzt: 'provider'. / keyword is not supported 'provider').

As far as i can see, this is a valid connection string. Someone any idea what's the problem here ?

(maybe this helps, here's the callstack up to my method:
System.ArgumentException: Schlusselwort wird nicht unterstutzt: 'provider'.
bei System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
bei System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
bei System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
bei System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
bei System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
bei System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
bei System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
bei System.Data.SqlClient.SqlConnection..ctor(String connectionString)
bei Stimulsoft.Report.Dictionary.StiSqlAdapterService.CreateConnectionInDataStore(StiDictionary dictionary, StiSqlDatabase database)
bei Stimulsoft.Report.Dictionary.StiSqlDatabase.RegData(StiDictionary dictionary, Boolean loadData)
bei Stimulsoft.Report.Dictionary.StiDictionary.CreateDatabases(Boolean loadData)
bei Stimulsoft.Report.Dictionary.StiDictionary.Connect(Boolean loadData)
bei Stimulsoft.Report.Engine.StiRenderProviderV2.ConnectToData(StiReport report)
bei Stimulsoft.Report.Engine.StiRenderProviderV2.Render(StiReport report, StiRenderState state)
bei Stimulsoft.Report.Engine.StiReportV2Builder.RenderSingleReport(StiReport masterReport, StiRenderState renderState)
bei Stimulsoft.Report.StiReport.RenderReport(StiRenderState renderState)
bei Stimulsoft.Report.StiReport.Render(StiRenderState renderState, StiGuiMode guiMode)
bei Stimulsoft.Report.StiReport.Render(Boolean showProgress)
bei OXStundenzettel.AFDPWindow.PrintPreview() in d:\AFSourcen\OXStundenzettel\AFDPWindow.cs:Zeile 87.
)

Thanks a lot for any reply!

With greetings from germany
Andre

[Brendan]

Hi,

Does it work if you remove 'Provider=SQLOLEDB.1;' from your connection string?

[johnham]

Our design program and report running program use basically the same routine to make sure they are using the correct data. We only allow our users to connect to SQL Data Sources and then we "pre-register" a data connection for them and disable the "New Connection" option in the Dictionary in the designer.

Here is the code that does most of the magic:

Code: Select all

        public Report(string Host, string User, string Password, string Database, string XMLReportParams, string SiteID, string ReportName)
        {

            ///////////
            //  Data
            ///////////
            #region Data
            #endregion
		
            ///////////
            //  Code
            ///////////
            #region Code
            this.ReportName = ReportName;
            this.XMLParameters = XMLReportParams;
            this.Host = Host;
            this.User = User;
            this.Password = Password;
            this.Database = Database;
            this.SiteID = SiteID;
            _ConnectString = BuildConnectString(_Host, _User, _Password, _Database);
            #endregion
		
        }

        private string BuildConnectString(string Host, string User, string Password, string Database)
        {

            ///////////
            //  Data
            ///////////
            #region Data
            StringBuilder strConnection;
            #endregion
		
            ///////////
            //  Code
            ///////////
            #region Code
            strConnection = new StringBuilder();
            strConnection.AppendFormat("Data Source={0}; User Id={1}; Password={2}; Initial Catalog={3};", Host, User, Password, Database);

            return strConnection.ToString();

            #endregion

		
        }

        private StiReport PrepReport()
        {
            ///////////
            //  Data
            ///////////
            #region Data
            StiReport TheReport;
            SqlConnection TheConnection;

            StiServiceContainer RegisteredDataAdapters;
            #endregion

            ///////////
            //  Code
            ///////////
            #region Code
            //Init Report
            
            
            TheReport = LoadReport();            

            //Init Data Connection                        
            TheConnection = new SqlConnection(_ConnectString);


            RegisteredDataAdapters = StiConfig.Services.GetServices(typeof(StiDataAdapterService));
            foreach (StiDataAdapterService dataAdapter in RegisteredDataAdapters)
            {
                if (dataAdapter.GetDataSourceType() != typeof(StiSqlSource))
                {
                    dataAdapter.ServiceEnabled = false;
                }
            }

            //Register Data                                   
            TheReport.RegData("POSitive Retail Manager", TheConnection);

            //Register Parameters
            MakeParameters(_ReportParams, TheReport);

            //Register Custom Functions
            RegisterCustomFunctions();

            return TheReport;
            #endregion
        }

The Constructor takes in some connection parameters and immediately builds it into a connection string. Don't mind the SiteID and XMLParameters that are set in the Constructor. Then my PrepReport() routine makes a StiReport object and sets all the connection information and environment options. In another routine (where I call the designer) I turn off the New Connection menu item in the dictionary so that my users only get the database that pertains to working with our product in their installation.

Hope this provides some direction.

[Jan]

Hello Johnham,

System.ArgumentException: Schlusselwort wird nicht unterstutzt: 'provider'.
bei System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
bei System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
bei System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
bei System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
bei System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
bei System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
bei System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
bei System.Data.SqlClient.SqlConnection..ctor(String connectionString)
bei Stimulsoft.Report.Dictionary.StiSqlAdapterService.CreateConnectionInDataStore(StiDictionary dictionary, StiSqlDatabase database)
bei Stimulsoft.Report.Dictionary.StiSqlDatabase.RegData(StiDictionary dictionary, Boolean loadData)
bei Stimulsoft.Report.Dictionary.StiDictionary.CreateDatabases(Boolean loadData)
bei Stimulsoft.Report.Dictionary.StiDictionary.Connect(Boolean loadData)
bei Stimulsoft.Report.Engine.StiRenderProviderV2.ConnectToData(StiReport report)
bei Stimulsoft.Report.Engine.StiRenderProviderV2.Render(StiReport report, StiRenderState state)
bei Stimulsoft.Report.Engine.StiReportV2Builder.RenderSingleReport(StiReport masterReport, StiRenderState renderState)
bei Stimulsoft.Report.StiReport.RenderReport(StiRenderState renderState)
bei Stimulsoft.Report.StiReport.Render(StiRenderState renderState, StiGuiMode guiMode)
bei Stimulsoft.Report.StiReport.Render(Boolean showProgress)
bei OXStundenzettel.AFDPWindow.PrintPreview() in d:\AFSourcen\OXStundenzettel\AFDPWindow.cs:Zeile 87.

As i see error occurs when report engine try to create new SqlConnection. Parser of SQL connection string does not accept string at "Provider=SQLOLEDB.1". Please check that entered provider exist.

Thank you.

[STB]

Just as short addentum, i resolved the problem. Here is the solution just for the search function if anyone else get's on that

All you (or I) need is to look exactly at the thing that should be done - to establish a new OleDb connection based on the name of the old connection in the report.

For sure, the StiSqlDatabase does not take the provider keyword because it uses the System.Data.SqlClient.SqlConnection Class to build a connection which is for the SQL Datasource Driver only. Therefore, there is no need for a provider keyword in the connection string, so it just throws the error.

Instead of StiSqlDatabase you just need to take StiOleDbDatabase. This make Database entry's based on the common OleDB Driver Components, which take as keyword the provider (what spezifies if it is a sql, a access, a excel, a cvs or something else of datasource which is supported by OleDB).

This works perfectly

[johnham]

Sorry. I didn't catch the need for OLEDB. I use SQL Server so you are correct it has no need for a provider string.

This is a good time to mention:

http://www.connectionstrings.com/


This will help you build a connection string to WHATEVER!!!