how to bind crystal report from multiple datatables using one dataset in asp.net c#

Question

0.00/5 (No votes)

See more:

C#

ASP.NET

reporting

how to bind crystal report from multiple datatables using one dataset in asp.net c#?

Posted 22-Aug-12 20:44pm

veenusethi

Add a Solution

2 solutions

Solution 1

Dear , you need to create a Add Command for that and in that you specify Union Query for that.
this done in using the ADD COMMAND...

and another thing is that "You need to Create Join Query for Return the Value from Different Table"

for Example..this example shows different from different database...

select * from db1.dbo.cust_list where Condtion
union all
select * from db2.dbo.cust_list where condition
union all
select * from db3.dbo.cust_list where condition

make sure your Query Returns same number of Columns from all database..

if any query, please post it....

Posted 22-Aug-12 20:58pm

Mits Machhi

Updated 22-Aug-12 21:04pm

v3

Comments

[no name] 23-Aug-12 3:58am

+5

[no name] 23-Aug-12 4:16am

thanks Sourav..

veenusethi 23-Aug-12 4:56am

I want to use two datatables like this

string query = " select saleitemadd.*,companymaster.address etc... wherecompanyname='"+HiddenField1.Value +"'";
con = new SqlConnection(strConn);
SqlCommand com = new SqlCommand(query, con);
com.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(ds,"saleitemadd");
rptDoc.Load(Server.MapPath("salereport.rpt"));
rptDoc.SetDataSource(ds.Tables["saleitemadd"]);// but here problem is occur ?

i don't know how take two or more than two datatable from one?
companymaster and saleitemad tables have a pk-fk relation.
please help me how to show crystal report using more than datarables?

saleitemadd and companymaster are tableadapters

veenusethi 23-Aug-12 4:57am

and by the way thanks for ur reply

[no name] 23-Aug-12 5:01am

you must use the Join Query in that and use Common field which is in both the table in Where Claue

veenusethi 24-Aug-12 1:15am

i used join query but in crystal report nothing display.
in this line table name in "" is complusory or not
da.Fill(ds,"saleitemadd");
rptDoc.SetDataSource(ds.Tables["saleitemadd"]);// here ds.tables[""] is complusory or not reply me please sir.

[no name] 24-Aug-12 4:23am

you also do this :
da.fill(ds);
rptDoc.SetDataSource(ds);

veenusethi 25-Aug-12 0:17am

yes i did but nothing happened.
crystalreport do not show data . no any error

[no name] 25-Aug-12 1:23am

first make you try your query in SQL Server Query Analyzer or Debug the Code. you get the Result of your query. if none returns , you understand what you done mistake in that query.

veenusethi 25-Aug-12 1:29am

it is a code which i used
string query = " select saleitemadd.item,saleitemadd.date1,saleitemadd.party, saleitemadd.vch, companymaster.address from saleitemadd INNER JOIN companymaster ON saleitemadd.companyname=companymaster.companyname where (saleitemadd.companyname='"+hdcompany.Value +"')";
con = new SqlConnection(strConn);
SqlCommand com = new SqlCommand(query, con);
com.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(ds);
rptDoc.Load(Server.MapPath("saleitembillreport.rpt"));
rptDoc.SetDataSource(ds);

[no name] 25-Aug-12 1:37am

just analyze this Query on your SQL Server.
select saleitemadd.item,saleitemadd.date1,saleitemadd.party, saleitemadd.vch, companymaster.address from saleitemadd INNER JOIN companymaster ON saleitemadd.companyname=companymaster.companyname where (saleitemadd.companyname='abc_company')

in where condition you write some manually company name

[no name] 25-Aug-12 1:43am

you got the company name when you write this :
saleitemadd.companyname='"+hdcompany.Value +"'

veenusethi 25-Aug-12 1:55am

In query analyzer i got result here is no any issue

veenusethi 25-Aug-12 1:55am

and yes i got companyname from saleitemass.companyname='"+hdcompany.value+"'

