Problems while migrating from RDC to .Net

[patrick_simons2]

I'm using VB.Net 2010 with CR 2008 SP3 to rewrite our library (Interop COM Class...)

I have two major problems:

first, while changing the table logon infos; I can't find the correct way (1st try vs. 2nd try) to update them. This sample tries to change a template MS-SQL table "xTableTest" to "##TableTest123456":

For Each checkTable As CrystalDecisions.CrystalReports.Engine.Table In crystalReportDocument.Database.Tables
    With checkTable
        If .Location.ToUpper.Substring(0, 2) = "XT" Then
            ' change SQL temporary table
            newDatabase = .LogOnInfo.ConnectionInfo.DatabaseName
            newTable = "tempdb.dbo.##" & .Location.Substring(1) & uniqueValue
        End If

        ' 1st try
        With checkTable.LogOnInfo.ConnectionInfo.LogonProperties
            .LookupNameValuePair("Data Source").Value = serverName
            .LookupNameValuePair("Initial Catalog").Value = newDatabase
            .LookupNameValuePair("Connect Timeout").Value = 300
        End With
        With .LogOnInfo.ConnectionInfo
            .ServerName = serverName
            .DatabaseName = newDatabase                        
            .UserID = "User"
            .Password = "pwd"
            .IntegratedSecurity = False
        End With
        .Location = newTable

        ' 2nd try
        Dim newTableLogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
        newTableLogoninfo = .LogOnInfo ' or .LogOnInfo.Clone
        With newTableLogoninfo
            With newTableLogoninfo.ConnectionInfo
                .ServerName = serverName
                .DatabaseName = newDatabase                            
                .UserID = "User"
                .Password = "pwd"
                .IntegratedSecurity = False
            End With
            .TableName = newTable
        End With
        .ApplyLogOnInfo(newTableLogoninfo)

        If Not .TestConnectivity Then                        
            Exit For
        End If
   End With
Next

the first try returns on " .Location = newTable" the following error:

Logon failed. Details: [Database Vendor Code: 18456 ] Error in File Test_L1 {2FFD3972-1D55-41D7-AAD2-FC06400E2F29}.rpt: Unable to connect: incorrect log on parameters.

The second try sometimes works (TestConnectivity), but when the whole code runs, the .ApplyLogOnInfo() resets orginal values of .LogOnInfo.ConnectionInfo ! And how can I set the LogonProperties through the ApplyLogOnInfo() method?

Second problem:

When I call the PrintToPrinter()-method, it returns Invalid report file path. The Load-method before worked without any problems.

Any ideas,

Patrick

[former_member183750 · Accepted solution]

CR 2008 will not work with VS 2010. Only [CRVS2010|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20322] [original link is broken] [original link is broken] [original link is broken]; can be used with .NET 2010.

Note that CRVS2010 is only a Beta...

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

[patrick_simons2 · Accepted solution]

Thanks Ludek,

But we have to use the stand-alone version CR 2008 (or CR XI R2). So we should downgrade to VS.Net 2008?

Do you have somewhere a relation reference between CR and VS? for example something like this:

• VS 2010 works only with CR 2010 for VS.net

• VS 2008 works only with CR 2008 and CR 2008 for VS.net

• VS 2005 works only with CR XI R2 and CR 2005 for VS.net

correct?

What will be the next stand-alone version for CR?

Is it possible to write an interface which can address both CR 2008 and CR XI R2 reports?

Patrick

[former_member183750 · Accepted solution]

See my replies below:

But we have to use the stand-alone version CR 2008 (or CR XI R2). So we should downgrade to VS.Net 2008?

- That is correct

Do you have somewhere a relation reference between CR and VS?

