Reporting using Crystal Reports in Visual Studio 2010

Question

I am new to Visual Studio 2010 C #. I am creating an application that creates a report. The information that will be displayed in the report is located on the MySQL server. I have already installed Crystal Reports. However, I have some problems getting data from MySQL because I cannot find data from MySQL. The data specified in the Report Wizard is not in the database files in MySQL, but in the forms that 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

Here I found a solution that is right for you.

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

Note. A known issue with some versions of Crystal Reports where an application cannot open and view tables and fields through an ODBC connection. Before using Crystal Reports with MySQL, make sure you have an update to the latest version, including any released service packs and patches. For more information about this issue, see Business) Knowledge Base Objects for more information.

Follow these steps:

• Create a DSN using the Data Sources (ODBC) tool . You can specify a complete database, including a username and password, or you can create a basic DSN and use Crystal Reports to set a username and password.
• Launch the multi-tab report wizard by clicking the option on the start page. Expand the Create New Connection folder, then expand the ODBC (RDO) folder to get a list of ODBC data sources.

Answer 3

  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 4

  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 5

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