• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

how do I get c# mysql query to pass through crystal reports 11?

I have a c# file that should pass the query I have through crystal, but it's not apssing the query through. code below. is there something I need to add in the report to get this to run? I thought it should just pick it up.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web.Design;
 
 
public partial class CapsManagerReport : System.Web.UI.Page
{
 
    string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["Connection"].ToString();
    ReportDocument report = new ReportDocument();
    protected void Page_Load(object sender, EventArgs e)
    {
       
        try
        {
 
            string id = Request.QueryString["Classification"];
 
            if (id != null)
            {
              
 
                OdbcConnection con = new OdbcConnection(ConnStr);
                con.Open();
                //old DB query before actuals table was created to make report more accurate.
               // OdbcCommand cmd = new OdbcCommand("SELECT * FROM projectmanager where Classification=" + id + " AND Classification <> 'EXP' order by projectleader and ordernumber NOT LIKE 'Pending%' and statuspro not LIKE 'clsd' or statuspro is null ", con);
                OdbcCommand cmd = new OdbcCommand("SELECT projectmanager.ordernumber, projectmanager.companycode, projectmanager.responsiblectr, projectmanager.reimbursable, projectmanager.totalplan, projectmanager.ordertype" +
                ", projectmanager.inservicedate, projectmanager.completiondate, projectmanager.system, projectmanager.priority, projectmanager.fcastatus, projectmanager.region, projectmanager.projectleader, projectmanager.commercialleader, projectmanager.wellconnect" +
                ", projectmanager.afestatus, projectmanager.description, projectmanager.shorttext, projectmanager.comments, projectmanager.additionaltext, projectmanager.datetime, projectmanager.rowcomplete, projectmanager.engineeringcomplete" +
                ", projectmanager.constructioncomplete, projectmanager.capspending, projectmanager.Lastdaysapactivity, projectmanager.applidate, projectmanager.IOPlanCIAC, projectmanager.IOPendingCIAC, projectmanager.shorttextold, projectmanager.commentsold" +
                ", projectmanager.additionalcommentsold, projectmanager.materialorder, projectmanager.longtxt, projectmanager.groupleader, projectmanager.Classification, projectmanager.Statuspro, actuals.totalactual, actuals.ordernumber" +
                " FROM projectmanager as projectmanager, actuals as actuals where projectmanager.ordernumber=actuals.ordernumber and projectmanager.Classification=" + id + " AND projectmanager.Classification <> 'EXP' and projectmanager.ordernumber NOT LIKE 'Pending%'" +
                " and projectmanager.statuspro not LIKE 'clsd' or projectmanager.statuspro is null order by projectmanager.projectleader", con);
                OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "etcOpsProjectManagement_DEV");
 
                //   ReportDocument report = new ReportDocument();
                report.Load(Server.MapPath(@"CapsClasificationReport.rpt"));
            
              
               
                
                Class1 ob = new Class1();                        
               
                
                ArrayList arrList = new ArrayList(ob.test());
              
                string d = arrList[3].ToString();
                string a = arrList[9].ToString();
                string b = arrList[5].ToString();
                string c = arrList[7].ToString();
             
               report.SetDatabaseLogon(c, a, d, b, true);
               
                
                report.SetDataSource(ds);
                CrystalReportViewer1.ReportSource = report;
                CrystalReportViewer1.DataBind();
                con.Close();
                
            }
 
            else
            {
                
 
                OdbcConnection con = new OdbcConnection(ConnStr);
                con.Open();
                OdbcCommand cmd = new OdbcCommand("SELECT projectmanager.ordernumber, projectmanager.companycode, projectmanager.responsiblectr, projectmanager.reimbursable, projectmanager.totalplan, projectmanager.ordertype" +
                ", projectmanager.inservicedate, projectmanager.completiondate, projectmanager.system, projectmanager.priority, projectmanager.fcastatus, projectmanager.region, projectmanager.projectleader, projectmanager.commercialleader, projectmanager.wellconnect" +
                ", projectmanager.afestatus, projectmanager.description, projectmanager.shorttext, projectmanager.comments, projectmanager.additionaltext, projectmanager.datetime, projectmanager.rowcomplete, projectmanager.engineeringcomplete" +
                ", projectmanager.constructioncomplete, projectmanager.capspending, projectmanager.Lastdaysapactivity, projectmanager.applidate, projectmanager.IOPlanCIAC, projectmanager.IOPendingCIAC, projectmanager.shorttextold, projectmanager.commentsold" +
                ", projectmanager.additionalcommentsold, projectmanager.capscommentsold, projectmanager.materialorder, projectmanager.longtxt, projectmanager.groupleader, projectmanager.Classification, projectmanager.ActualPriorMonth, projectmanager.Statuspro, actuals.totalactual, actuals.ordernumber" +
                " FROM projectmanager as projectmanager, actuals as actuals where projectmanager.ordernumber=actuals.ordernumber and projectmanager.Classification<>'EXP' AND projectmanager.Classification <> 'EXP'" +
                " and projectmanager.statuspro not LIKE 'clsd' or projectmanager.statuspro is null", con);
                OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "etcOpsProjectManagement_DEV");
 
                //ReportDocument report = new ReportDocument();
                report.Load(Server.MapPath(@"CapsClasificationReport.rpt"));
                //  report.Load(path);
                
                
                Class1 ob = new Class1();
                ArrayList arrList = new ArrayList(ob.test());
              
                string d = arrList[3].ToString();
                string a = arrList[9].ToString();
                string b = arrList[5].ToString();
                string c = arrList[7].ToString();
                
 
                report.SetDatabaseLogon(c, a, d, b, true);
                //report.SetDatabaseLogon("etcopsproj_pxy", "8eThe8all");
                report.SetDataSource(ds);
                CrystalReportViewer1.ReportSource = report;
                CrystalReportViewer1.DataBind();
                con.Close();
 
            }
            
 
        }
        catch (Exception ee)
        {
            Response.Write(ee.Message.ToString()); ;
        }
        CrystalReportViewer1.PageToTreeRatio = 12; 
    }
 
    protected void Page_Unload(object sender, EventArgs e)
    {
        
        report.Close();
        report.Dispose();
 
    }
}

Open in new window

0
bschave2
Asked:
bschave2
1 Solution
 
bschave2Author Commented:
I figured out the solution. it was not reading in because I was using a dataset instead of a data table.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now