Linq2sql Connection string for crystal report. Report requires logon

[Former Member]

If I take out the connection string info the report runs fine from my machine but any other user it asks for logon information so I am trying to add the connection info. The line that is not compiling is `cr1.SetDatabaseLogon(connection, cr1);`

What am I doing wrong? Any help would be appreciated!

    private void launchReport(int pKReport)

    {

        using (DataClasses1DataContext db = new DataClasses1DataContext())

        {

            var query = (from s in db.expenseHdrs

                         join d in db.expenseDtls on s.rptNo equals d.rptNo

                         where s.rptNo == pKReport

                         from g in db.employees

                         join r in db.expenseHdrs on g.pk equals r.empPk

                         select new

                         {

                             s.period,

                             s.description,

                             s.department,

                             s.rptNo,

                             s.reimbursement, g.name,

                             d.expDate,

                             d.expType,

                             d.expDesc

                         });

            CrystalReport1 cr1 = new CrystalReport1();

            ConnectionInfo connection = new ConnectionInfo();

            connection.DatabaseName = "intranet";

            connection.UserID = "sa";

            connection.Password = "*****";

            cr1.SetDatabaseLogon(connection, cr1);

            cr1.SetDataSource(query);

            crystalReportViewer1.ReportSource = cr1;

        }

    }

I also tried changing the code after my query to below but I still have the exact same problem as before when I didnt have the connection string credentials. On every user but myself I get the same sql server login screen and no matter what I enter it fails. I think it is due to there being no database name which it will not allow me to manually enter.

     CrystalReport1 cr1 = new CrystalReport1();

                cr1.FileName = @"C:\Intranet\CrystalReport1.rpt";

                ConnectionInfo connectionInfo = new ConnectionInfo();

                connectionInfo.ServerName = "svr-sql";

                connectionInfo.DatabaseName = "intranet";

                connectionInfo.UserID = "sa";

                connectionInfo.Password = "*****";

                SetDBLogonForReport(connectionInfo, cr1);

                cr1.SetDataSource(query);

                crystalReportViewer1.ReportSource = cr1;

            }

        }

    private void SetDBLogonForReport(ConnectionInfo connectionInfo, CrystalReport reportDocument)

        {

            Tables tables = reportDocument.Database.Tables;

            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)

            {

                TableLogOnInfo tableLogonInfo = table.LogOnInfo;

                tableLogonInfo.ConnectionInfo = connectionInfo;

                table.ApplyLogOnInfo(tableLogonInfo);

            }

        }

[former_member183750 · Accepted solution]

The code I use is as follows:

Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crTableLogOnInfo As TableLogOnInfo
Dim crConnectionInfo As ConnectionInfo
Dim crReportDocument As ReportDocument
CrConnectionInfo = New ConnectionInfo
With crConnectionInfo
.ServerName = "dbconn1"
.DatabaseName = "Pubs"
.UserID = "vantech"
.Password = "vantech"
crDatabase = crReportDocument.Database
crTables = crDatabase.Tables
End With
crDatabase = crReportDocument.Database
crTables = crDatabase.Tables
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectionInfo
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next

This assumes that there are no subreports (or at least that if there are subreports, they use identical db connection info).

My recommendation would be to test the above code with a simple test report (one table, one field). If you have subreports, you may need to set the logon for those also - depends on how the subreports are implemented. Subreport logon code would be something like this:

Dim crSubreportDocument As ReportDocument
crSubreportDocument = crReportDocument.OpenSubreport("Ron")
crConnectionInfo = New ConnectionInfo
With crConnectionInfo
.ServerName = "Rcon1"
.DatabaseName = "Northwind"
.UserID = "vantech"
.Password = "vantech"
End With
crDatabase = crSubreportDocument.Database
crTables = crDatabase.Tables
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectionInfo
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next

Alternatively, you could download the db logon writing utility that is attached to this KB and implement that.

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Thanks for your help.

What I had to do to get rid of the logon was instead of using a straight sql server datasource. I had to change it to OLE DB(ADO) for sql server to get it to work.