Change Database at Runtime for Crystal Report

[Grant Stanley]

I'm developing an application in VS.Net 2003 using C#, which uses Crystal
Reports.

When creating each report, crystal asks where the database to use is, so I
specify our SQL server using the ADO option. The problem is that these
database settings seem to get compiled into the report, so when we take the
application to our client it won't work on their systems.

Before running each report I run some code which is ment to reset the
database information to the correct settings (used by our client) for each
table. The problem is that the report has an SQL Query Command embedded in
it (this query unions two select statements), which when in design view in
crystal it just looks like another database table. The code which runs seems
to update the database information, but cannot update the location setting
for the query and therefore when the report is run, it will keep trying to
look at the SQL Server we used for development.

The Crystal Help suggests something like:
TableLogOnInfo logOnInfo = new TableLogOnInfo();
// Loop through every table in the report.
for(int i=0;i<report.Database.Tables.Count;i++)
{
// Set the connection information for the current table.
logOnInfo.ConnectionInfo.ServerName = DataSource;
logOnInfo.ConnectionInfo.DatabaseName = DatabaseName;
logOnInfo.ConnectionInfo.UserID = Username;
logOnInfo.ConnectionInfo.Password = Password;
report.Database.Tables.ApplyLogOnInfo(logOnInfo);
}

This doesn't work unless you add:
report.Database.Tables.Location = DatabaseName.dbo.TableName

The problem with this is that when it gets to the table called "Command"
which is the SQL Query, Crystal throws an exception because it decides that
"Command" is not a valid table in the specified database.

If you get the code to ignore the "Command" query/table then the report will
continue trying to access our SQL Server for this one query and not the
other one specified.

I've made sure that the SQL Query doesn't have any occurances of
"dbo.tablename" and instead just has "tablename". Both methods fail.

Does anyone know how to update the connection details for a SQL Command in
Crystal?

Thanks,
Grant

 

[Kev Sherratt]

I'm developing an application in VS.Net 2003 using C#, which uses Crystal

Reports.

Poor soul - you have my sympathies.

When creating each report, crystal asks where the database to use is, so I
specify our SQL server using the ADO option. The problem is that these
database settings seem to get compiled into the report, so when we take the
application to our client it won't work on their systems.

About 18months ago, I discovered that this was happening to our reports
also, in v9 evaluation and the version that ships with VS.NET. Everything
changes except the database when you set the connection details at runtime.
If you are using SQL Server you can run a trace and notice that it tries to
access the original database that the report was developed against,
regardless of your code. We develop against what is essentially an
arbitrary named database, so this is a major problem for us too.

 

[Kev Sherratt]

When creating each report, crystal asks where the database to use is, so I

specify our SQL server using the ADO option. The problem is that these
database settings seem to get compiled into the report, so when we take the
application to our client it won't work on their systems.

(Whoops - sent the message before I finished! Wrong key combo..)

I filed a bug report to Crystal/Seagate over a year ago about the fact the
database does not reflect changes at runtime, but the last evaluation I
downloaded still hadn't addressed this problem. I also got into
conversations with other users facing this problem who also reported or had
no help from Crystal support. All in all, it sucks. Version 8 doesn't
suffer from this, so we use that through COM.

I don't think it helps that it manifests itself as an addressed issue
(completely unrelated) that they provide a "fix" for. So while it doesn't
exactly help you out, at least you know you're not alone and (as far as I'm
willing to guess) it's not your fault

(btw, we use ODBC access to our database not ADO directly, but the
problem seems to persist)

K.

 

[Grant Stanley]

Hello,

Thanks for the reply, its good to know its not just my code. I guess I'll
have to work around it somehow.

One question, is it just the Database Name that cannot be changed at runtime
but the Database Server, Username and Password can be changed?
Because if this is the case then I can probably live with it, although it
will make testing the software against our development database and our
clients database a little more annoying.

Thanks for the help,
Grant

When creating each report, crystal asks where the database to use is, so I
specify our SQL server using the ADO option. The problem is that these
database settings seem to get compiled into the report, so when we take the
application to our client it won't work on their systems.

(Whoops - sent the message before I finished! Wrong key combo..)

I filed a bug report to Crystal/Seagate over a year ago about the fact the
database does not reflect changes at runtime, but the last evaluation I
downloaded still hadn't addressed this problem. I also got into
conversations with other users facing this problem who also reported or had
no help from Crystal support. All in all, it sucks. Version 8 doesn't
suffer from this, so we use that through COM.

I don't think it helps that it manifests itself as an addressed issue
(completely unrelated) that they provide a "fix" for. So while it doesn't
exactly help you out, at least you know you're not alone and (as far as I'm
willing to guess) it's not your fault

(btw, we use ODBC access to our database not ADO directly, but the
problem seems to persist)

K.

 

[Kev Sherratt]

One question, is it just the Database Name that cannot be changed at
runtime

but the Database Server, Username and Password can be changed?
Because if this is the case then I can probably live with it, although it
will make testing the software against our development database and our
clients database a little more annoying.

From what I've experienced, yes, it's just the database name (this is with
ODBC and a minor ADO testing with SQL Server.) But of course, your mileage
may vary and it'd be best to make sure this is the case with you too,
obviously.

Best of luck.