How To Create Reports Using Crystal Reports in Visual Studio 2010

Question

I am new to Visual Studio 2010 C#. I'm creating an application which creates a report. The information that will be displayed in the report is from the MySQL Server. I already installed the Crystal Reports. However, I do have some problems with getting data from MySQL because I can't find the data from MySQL. The data shown in the Report Creation Wizard in not the database files in MySQL but the forms I created in C#. Please help.

Screenshots:

Answer 1

DataAccess.Connection.Close();
DataAccess.Connection.Open();
DataAccess.dataAdapter = new SqlDataAdapter(" SELECT sales.qty, sales.ord_date, sales.payterms, stores.stor_name, stores.stor_id, titles.title_id, titles.title, titles.price, (sales.qty * titles.price) AS Total FROM sales INNER JOIN stores ON sales.stor_id = stores.stor_id INNER JOIN  titles ON sales.title_id = titles.title_id", DataAccess.Connection);

DataAccess.dataTable = new DataTable();
DataAccess.dataAdapter.Fill(DataAccess.dataTable);
Bind.DataSource = DataAccess.dataTable;
string temp = DataAccess.dataTable.Rows[0][3].ToString();

Excel.Application xlApp2 = new Excel.Application();
xlApp2.Visible = true;
Excel.Workbook Workbook2 = xlApp2.Workbooks.Add(1);
Excel.Worksheet Worksheet2 = (Excel.Worksheet)Workbook2.Sheets[1];

Worksheet2.Cells[1, 1] = "Sales Report";
Worksheet2.Cells[3, 1] = temp.ToUpper();
Worksheet2.Cells[4, 2] = "Order Date";
Worksheet2.Cells[4, 3] = "Payment Terms";
Worksheet2.Cells[4, 4] = "Store Name";
Worksheet2.Cells[4, 5] = "Store ID";
Worksheet2.Cells[4, 6] = "Title ID";
Worksheet2.Cells[4, 7] = "Book Title";
Worksheet2.Cells[4, 8] = "Unit Price";
Worksheet2.Cells[4, 9] = "Total Price";

int intCount2 = 5;
int TotalRec = 0;
int finecort = 0;
decimal cost = 0;
decimal cost1 = 0;

for (int n = 0; n < DataAccess.dataTable.Rows.Count; n++)
{
    if (DataAccess.dataTable.Rows[n][3].ToString().Equals(temp))
    {
        Worksheet2.Cells[intCount2, "C"] = DataAccess.dataTable.Rows[n][0];
        Worksheet2.Cells[intCount2, "B"] = DataAccess.dataTable.Rows[n][1];
        Worksheet2.Cells[intCount2, "D"] = DataAccess.dataTable.Rows[n][3];
        Worksheet2.Cells[intCount2, "E"] = DataAccess.dataTable.Rows[n][4];
        Worksheet2.Cells[intCount2, "F"] = DataAccess.dataTable.Rows[n][5];
        Worksheet2.Cells[intCount2, "G"] = DataAccess.dataTable.Rows[n][6];
        Worksheet2.Cells[intCount2, "H"] = DataAccess.dataTable.Rows[n][7];
        Worksheet2.Cells[intCount2, "I"] = DataAccess.dataTable.Rows[n][8];

        intCount2++;
        TotalRec++;
        finecort++;
        cost += Convert.ToDecimal(DataAccess.dataTable.Rows[n][8]);
        cost1 += Convert.ToDecimal(DataAccess.dataTable.Rows[n][8]);
    }
    else
    {
        Worksheet2.Cells[intCount2, "B"] = ("Number of records in " + temp + " group are" + TotalRec + " and the cost is R" + cost1);
        TotalRec = 0;
        cost1 = 0;
        temp = DataAccess.dataTable.Rows[n][3].ToString();
        Worksheet2.Cells[intCount2 + 2, "A"] = temp.ToUpper();

        Worksheet2.Cells[intCount2 + 3, "B"] = "Order Date";
        Worksheet2.Cells[intCount2 + 3, "C"] = "Quantity";
        Worksheet2.Cells[intCount2 + 3, "D"] = "Store Name";
        Worksheet2.Cells[intCount2 + 3, "E"] = "Store ID";
        Worksheet2.Cells[intCount2 + 3, "F"] = "Title ID";
        Worksheet2.Cells[intCount2 + 3, "G"] = "Book Title";
        Worksheet2.Cells[intCount2 + 3, "H"] = "Unit Price";
        Worksheet2.Cells[intCount2 + 3, "I"] = "Total Price";

        intCount2 += 4;
    }
    Worksheet2.Cells.Columns.AutoFit();

}

