[AnalyticLunatic]

Hello Everyone! Hopefully someone with more experience in this material can assist me

Using SQLConnection/Commands in my code I have successfully filled an ObservableCollection using my DataBase Query. What I want to do now is use the values in my collection as the source data for a Crystal Report.

I have as of yet been unsuccessful in finding a best or even functioning solution to my issue. I had thought to process the values into a Solution DataTable created using the DataSet Designer and have that as my Report Data Source, but, haven't figured out how to process the values into the DataTable.

ObservableCollection Values Example:
"1", "1", "5W20"
"2", "2", "5W30"
"3", "3", "10W40"

In the end game I am trying to make a Crystal Report which, when printed, will be pull-&-stick labels (12 per page). Each of the 3 values (or each row) will be on 1 label.

Thanks, and I'll be watching for any replies while I keep tackling from my end!

[Skydiver]

According to this StackOverflow response, Crystal Reports' SetDataSource() takes an IEnumerable. Since the ObservableCollection is an IEnumerable, things should just work.

http://stackoverflow...eports-and-linq

[AnalyticLunatic]

Skydiver, on 08 January 2013 - 02:58 PM, said:

According to this StackOverflow response, Crystal Reports' SetDataSource() takes an IEnumerable. Since the ObservableCollection is an IEnumerable, things should just work.

http://stackoverflow...eports-and-linq

Thank You Skydiver. I have come across that post before, but I'm not trying to just set the DataSource in code and have the report generate. Because I need 12 labels (each as it's own record), I need to move my data from the Observable Collection into a solution DataTable that I can use as the Source on the Crystal Report in Visual Studio and drag the records into correct position.

Theory (Columns in DataTable as Report Source):

BottleNum1, SampleNum1, Viscosity
BottleNum2, SampleNum2, Viscosity
BottleNum3, SampleNum3, Viscosity
BottleNum4, SampleNum4, Viscosity
BottleNum5, SampleNum5, Viscosity
BottleNum6, SampleNum6, Viscosity
BottleNum7, SampleNum7, Viscosity
BottleNum8, SampleNum8, Viscosity
BottleNum9, SampleNum9, Viscosity
BottleNum10, SampleNum10, Viscosity
BottleNum11, SampleNum11, Viscosity
BottleNum12, SampleNum12, Viscosity

By setting the DataTable thus, I can drag each field to the appropriate place on my Report, and for example when I have 13 records, in code I will set the 13th as a new record in column 1, which will cause the second Page to populate with that single record.

What I need assistance with is moving my data from the Observable Collection into a DataTable.

[Skydiver]

So what is wrong with this pseudo code:

var table = new DataTable();

// Add table columns...

// Fill table rows...
foreach(var item in observableCollection)
{
    row = table.NewRow();
    row['Bottle'].Value = item.Bottle;
    row['Sample'].Value = item.Sample;
    row['Viscosity'].Value = item.Viscosity;
    table.Rows.Add(row);
}

[AnalyticLunatic]

Skydiver, on 09 January 2013 - 09:37 AM, said:

So what is wrong with this pseudo code:

var table = new DataTable();

// Add table columns...

// Fill table rows...
foreach(var item in observableCollection)
{
    row = table.NewRow();
    row['Bottle'].Value = item.Bottle;
    row['Sample'].Value = item.Sample;
    row['Viscosity'].Value = item.Viscosity;
    table.Rows.Add(row);
}