- See the wiki [Which Crystal Reports assembly versions are supported in which versions of Visual Studio .NET |https://wiki.sdn.sap.com/wiki/display/BOBJ/WhichCrystalReportsassemblyversionsaresupportedinwhichversionsofVisualStudio+.NET]

What will be the next stand-alone version for CR?

- I suppose CR 2011, but you never know what "they" will call it. Should release sometime in the 1st half of 2011 (this is an ETA, not a guarantee).

Is it possible to write an interface which can address both CR 2008 and CR XI R2 reports?

- The version of CR that the report is writhen in is not that important. The runtime is. You always want to use runtime that is either equal to or higher than the version the report was written in - never use a lower version of the runtime. To answer the question directly. CR2008 will be able to run report built in CR XI R2 as well as CR 2008. So will CR 2010.

Ludek

[patrick_simons2 · Accepted solution]

I recreated the project from scratch with VS 2008. The first problem went away, but the second problem is still where.

Any ideas?

Patrick

[former_member183750 · Accepted solution]

Can you paste in a copy of the complete error? I don't think I've ever seen CR throw the error:

Invalid report file path

Ludek

[patrick_simons2 · Accepted solution]

Concerning "Invalid report file path": I found the mistake, I was addressing the subreport instead of the mainreport, so it works now.

Nevertheless a question concerning my first problem. Since I changed back to VS 2008, it works and I got no error. But what is the best rule of thumb to change the location of tables?

When I analyse my two codeblocks, the .ApplyLogOnInfo()-method resets some values set before ("Connect Timeout" is resetted to 15, checkTable.LogOnInfo.ConnectionInfo.Password is resetted to "") - why?

BTW: "newTableLogoninfo = .LogOnInfo" does not "copy/clone" the LogonProperties....!

Patrick

[patrick_simons2 · Accepted solution]

Changing the table location is an important "corner stone" of the CR-SDK?

There should exist somewhere a good guideline....

any guru out there?

Patrick

[former_member11696 · Accepted solution]

Hi Pat,

The problem I believe is in the RDC you were using the LogSQLServerWithPrivateInfo API correct?

It was the only way to connect to the TEMPDB under the HDBC handle of your ODBC connection:

"newTable = "tempdb.dbo.##" & .Location.Substring(1) & uniqueValue"

The problem is we never migrated that API to the .NET components. Your only option is to use real tables. Because CR cannot get access to those tempdb tables under the local user account. And there are no API's that will allow Cr to set location to them.

One option using the code below is to convert to a command object. Then you may gain access to the tempdb:

Hereu2019s my code to convert your report from direct table connection to a Command object. What this allows you to do now is make any change to the SQL statement that you used to do in the RDC version of your app:

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using CrystalDecisions.ReportAppServer.ClientDoc;

using CrystalDecisions.ReportAppServer.Controllers;

using CrystalDecisions.ReportAppServer.ReportDefModel;

using CrystalDecisions.ReportAppServer.CommonControls;

using CrystalDecisions.ReportAppServer.CommLayer;

using CrystalDecisions.ReportAppServer.CommonObjectModel;

using CrystalDecisions.ReportAppServer.ObjectFactory;

using CrystalDecisions.ReportAppServer.DataSetConversion;

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.ReportSource;

using CrystalDecisions.Windows.Forms;

public class frmMain : System.Windows.Forms.Form

{

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rptClientDoc;

u2026u2026u2026u2026u2026u2026u2026.

private void TableToCommand_Click(object sender, EventArgs e)

{

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

ISCDReportClientDocument rcd;

rcd = rptClientDoc;

rptClientDoc.DatabaseController.LogonEx("DSN Name", "xtreme", "sa", "password");

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConninfo;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

CrystalDecisions.ReportAppServer.DataDefModel.Table oldTbl;

CrystalDecisions.ReportAppServer.DataDefModel.CommandTable newTbl = new CommandTable();

oldTbl = (CrystalDecisions.ReportAppServer.DataDefModel.Table)rcd.Database.Tables[0];

oldConninfo = oldTbl.ConnectionInfo.Clone(true);

GroupPath gp = new GroupPath();

gp.FromString("");

string sql = String.Empty;

rptClientDoc.RowsetController.GetSQLStatement(gp, out sql);

PropertyBag QEProps = new PropertyBag();

PropertyBag logonProps = new PropertyBag();

//Set the attributes for the logonPropsBag

logonProps.Add("Database", "xtreme");

logonProps.Add("DSN", "DSN Name");

logonProps.Add("UseDSNProperties", "False");

//Set the attributes

QEProps.Add("Database DLL", "crdb_odbc.dll");

QEProps.Add("QE_DatabaseName", "xtreme");

QEProps.Add("QE_DatabaseType", "ODBC (RDO)");

//Add the QE_LogonProperties we set in the logonProps Object

QEProps.Add("QE_LogonProperties", logonProps);

QEProps.Add("QE_ServerDescription", "Dwilliams1 - VAN-W-13-DWILLI");

QEProps.Add("QE_SQLDB", "True");

QEProps.Add("SSO Enabled", "False");

newConnInfo.Attributes = QEProps;

newConnInfo.UserName = "sa";

newConnInfo.Password = "password";

newConnInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

newTbl.ConnectionInfo = newConnInfo;

newTbl.CommandText = sql;

newTbl.Name = "Command";

//foreach(CrystalDecisions.ReportAppServer.DataDefModel.Table tbl in rcd.Database.Tables)

// use the above line for more than one table if required, Also note the Table[0] index needs to be updated also.

{

//oldTbl = tbl.Clone(true);

rcd.DatabaseController.SetTableLocationEx(oldTbl,newTbl);

oldTbl =(CrystalDecisions.ReportAppServer.DataDefModel.Table) rcd.Database.Tables[0].Clone(true);

rcd.DatabaseController.SetTableLocationEx(oldTbl, newTbl);

}

// save the report to a new folder

rcd.SaveAs(rcd.DisplayName, "c:
Updated
", 0);

}

[patrick_simons2 · Accepted solution]

Thanks Don,

I never used neither the LogSQLServerWithPrivateInfo() API nor ODBC. In the report (OLEDB to MS SQL) I have a real table, let's say "WTable" which will be translated to "tempdb.dbo.##Table".

here my simplified "old" VB6-RDC code:

Private Function SetTableLocation(oCReport As CRAXDRT.Report) As Boolean
    Dim iCharPos As Integer
    Dim iCounter As Integer
    Dim sNewDB As String
    Dim sNewTable As String
    Dim oCRTable As CRAXDRT.DatabaseTable

    SetTableLocation = True
    
    For Each oCRTable In oCReport.Database.Tables
        If Left(UCase(oCRTable.Location), 2) = "WT" Then
            ' MS-SQL ##-table
            sNewDB = oCRTable.LogOnDatabaseName
            sNewTable = "tempdb.dbo.##" & Mid(oCRTable.Location, 2) & sUniqueVal
        Else
            sNewDB = oCRTable.LogOnDatabaseName
            'sNewDB = GetNewDBName()
            sNewTable = sNewDB & ".dbo." & oCRTable.Location
        End If
        
        oCRTable.ConnectionProperties.Item("Data Source").Value = "servername"
        oCRTable.ConnectionProperties.Item("Initial Catalog").Value = sNewDB
        oCRTable.ConnectionProperties.Item("User ID").Value = "user"
        oCRTable.ConnectionProperties.Item("Password").Value = "pwd"
        oCRTable.ConnectionProperties.Item("Connect Timeout").Value = 300
        
        oCRTable.Location = sNewTable

        If Not oCRTable.TestConnectivity Then
            SetTableLocation = False
            Exit For
        End If
    Next
End Function

That function did the trick. Your sample uses RAS, is this really needed?

My sample (see 1st mail above) works, but I want to know how it could be that some values are reset? What is the relation between my 1st and 2nd try?

Thanks,

Patrick

[former_member11696 · Accepted solution]

Hi Pat,

Very interesting. TEMPDB is locked by MS SQL Server so not sure how this is working but depends on how you have SQL Server configured and how you created the report to start with on those temp DB's. The only way I've ever been able to connect to them is piggy backing on the HDBC handle. Interesting that OLE DB allows it, I'll have to do more testing with this.

in the mean time RAS is the replacement for the RDC. It has much more power than the report engine does, although you can use both in the same app, I suggest you use the ReportClientDocument, lot more power with RCAPI functionality. You can create your own report designer.

Only thing I can think of as to why values are being reset is typically due to the order the API's are called. If at some point verifying or using the reports log on info will reset/overwrite any changes you have done.

Verify Database will enact the Auto Mapping function which will delete any field/parameter/formula references it can't verify.

What you may want to do is NOT use the Verify command and re-test.

Thanks again

Don

[patrick_simons2 · Accepted solution]

Tx Don,

I'm trying your RAS-sample.

BTW what is the difference between SetTableLocationEx() and SetTableLocation()?

Where can I find the official Help (online) on RAS-methods?

Thanks,

Patrick

Edited by: PatSim on Sep 24, 2010 2:41 PM

[former_member183750 · Accepted solution]

See the API Reference here:

http://help.sap.com/businessobject/product_guides/boexir31/en/rassdk_net_apiRef_12_en.chm

Other developer help files:

http://www.sdn.sap.com/irj/boc/sdklibrary

Ludek