Worksheet2.Cells[intCount2, "B"] = ("Number of records in " + temp + " group=" + TotalRec);
Worksheet2.Cells[intCount2 + 2 ,"A"] = "2013 Sales Report records added upto ";
Worksheet2.Cells[intCount2 + 2, "B"] = finecort;
Worksheet2.Cells[intCount2 + 3, "A"] = "Grrand Total of all records ";
Worksheet2.Cells[intCount2 + 3, "B"] = "R"+cost;
Worksheet2.Range[Worksheet2.Cells[1, "A"], Worksheet2.Cells[1, "I"]].Merge();
Worksheet2.Cells.Columns.AutoFit();
DataAccess.Connection.Close();

Answer 2

        XmlDocument xmlDocument = new XmlDocument();
        string xmlFileName = (@"C:");
        xmlDocument.Load(xmlFileName);

        XmlElement xItem = xmlDocument.CreateElement("item");


        XmlElement xmlSubElement1 = xmlDocument.CreateElement("todoID");
        xmlSubElement1.InnerText = todoID;
        xItem.AppendChild(xmlSubElement1);

        XmlElement xmlSubElement2 = xmlDocument.CreateElement("todoDate");
        xmlSubElement2.InnerText = todoDate;
        xItem.AppendChild(xmlSubElement2);

        XmlElement xmlSubElement3 = xmlDocument.CreateElement("todoTime");
        xmlSubElement3.InnerText = todoTime;
        xItem.AppendChild(xmlSubElement3);

        XmlElement xmlSubElement4 = xmlDocument.CreateElement("todoItem");
        xmlSubElement4.InnerText = todoItem;
        xItem.AppendChild(xmlSubElement4);

        XmlElement xmlSubElement5 = xmlDocument.CreateElement("todoStatus");
        xmlSubElement5.InnerText = todoStatus;
        xItem.AppendChild(xmlSubElement5);

        XmlElement xmlSubElement6 = xmlDocument.CreateElement("UserID");
        xmlSubElement6.InnerText = UserID;
        xItem.AppendChild(xmlSubElement6);


        //xmlDocument.AppendChild(xItem);
        xmlDocument.DocumentElement.LastChild.AppendChild(xItem);
        // then finally save
        xmlDocument.Save(@"C:");
        xmlmsg ="A new item has been added to the to do list";

