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:
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");
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
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.