*Facepalm* Yep... that will do it. I knew it had to be simpler than I was making it. Visual Studio did require me to use double quotes (") instead of single quotes (') though.

Now I have an issue with displaying the data. I have confirmed the records are correctly adding to the DataTable, and that the correct report is being loaded. However, the report is displaying no data in the fields I have dragged onto the Report. It is blank aside from Static Text on the Report. I have also confirmed the DataSource Location for the Report is pointing to my Solution Dataset. Do you spot anything I am doing wrong?

        private void frmReportViewer_Load(object sender, EventArgs e)
        {
            try
            {
                switch (_report_type)
                {
                    case "SampleLabels":

                        ObservableCollection<rptSample> rptSample = new ObservableCollection<rptSample>();

                        SqlConnection conn = null;
                        conn = new SqlConnection(MY-CONNECTION);
                        conn.Open();
                        
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = (MY-QUERY);
                        
                        SqlDataReader myReader = null;
                        myReader = cmd.ExecuteReader();

                        while (myReader.Read())
                        {
                            //rptSample.Add(new rptSample(Convert.ToInt32(myReader["BottleNumber"]), Convert.ToInt32(myReader["SampleNumber"]), myReader["Viscosity"].ToString());
                            rptSample.Add(new rptSample(myReader["BottleNumber"].ToString(), myReader["SampleNumber"].ToString(), myReader["Viscosity"].ToString()));
                        }

                        // Database is empty at the moment, lines below
                        // are purely for testing purposes until Data input.
                        rptSample.Add(new rptSample("1", "1", "5W20"));
                        rptSample.Add(new rptSample("2", "2", "5W30"));
                        rptSample.Add(new rptSample("3", "3", "10W40"));
                        rptSample.Add(new rptSample("4", "4", "5W50"));
                        rptSample.Add(new rptSample("5", "5", "5W60"));
                        rptSample.Add(new rptSample("6", "6", "10W70"));
                        rptSample.Add(new rptSample("7", "7", "5W80"));
                        rptSample.Add(new rptSample("8", "8", "10W90"));
                        rptSample.Add(new rptSample("9", "9", "5W10"));
                        rptSample.Add(new rptSample("10", "10", "5W20"));
                        rptSample.Add(new rptSample("11", "11", "5W30"));
                        rptSample.Add(new rptSample("12", "12", "10W40"));
                        rptSample.Add(new rptSample("13", "13", "10W50"));

                        MessageBox.Show(rptSample.Count.ToString());

                        DataTable tbl = new wm_MOSS_Chemist.dsSample.SampleLabelsDataTable();
                        DataRow row = tbl.NewRow();
                        int count = 0;
                        int totalCnt = 0;
                        foreach (var item in rptSample)
                        {
                            count++;
                            totalCnt++;
                            row["BottleNum"+count] = item.BottleNum;
                            row["SampleNum"+count] = item.SampleNum;
                            row["Viscosity"+count] = item.Viscosity;
                            if (count.CompareTo(12) == 0)
                            {
                                tbl.Rows.Add(row);
                                row = tbl.NewRow();
                                count = 1;

                            }
                            if (totalCnt == rptSample.Count())
                            {
                                tbl.Rows.Add(row);
                            }
                        } // End ForEach Loop

                        tbl.AcceptChanges();

                            //MessageBox.Show(row["BottleNum1"].ToString());
                            //MessageBox.Show(row["SampleNum1"].ToString());
                            //MessageBox.Show(row["Viscosity1"].ToString());
                            //MessageBox.Show(row["BottleNum2"].ToString());
                            //MessageBox.Show(row["SampleNum2"].ToString());
                            //MessageBox.Show(row["Viscosity2"].ToString());
                            //MessageBox.Show(row["BottleNum12"].ToString());
                            //MessageBox.Show(row["SampleNum12"].ToString());
                            //MessageBox.Show(row["Viscosity12"].ToString());

                        RPT = new Reports.rptSampleLabels();
                        RPT.Load("rptSampleLabels.rpt");
                        rptViewer.ReportSource = RPT;

                        //int i = 1;
                        //if (i == 1)
                        //{

                        //} else {
                        //    //
                        //}
                        break;
                } // End switch
            } // End try

            catch (InvalidOperationException x)
            {
                if (x.Source != null)
                {
                    MessageBox.Show("IOException source: {0}", x.Source);
                }
            } // End catch
            catch (SqlException y)
            {
                MessageBox.Show("SqlException Source: {0}", y.Source);
            } // End catch
            catch (ArgumentException z)
            {
                MessageBox.Show("Argument Exception Source: {0}", z.Source);
            } // End catch
        
        } // End frmReportView_Load()

I tried uploading images to be a little clearer, but as you probably know, DIC is having Image Upload Server issues. />

Linked Images:






EDIT: Typo.

This post has been edited by AnalyticLunatic: 10 January 2013 - 08:52 AM

[AnalyticLunatic]

Just thought I would post this for anyone who might find it useful in the future. As for my last mentioned issue, it appears I was setting the ReportViewer Controls ReportSource, but never the DataSource for the Report itself, thus, it was blank (go figure? ).

This did give me a new Error however about a missing .dll file in my SAP Business Crystal Reports directory.

Full break down in below article:

Missing .dll Exception when setting CrystalReport DataSource in VS2010

Quick fix is to add the missing folder/.dll file to the listed directory in the Exception Error, and then add the following to the app.config file:

<startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>

A big thank you to Skydiver for all his help!

This post has been edited by AnalyticLunatic: 10 January 2013 - 10:32 AM