GetSqlStatement ? connectionless.. ras 9 crystal (asp)

[wolfieman]

Hi everyone!



I am currently developing a crystal report 9 solution for web, using the RAS SDK (report application server) in ASP, but have ran into the following problem:





I have created a report, and selected the following tables and fields in the report.



“SELECT id, name, address, postal, city FROM table1, table2 WHERE table1.id = table2.id”



When I push a submit button in my asp page, I want to add some SQL to the sentence in my report.

For instance we could say I would like to add: and table1.postal = 8000. So the whole SQL sentence would be:



“SELECT id, name, address, postal, city FROM table1, table2 WHERE table1.id = table2.id and and table1.postal = 8000”



I can use the GetSQLStatement method to retrieve the SQL statement stored in the report. Below is my asp code:







'*** Constants ***

Dim adUseClient1

Dim adOpenDynamic1



adUseClient1 = 3

adOpenDynamic1 = 2

'*** End Constants ***



'*** Declarations ***

Dim oConnection 'as ADODB.Connection

Dim oRecordSet 'as ADODB.Recordset

Dim Field 'as ADODB.Field

Dim ObjectFactory 'as CrystalReports.ObjectFactory.2

Dim HTMLViewer 'as CrystalReports.CrystalReportInteractiveViewer

Dim Reportname 'as String

Dim Path 'as String

Dim iLen 'as Integer

Dim viewer

'*** End Declarations ***



Set ObjectFactory = CreateObject("CrystalReports.ObjectFactory.2&quot

Set ReportAppSession = ObjectFactory.CreateObject("CrystalReports.ReportAppSession&quot

ReportAppSession.Initialize

Set Session("ReportAppSession&quot = ReportAppSession



Set ReportClientDocument = ReportAppSession.CreateService("CrystalReports.ReportClientDocument&quot

Reportname = Server.MapPath("../report/report.rpt

Set egbolig_oClientDoc = ReportClientDocument



dim rapport_sql

Set obj = nothing



crConnectionInfoKindCRQE = 5

Dim ci 'Connection Info

Dim pb 'Property BAg for Connection Info Attributes

Dim logonPb 'Logon Property bag for Connection specific information



‘ *** Set ci = egbolig_oClientDoc.DatabaseController.GetConnectionInfos(Nothing).Item(0).Clone(true)

‘ *** Set pb = ci.Attributes

‘ *** If (ci.Kind = crConnectionInfoKindCRQE) Then

‘ *** ' Get the QE_LogonProperties property bag and set the item

‘ *** ' - DSN - System Data Source Name

‘ *** ' - Data Source - name of Database

‘ *** Set logonPb = pb.item("QE_LogonProperties&quot

‘ *** logonPb.item("DSN&quot = Session("egbolig_db&quot

‘ *** End If

pb.item("QE_ServerDescription&quot = Session("egbolig_db&quot

ci.attributes = pb

ci.userName = "sa"

ci.password = ""



egbolig_oClientDoc.DatabaseController.ModifyTableConnectionInfo "Command", ci



rapport_sql = egbolig_oClientDoc.RowsetController.GetSQLStatement(obj)





This code works fine, and I will get the reports SQL statement, through the GetSQLStatement method. BUT if the report uses a DSN name called “GREENDSN”, and I put it on a server where “GREENDSN” doesn’t exist, and I instead call it “YELLOWDSN”, then I get the following error message, when I call the GetSqlStatement method.



Error Type:
Analysis Server (0x8004100F)
SQL server logon failed.
/EgBoligWeb/lejerlister/cs_print.asp, line 171
This is because the report tries to get the SQL statement, but can’t login because the DSN doesn’t exist on that server. Therefore I need to change the connection info (from GREENDSN to YELLOWDSN) before calling the GetSqlStatement method. I do this in the code (uncommented) where the line starts with “‘***”. I try to change the connection info through the method “modifyTableConnectionInfo”. But it doesn’t work; it will give me the following error message:



Error Type:
Analysis Server (0x8004100F)
Logon failed. Details: IM002:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error in File C:\WINNT\TEMP\{87EF303B-069F-4F14-BF18-05176BA48D5D}.rpt: Unable to connect: incorrect log on parameters.
/EgBoligWeb/lejerlister/cs_print.asp, line 171
Why this doesn’t work is my BIG QUESTION? Have anyone experienced the same problem? Maybe it is a bug, because it should be pretty straight forward…Any other way to deal with this problem?