Answer 3

    private void btndlt_Click(object sender, EventArgs e)
    {

        DataGridViewRow row = dataGridView1.Rows[0];
        string id = row.Cells[0].Value.ToString();
        string path = @"";
        XmlDocument doc = new XmlDocument();
        doc.Load(path);
        XmlNode node = doc.SelectSingleNode("/TheToDoList/item[todoID='" + id + "']");
        node.ParentNode.RemoveChild(node);
        doc.Save(path);
        MessageBox.Show("Selected Record Deleted Successfully");
    }

    private void btnUpdate_Click(object sender, EventArgs e)
    {
        DataGridViewRow row = dataGridView1.Rows[0];
        int id1 = Convert.ToInt32(row.Cells[0].Value);
        string path = @"";
        XmlDocument doc = new XmlDocument();
        doc.Load(path);
        XmlNode node = doc.SelectSingleNode("/TheToDoList/item[todoID='" + id1 + "']");
        node.ParentNode.RemoveChild(node);
        doc.Save(path);

                //    //Load the XML File
                    doc.Load(path);


                    XmlElement root = doc.CreateElement("TheToDoList");
                    XmlElement Subroot = doc.CreateElement("item");
                    XmlElement todoID = doc.CreateElement("todoID");
                    XmlElement todoDate = doc.CreateElement("todoDate");
                    XmlElement todoTime = doc.CreateElement("todoTime");
                    XmlElement todoItem = doc.CreateElement("todoItem");
                    XmlElement todoStatus = doc.CreateElement("todoStatus");
                    XmlElement UserID = doc.CreateElement("UserID");
                    //Add the values for each nodes
                    todoID.InnerText = row.Cells[0].ToString();
                    todoDate.InnerText = row.Cells[1].ToString();
                    todoTime.InnerText = row.Cells[2].ToString();
                    todoItem.InnerText = row.Cells[3].ToString();
                    todoStatus.InnerText = row.Cells[4].ToString();
                    UserID.InnerText = row.Cells[5].ToString();
                    //Construct the document
                    doc.AppendChild(root);
                    root.AppendChild(Subroot);
                    Subroot.AppendChild(todoID);
                    Subroot.AppendChild(todoDate);
                    Subroot.AppendChild(todoTime);
                    Subroot.AppendChild(todoItem);
                    Subroot.AppendChild(todoStatus);
                    Subroot.AppendChild(UserID);
                    doc.Save(path);
                    MessageBox.Show("Selected Record Edited Successfully");

Answer 4

    DataTable dataTable = new DataTable();

     int count1,count2;
        clsInfor.northCON.Open();
        clsInfor.dataAdapter = new SqlDataAdapter("SELECT Discontinued, QuantityPerUnit FROM Products", clsInfor.northCON);
        dataTable = new DataTable();
        clsInfor.dataAdapter.Fill(dataTable);
        Excel.Application app = new Excel.Application(); //creating a new application
        app.Visible = true;

        Excel.Workbook book = app.Workbooks.Add(1); // creating an instance of the workbook 
        Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[1]; // creating an instance of the worksheet

        ((Excel.Range)sheet.Cells[1, "A"]).Value2 = "Report"; // creating the header of the report
        ((Excel.Range)sheet.Cells[2, "B"]).Value2 = "Number of products per Cat";//creating the names of the colomns in the excell spreedsheet
        ((Excel.Range)sheet.Cells[2, "C"]).Value2 = "Number of products that have been discontinued";

        ((Excel.Range)sheet.Cells[4, "D"]).Value2 = "Tot number of Prod";

        for (count1 = 0; count1 < dataTable.Rows.Count; count1++)
        {
            for (count2 = 0; count2 < dataTable.Columns.Count; count2++)
            {
                sheet.Cells[count1 + 3, count2 + 2] = dataTable.Rows[count1][count2];
            }
            sheet.Cells.Columns.AutoFit();
        }

Answer 5

Here I found the solution that fits for you question.

Crystal Reports can use an ODBC DSN to connect to a database from which you to extract data and information for reporting purposes.

Note There is a known issue with certain versions of Crystal Reports where the application is unable to open and browse tables and fields through an ODBC connection. Before using Crystal Reports with MySQL, please ensure that you have update to the latest version, including any outstanding service packs and hotfixes. For more information on this issue, see the Business) Objects Knowledgebase for more information.

Follow these steps:

1. Create a DSN using the Data Sources (ODBC) tool. You can either specify a complete database, including user name and password, or you can build a basic DSN and use Crystal Reports to set the user name and password.
2. Start the Cross-Tab Report Wizard, either by clicking the option on the Start Page. Expand the Create New Connection folder, then expand the ODBC (RDO) folder to obtain a list of ODBC data sources.