Changing databases at run time in SQL Server 2005

Tags:
Crystal Reports 8.5
SQL Server 2005
Visual Basic 6
I am using Crystal Reports 8.5, Visual Basic 6 and SQL Server 2005. In my server, I have two databases named Eng_School_Database and Guj_School_Database with the same structured tables. I want to change the databases at run time so that the report should contain the data of desired medium. I have tried the following code:
If medium="English" Then

rptObj.Database.Tables(1).SetLogOnInfo "XEONSERVER","Eng_School_Database","UserID","Password"

Else

rptObj.Database.Tables(1).SetLogOnInfo "XEONSERVER","Guj_School_Database","UserID","Password"

End If
The problem with the above code is, even if the medium is Gujarati, the report displays data from Eng_School_Database, which is used in designing the crystal report. What can I do to solve this problem?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You may try using Exec SP_ExecuteSQL in SQL Server, or ADO.NET and the System.Data.SQLClient Namespace.

-- Parameters for Stored procedure
@DataBase_Name nVarchar(20)

-- set variables in SQL
Declare
@sql_All nVarchar(1024),
@sql_Select nVarchar(1024),
@sql_From nVarchar(1024)

/*
@sql_Where nVarchar(1024),
@sql_Order nVarchar(1024)
*/

set @sql_Select = ' Select field1, field2' + CHAR(13)

set @sql_From = ' From ' + @DataBase_Name + '.dbo.Table1 TB1 join ' + @DataBase_Name + ' .dbo.Table2 TB2
on TB1.field1 = TB2.field1' + CHAR(13)

set @sql_All = @sql_Select + @sql_From

Exec SP_ExecuteSQL @sql_All,
N'@DataBase_Name nVarchar(20)',
@DataBase_Name

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following