how to fill crystal report with sql data

Question

0.00/5 (No votes)

See more:

hi all

i want to fill crystal report with sql data..

i am using sql server 2008 and vs 2010.
i had set a dataset namely report.xsd with 13 field namely dprt1, dprt2....dprt 13.

and alse had set crystal report parameters as col1,col2....col13.
the problem is with filling data.
the problem happens with the code(its not showing reprt)

report ds = new report();
c27.da.Fill(ds, "dynam_report");//error is showed here.
objRpt.SetDataSource(ds);
CrystalReportViewer1.ReportSource = objRpt;

the full code i used is as follows:

C#

protected void bt_rep0_Click(object sender, EventArgs e)
    {
        objRpt = new CrystalReport2();
        string query = CreateSelectQueryAndParameters();
        if (!query.Contains("drpt"))
        {

            return;
        }
        try
        {
            c27.cmd = new System.Data.SqlClient.SqlCommand(query, c27.connect());
            c27.da = new System.Data.SqlClient.SqlDataAdapter(query, c27.connect());
            report ds = new report();
            c27.da.Fill(ds, "dynam_report");//error is showed here.
            objRpt.SetDataSource(ds);
            CrystalReportViewer1.ReportSource = objRpt;


        }
        catch (OleDbException oleEx)
        {
            Label37.Text = oleEx.ToString();
        }
        catch (Exception Ex)
        {
            Label38.Text = Ex.ToString();
        }
    }

function for selecting needed fields in the report

C#

private string CreateSelectQueryAndParameters()
   {
       ReportDocument reportDocument;
       ParameterFields paramfields;

       ParameterField paramfield;
       ParameterDiscreteValue paramDiscreteValue;

       reportDocument = new ReportDocument();
       paramfields = new ParameterFields();
       string query = "SELECT";
       int coloumnNo = 0;

       if (cbproj_type.Checked)
       {
           coloumnNo++;
           query = query.Insert(query.Length, " pt.[proj_type_ff] as drpt" + coloumnNo.ToString());
           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Project Type";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_projno.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, " m.[proj_no] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Project NO";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }

       if (cb_proj_prop.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, " m.[proj_prop_no] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Proposal NO";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }

       if (cb_title.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, " m.[Proj_title] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Title";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_pinvest.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, " pr.[investigator_name] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Principal Investigator";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_assinvest.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, " pa.[investigator_name] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Associate Investigator";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_fund.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, " fa.[fund_agency_acronym] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Funding Agency";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_Budget.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, " m.[Budg] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Budget";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_sdate.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, "m.[from_date] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Start Date";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_cdate.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, "m.[close_date] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "End Date";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_ext.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, "pe.[ext_date] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Extension Date";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_rep.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, "m.[RptNo] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Report Number";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       if (cb_proj_stat.Checked)
       {
           coloumnNo++;
           if (query.Contains("drpt"))
           {
               query = query.Insert(query.Length, ",");
           }
           query = query.Insert(query.Length, "m.[status] as drpt" + coloumnNo.ToString());
           coloumnNo.ToString();

           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "Status";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }

       for (int i = coloumnNo; i < 5; i++)
       {
           coloumnNo++;
           paramfield = new ParameterField();
           paramfield.Name = "col" + coloumnNo.ToString();
           paramDiscreteValue = new ParameterDiscreteValue();
           paramDiscreteValue.Value = "";
           paramfield.CurrentValues.Add(paramDiscreteValue);
           paramfields.Add(paramfield);
       }
       CrystalReportViewer1.ParameterFieldInfo = paramfields;

       query += "FROM [monitor].[dbo] .[MENU] m LEFT OUTER JOIN [monitor].[dbo].[pro_investigator] pr ON m.[investgator_id] = pr.[investgator_id] LEFT OUTER JOIN [monitor].[dbo] .[project_type] pt ON m.[proj_type_id]  = pt.[proj_type_id] LEFT OUTER JOIN [monitor] .[dbo] .[project_AItable] pa on m.[proj_ID] = pa.[proj_ID] LEFT OUTER JOIN [monitor].[dbo].[project_FAtable] pf on m.[proj_ID] = pf.[proj_ID] LEFT OUTER JOIN [monitor] .[dbo] .[fund_agency] fa on pf.[fund_agency_id] = fa.[fund_agency_id] LEFT OUTER JOIN [monitor] .[dbo] .[project_ext] pe on m.[proj_ID] = pe.[proj_ID] order by m.[proj_ID]";
       return query;

   }

can any one help me to solve the problem.
thanks in advance.

Posted 26-Apr-12 23:32pm

Ragi Gopi

Updated 26-Apr-12 23:49pm

v2

Add a Solution

Comments

Sandeep Mewara 27-Apr-12 5:42am

You failed to mention what is the problem.

Further, format your code part with PRE tags such that it is readable. Use Improve Question link.

Ragi Gopi 27-Apr-12 5:49am

question improved...

the error is
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'monitor'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at RME_rme_default.bt_rep0_Click(Object sender, EventArgs e) in f:\workson\Virtual_Office_KFRI\RME\rme_default.aspx.cs:line 820

Ragi Gopi 27-Apr-12 5:50am

i had no friends using crystal reports that's y i am asking help here..

m@dhu 27-Apr-12 6:13am

Nothing wrong in asking here. Members will not be interested in reading complete code, you should have post only the code where you are facing the issue.

m@dhu 27-Apr-12 6:16am

Incorrect syntax near 'monitor'.
Some syntax error in your sql query. It would be easy to trace if you can check the query in sql query analyzer.

Ragi Gopi 27-Apr-12 6:24am

the query had worked when populated the grid view...
i think the error with the part
c27.cmd = new System.Data.SqlClient.SqlCommand(query, c27.connect());
c27.da = new System.Data.SqlClient.SqlDataAdapter(query, c27.connect());
report ds = new report();
c27.da.Fill(ds, "dynam_report");//error is showed here.
objRpt.SetDataSource(ds);
CrystalReportViewer1.ReportSource = objRpt;

Ragi Gopi 27-Apr-12 6:24am

were report is dataset.xsd

Ragi Gopi 27-Apr-12 6:45am

now the error is

CrystalDecisions.CrystalReports.Engine.DataSourceException: The report has no tables. at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSourceInternal(Object val, Type type) at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSource(DataSet dataSet) at RME_rme_default.bt_rep0_Click(Object sender, EventArgs e) in f:\workson\Virtual_Office_KFRI\RME\rme_default.aspx.cs:line 821

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)