Crystal Reports Connection Provider

Question

5.00/5 (1 vote)

See more:

Hi All,

I've an application which has to work both sql server and oracle databases. So when I create crystal reports, I've to select provider for the connection. How can I manage it to make my reports compatible with both databases. Is there any way to set provider from code behind? Can I set provider dynamically through code? Is there any alternate ways???
Please give solution.... :doh: :doh: :doh:

Is there any way to pass connection object ya connection string to SetDatabaseLogon() function instead of username and password? Or is there any alternate way to change the connection at runtime?:confused::confused::confused:

Posted 9-Jun-10 21:11pm

jisha Johnson

Updated 10-Jun-10 20:23pm

v2

Add a Solution

Comments

jisha Johnson 11-Jun-10 1:35am

Can anyone say about dataproviders in crystal reports??

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

V. · Answer 1 · 2010-06-10T22:27:00

Solution 1

I did it as follows:

public ReportDocument GetReportDocument(string reportid){
    bool success = GetReportData(reportid) && GetDBData();
    ReportDocument document = null;
    if(success == true){
        //create a document
        document = new ReportDocument();
        //Load it.
        document.Load(report.Path);
        //set the logon credentials for the database.
        document.SetDatabaseLogon(databaseobject.UserName, databaseobject.Password, databaseobject.Server, databaseobject.Database);
    }                                               //end if
    else{
        document = null;
    }                                               //end else
    return document;
}                                                   //end method GetReportDocument

the only thing you need to do is have the information for the database and additionally make sure the structure of the used data is the same for both databases.

I hope this helps.

Posted 10-Jun-10 22:27pm

V.

Comments

jisha Johnson 11-Jun-10 4:41am

What about design? How u made the report in design? Using dataset ya normal pull method? Please explain

V. 11-Jun-10 5:35am

I made a report with the application Crystal Reports, not via code, I Load the report with "document.Load(report.Path);" (see code snippet)
How much experience do you have with Crystal Reports? This looks like basic stuff to me.

jisha Johnson 11-Jun-10 6:32am

Ya I'm new to crystal report. I'm making my first report. I've done all these. I've made the report using oracle tables. Bt my problem is that I've to work this report both in sql server and oracle. I selected the provider for oracle in design. My report is working now. Bt I want to sure it with sql server also. So I want to know whether I've to change the provider also? If yes how?
I'm using the connection from webconfig file. And one problem is coming when I run the report I've to enter login details again. How can I solve it?
If u,ve time please help me
Thanks for ur reply

V. 11-Jun-10 6:53am

Did you try MSDN ? http://social.msdn.microsoft.com/Search/nl-BE?query=SetDatabaseLogon&ac=8

jisha Johnson 11-Jun-10 8:12am

Bt my problem is that if I select provider in the design for selection of database, Setdatabaselogon cannot change the database and server. It only changes usernbame and password. Now I tried the pull method. I think its suitable for me. Bcoz in this I dnt need to select provider. Bt I've to do more coding. I cant assign the dataset directly. Now I'm fllowing the below process
1. Create dataset
2. Create crystal report
3. Create form and assign dynamically created dataset with my current connection and assign dataset values to the datatable.
I'm attaching the code below

CrystalReport2 rpt = new CrystalReport2();
DataSet ds = new DataSet("DataTable1");
DataTable table = new DataTable("DataTable1");
con.Open();
con.ExecuteReader(CommandType.Text, "select * from VW_NONAIRTIME_INVHDR");
table.Columns.Add("INV_NO", typeof(System.String));
table.Columns.Add("INV_DATE", typeof(System.String));
DataRow dr = table.NewRow();
while (con.DataReader.Read())
{

dr["INV_NO"] = con.DataReader["INV_NO"].ToString();
dr["INV_DATE"] = con.DataReader["INV_DATE"].ToString();
}
con.Close();
table.Rows.Add(dr);
ds.Tables.Add(table);
rpt.SetDataSource(ds);
CrystalReportViewer1.ReportSource = rpt;
rpt.SetDataSource(con.dataSet);
con.Close();
CrystalReportViewer1.ReportSource = rpt;

Bt I want to assign the dyanamically made dataset to the report without the use of dataset object already made.
Is there any alternate way??

V. 11-Jun-10 8:21am

I'm at a loss what you're trying to do... I have the feeling you want the result quickly, but your design seems a little flakey. I suggest you read up on Crystal Reports first before attempting a design for your application.

jisha Johnson 14-Jun-10 0:41am

The above code is working for me. Ya I need the result quickly. I've read about the push method in crystal reports and done in the same way and its working for me also. Now I can connect to the database to which my whole application is connected which is specified in webconfig file. Y it is flekey? Pls say me

Goutam Patra · Answer 2 · 2010-06-11T22:15:00

Solution 2

I would suggest try to make a XSD file in this format

