ADO OLE Connection with ASP C#.NET for Crystal 2008

We have been using the ODBC connection to use the Crystal Viewer in our .NET programs,  specfically C#.NET (Aspx).  Does anyone had the integrated security connection to the Crystal Report written in C#.  At this point I have this.... But I keep getting failure to login to the database on the SQL connection, which is ADO OLE.  I cannot seem to find an example of the code for this type of connection to Crystal in C#.

Thank you in Advance!!

 // Connect to Crystal Report
        ConnectionInfo connectionInfo = new ConnectionInfo();
        connectionInfo.ServerName = "ServerName";
        connectionInfo.DatabaseName = "DatabaseName";
        //Using windows authenication
        // connectionInfo.userid = XXXXX; Don't use
        // connectionInfo.password = XXXXX; Don't use
        SetDBLogonForReport(connectionInfo);

        crystalReportViewer.ReportSource = SecurityIntakeReport;
        Session["SecurityIntakeReport"] = SecurityIntakeReport;
kimmie8000Asked:
Who is Participating?
 
kimmie8000Connect With a Mentor Author Commented:
I figured this out on my own.

This is how you would want to do this without an ODBC!

How to use OLE DB ADO with embedded Crystal 2008 Report?

 

In the Crystal 2008 report, you go into the menu option “Database”.  Click on Database.  There are other ways to get there, but this is just for example.  

 

It will give you options.

 

Database Expert

Set Database Location

Log on or off server

 

Verify Database

Show SQL Query

Perform groupings on server

Select distinct records

 

_---------------------------------------------------------------------

 

You will want to click Set Database Location.  

 

The provider on the database location must be sqloledb, not sqlncli for ADO to work on servers.

 

To change this, do the following:

1.  Select Set Database Location

 

2.  Expanded the + sign on the Properties option below the server name.  You will need to do this for the “Subreports”, as well.  On the Subreports, you will click the + sign on the server name first.  Then you will the + on the Properties.  At this point, you can look at the connections properties.

 

Please note that that the Database Type: OLE DB (ADO), the provider is SQLOLEDB.

 

We are using integrated security, so this is set to, True in the report, as well.  

 

Leave this rest of the “Properties” as default.

If any of these properties are not correct, all you have to do I highlight the property and right click to edit the property.  

You don’t have to do a universal database location change, which can cause problems if you are jumping from server to server.  This is much easier.

Now instead of putting the ODBC connection for the report connection in the .cs code, you can place it in the web.config file for the C#.NET code, which will look like this.

// Connect to Crystal Report place directly in C# code.

        ConnectionInfo connectionInfo = new ConnectionInfo();

        connectionInfo.ServerName = "Your Server Name";

        connectionInfo.DatabaseName = "Your Database Name";

        connectionInfo.IntegratedSecurity = true;

        //Using windows authenication

        // connectionInfo.userid = XXXXX;

        // connectionInfo.password = XXXXX;

        SetDBLogonForReport(connectionInfo);

 

Now, we can do a connection information string in the web.config file.  

This way if we need to change the server name it is not a hassle.  However, you will still need to change the server name (connection) on the report(s), or the report(s) will fail to load properly.  

The web.config file will look like this for the connection information.

Now you do not need an extra ODBC connection to the server!


Web.Config

Add the following to the web.config so that then you can change the database in the config, and not have to recompile the code, if these values change.

<!-- connection string -->

              <add key="OSFServerName" value="xxx-xxx-xxxx" />

              <add key="OSFDBaseName" value="DatabaseName" />

// C# programming code.

             

  ConnectionInfo connectionInfo = new ConnectionInfo();

        connectionInfo.ServerName = WebConfigurationManager.AppSettings["OSFServerName"];

        connectionInfo.DatabaseName = WebConfigurationManager.AppSettings["OSFDBaseName"];

        SetDBLogonForReport(connectionInfo);

 

Summary

This was stumbled on by accident, because we had been doing OLE DB (ADO) for Business objects.  In my rush to get a report for a new program.  I accidently clicked the ADO instead of the ODBC.  

This technique eliminated the extra step of creating an ODBC connection every time we create a Crystal report that is embedded in the C#.NET/ASPX.

The things that have to be "so" in the report Database Expert in Crystal 2008!

provider = sqloledb

Datasoure = ServerName

IntialCatalog = DatabaseName

IntegratedSecurity = true

I hope this helps..... You will get the generic Load Report Failed, if this is done incorrectly.  A few hours of that will drive you insane.  

0
 
kimmie8000Author Commented:
I was able to figure this out on my own.  I thought I would share with the rest of the EE commnunity being that I been with you guys since your web site went live.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.