[no name] 25-Aug-12 1:56am

just modify the Query :
string query = " select saleitemadd.item,saleitemadd.date1,saleitemadd.party, saleitemadd.vch, companymaster.address from saleitemadd INNER JOIN companymaster ON saleitemadd.companyname=companymaster.companyname where (saleitemadd.companyname='"+hdcompany.Selecteditem.Value+"')";

veenusethi 25-Aug-12 1:59am

hdcompany is a hiddenfield and their value is coming using sessions

veenusethi 27-Aug-12 0:14am

Thanks Mits for trying to solve my problem. but now i solved myself

[no name] 27-Aug-12 1:02am

Great. but please post it the solution that helps for other in Future.. and My 5+ to you to solve yourselff....

veenusethi 27-Aug-12 2:49am

Thank You sir i posted it i am new here so in solution if any mistake then i am sorry

[no name] 27-Aug-12 2:59am

nothing To say SOrry Dearrr.. We are face same when freshers in ASP.NET... and we are ready to try our best for solve the Problem ... Thnks

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)

veenusethi · Accepted Answer · 2012-08-26T18:32:00

Crystal reports create so much problems for me. one of them was how to bnd crystal report from multiple datatables using one dataset?
but now i solved this problem myself
1) firstly i was using joins in query but in datasource i was unable to give table name
then i create view in which i use join query
join query is:-

SQL

SELECT        companymaster.address, companymaster.tin, saleitemadd.vch, saleitemadd.item, saleitemadd.qty, saleitemadd.rate,
                         saleitemadd.amount, saleitemadd.tax, saleitemadd.totaltax, saleitemadd.totalamount, saleitemadd.companyname, saleitemadd.party,
                         saleitemadd.date1, saleitemadd.discount
FROM            companymaster INNER JOIN
                         saleitemadd ON companymaster.companyname = saleitemadd.companyname AND
                         companymaster.companyname = saleitemadd.companyname

and then i execute query and save view and then in dataset i add tableadapter and then choose view tab and then select my view table.

And after that i create crystal report and i call dataset and select view table

and in c# i use these lines of code
public partial class saleitemvoucherbill : System.Web.UI.Page
{
string strConn;
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet("salereport");
private ReportDocument rptDoc = null;
protected void Page_Load(object sender, EventArgs e)
{
strConn = ConfigurationManager.ConnectionStrings["ConnectionASPX"].ConnectionString;
con = new SqlConnection(strConn);
con.Open();
if (Session["cmpny"] != null)
hdcompany.Value = Session["cmpny"].ToString();
this.rptDoc = new ReportDocument();
if (Session["vchbill"] != null)
hdbill.Value = Session["vchbill"].ToString();
if (Session["date"] != null)
date.Value = Session["date"].ToString();
if (Session["party"] != null)
hdparty.Value = Session["party"].ToString();

string query = " select * from salebillreport where vch='"+hdbill.Value +"' and companyname='"+hdcompany.Value +"'" ;
// in query salebillreport is view name and hdbill.value and hdcompany.value are hiddenfields and here values are coming through sessions
con = new SqlConnection(strConn);
SqlCommand com = new SqlCommand(query, con);
com.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(ds,"salebillreport");
rptDoc.Load(Server.MapPath("saleitembillreport.rpt"));
rptDoc.SetDataSource(ds);
//parameter pass
DateTime fromdate = Convert.ToDateTime(date.Value);
rptDoc.SetParameterValue("date", fromdate);
rptDoc.SetParameterValue("party", hdparty.Value);
rptDoc.SetParameterValue("company", hdcompany.Value);
rptDoc.SetParameterValue("bill", hdbill.Value);
//// this code is used for clear the report after selection means refreshing the report
CrystalReportViewer1.ParameterFieldInfo.Clear();
CrystalReportViewer1.ReportSource = rptDoc;

}

Thank you if You like this please vote for me
Happy coding