XML

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="dsACCLIST" targetNamespace="http://tempuri.org/ACCList.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/ACCList.xsd" xmlns:mstns="http://tempuri.org/ACCList.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Document">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="ACCLIST">
<xs:complexType>
<xs:sequence>
<xs:element name="ACCCODE" type="xs:string" />
<xs:element name="APPDATE" type="xs:dateTime" />
<xs:element name="CODE" type="xs:string" minOccurs="0" />
<xs:element name="GRPNAME" type="xs:string" />
<xs:element name="GIVENNAME" type="xs:string" />
<xs:element name="SURNAME" type="xs:string" />
<xs:element name="AMOUNT" type="xs:decimal" />
<xs:element name="LGROUP" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="DocumentKey1">
<xs:selector xpath=".//mstns:ACCLIST" />
<xs:field xpath="mstns:ACCCODE" />
</xs:unique>
</xs:element>
</xs:schema>

Then in crystal report use this XSD as data source using ADO.Net datasets.
Then in your projct fetch the data in a dataset.

Name your dataset exactly the same name specified in the XSD file like

ds.Tables(0).TableName = "ACCLIST"

Now set the datasource of the report objectto the dataset you fetched from database earlier like this

rptReport.SetDataSource(ds)

Hope this would help

Posted 11-Jun-10 22:15pm

Goutam Patra

Comments

Henry Minute 12-Jun-10 6:02am

Exactly how will this help the OP to connect to either Oracle or SQLServer?

Goutam Patra 12-Jun-10 7:56am

He has the problem to set diff db source in crystal report. In this Solution he dont need to worry about the diff data sorce for crystal report. He has to fetch the data in his main program only using diff data source. That he can control in his .Net code. Crystal report will work with pre-fetched data only. Thats how it will help him. i hope this will explain how it will help.

jisha Johnson 14-Jun-10 0:45am

I've done using dataset creation and accessing it in the crystal reports. By this I can use datatables. Bt I need to write code for assigning data to each of the fields as follows.

CrystalReport2 rpt = new CrystalReport2();
DataSet ds = new DataSet("DataTable1");
DataTable table = new DataTable("DataTable1");
con.Open();
con.ExecuteReader(CommandType.Text, "select * from VW_NONAIRTIME_INVHDR");
table.Columns.Add("INV_NO", typeof(System.String));
table.Columns.Add("INV_DATE", typeof(System.String));
DataRow dr = table.NewRow();
while (con.DataReader.Read())
{

dr["INV_NO"] = con.DataReader["INV_NO"].ToString();
dr["INV_DATE"] = con.DataReader["INV_DATE"].ToString();
}
con.Close();
table.Rows.Add(dr);
ds.Tables.Add(table);
rpt.SetDataSource(ds);
CrystalReportViewer1.ReportSource = rpt;
rpt.SetDataSource(con.dataSet);
con.Close();
CrystalReportViewer1.ReportSource = rpt;

Is it good ya XML one?

And also I'm not 'he' 'she'

Regards
Jisha Johnson

Goutam Patra 14-Jun-10 4:40am

I did not understand your purpose of the while loop.

If i am not mistaking you are fetching data in a datareader object and copy it into a Dataset. Why dont you just fetch the data in a dataset may be like
Public Function FetchData(ByVal V_QRY As String) As DataSet
Dim oCon As OracleConnection
Dim ds As New DataSet
Try

Dim cmdData As New OracleCommand(V_QRY)
cmdData.CommandType = CommandType.Text
oCon = New OracleConnection(ConString)
oCon.Open()
cmdData.Connection = oCon
cmdData.ExecuteNonQuery()
Dim da As OracleDataAdapter = New OracleDataAdapter(cmdData)
da.SelectCommand = cmdData
da.Fill(ds)
da.Dispose()
cmdData.Dispose()
oCon.Dispose()
Return ds
Catch ex As Exception
Return AddError(ex.Source, 0, ex.Message)
End Try
Return ds
End Function

You can try it once

jisha Johnson 14-Jun-10 5:15am

I've tried with this. I tried to set the dataset directly to the crystal report. Bt it's asking username and password at runtime. After all the above code is working. The process here following is as follows
1. First I created a dataset which contains the datatable DataTable1.
2. I created the report by using the above dataset
3. Then in the form I used the report through reportviewer controller
and wrote the above code.
The above code works as follows.
(1) create dataset and datareader of type DataTable1
(2) Then I made a dataset for my query
(3) And assignes the dataset values one by one to the table
(4) Then assigned it to the dataset(which contains the datatable) and then it to the report
I dnt know y the direct method is not working?
I think its because at the time of database creation we are not giving any connection. Thats y? Please say if any other way for this

Goutam Patra 14-Jun-10 7:54am

There are only 2 things you need to ckeck

1. Have you created the Report using the XML File as data source instead of setting it directly from server.
2. The name of the data set should be exactly the same specified in XSD file

<xs:element name="ACCLIST">

Data set name should be changed after feching data

ds.Tables(0).TableName = "ACCLIST"

If you did those 2 things there should not be any error.

It seems you created the report(RPT fle) setting database object directly.

jisha Johnson 14-Jun-10 8:21am

Ya. I've created dataset object directly and set it to the report. It also dont need of the provider. Thats y I selected this method.I haven't tried XML file. As I told earlier I'm new to crystal report and .Net. Thats y. This method I feel comfortable