Dear All,

I need advises regarding to my problem below

I have two database in same SQL 2005 SERVER for TEST and LIVE Environtment, and I've created more than 100 reports with Crystal Reports 11 and call it from ASP classic page.

The problem is how can I change a database from TEST01 to LIVE01 at the run time from ASP code as I already using TEST01 database on Crystal Reports as I do not want to set a new database location inside crystal for each repots

Thanks and wait for your reply soon.

Below is my code, which has no effect to crystal reports although I've change the database from TEST01 to LIVE01:

<%

Dim oADOConnection, oRptTable, oADORecordset, sql
Dim struser, strpwd, strdriver, dblocation, dbname, strConnect

struser = "sa"
strpwd = ""
strdriver = "{SQL SERVER}"
dblocation = "SQL200501"
dbname = "LIVE01" ' Changed from TEST01 to LIVE01

strConnect = "User Id=" & strUser & ";"
strConnect = strConnect & "PWD=" & strPwd & ";"
strConnect = strConnect & "DRIVER=" & StrDriver & ";"
strConnect = strConnect & "SERVER=" & DBLocation & ";"
strConnect = strConnect & "DATABASE=" & dbName

sql="Select * from Employee"

Set session("oApp") = Server.CreateObject("CrystalRuntime.Application.11 ")
Set session("oRpt") = session("oApp").OpenReport("C:\REPORTS\RPT01.RPT", 1) 'USING TEST01 DATABASE

session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False
session("oRpt").DiscardSavedData

Set oADOConnection = Server.CreateObject("ADODB.Connection")
oADOConnection.Open (strConnect)
Set oADORecordset = Server.CreateObject("ADODB.Recordset")

Set oRptTable = session("oRpt").Database.Tables.Item(1)
oRptTable.SetDataSource oADORecordset, 3

session("oRpt").SQLQueryString = CStr(sql)

session("oRpt").ReadRecords

%>

I have a similar problem.

Did you find a solution?

>The problem is how can I change a database from TEST01 to LIVE01 at the run time

Have you tried making an include file

<%
Const This DBname = "LIVE01"
%>
Include this file in all your pages and change

dbname = "LIVE01" ' Changed from TEST01 to LIVE01
To
dbname = DBname ' Constant from include file.

Then when you want to change the name of the DB you just change it in the include file.

Earl
www.jhdesigninc.com

In your File Take four variables as-
ODBC="DSN_Name" which store name of DSN for Live Database
DB="Live Database Name"
userid="Live Database Username"
password="Password of Live Database"

And Now making Connection & Recordset is not Required.
Instead of using this line -

oRptTable.SetDataSource oADORecordset, 3

Use this one -

oRptTable.SetLogonInfo cstr(ODBC), cstr(DB), cstr(userid), cstr(password)

Try it.