I'm trying to get my data from SQL and create/map that data into excel file but I'm facing this error message:
"The program '[1004] reportTest4.vshost.exe' has exited with code -1073741510 (0xc000013a)."
The codes can run before I exited the process.
Here is my code:
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
namespace reportTest4
{
class reportTest4
{
public static DataTable ExportDataFromSQLServer()
{
DataTable dataTable = new DataTable();
using (SqlConnection connection = new SqlConnection("Server=172.**.*.***;Database=SARSDB;Uid=****;Pwd=****"))
{
connection.Open();
// Define the query to be performed to export desired data
SqlCommand command = new SqlCommand("SELECT a.CustomerCode, b.ProductClassGroupName, b.ProductClass, SUM(a.CardTransAmt) as RM, SUM(a.CardTransCount) as TRANS, SUM(a.CashTransAmt) as RM, SUM(a.CashTransCount) as TRANS, SUM(a.ChqTransAmt) as RM, SUM(a.ChqTransCount) as TRANS FROM tblSalesAnalysis a INNER JOIN tblProductClass b ON a.CustomerCode = b.CustomerCode where b.CustomerCode = 'TM' group by a.CustomerCode, b.ProductClassGroupName, a.Location, b.ProductClass, b.ProductClassGroupCode", connection);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
var excelApplication = new Excel.Application();
var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing);
DataColumnCollection dataColumnCollection = dataTable.Columns;
for (int i = 1; i <= dataTable.Rows.Count + 1; i++)
{
for (int j = 1; j <= dataTable.Columns.Count; j++)
{
if (i == 1)
excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString();
else
excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString();
}
}
// Save the excel file at specified location
excelApplication.ActiveWorkbook.SaveCopyAs(@"C:\Users\nfhalim\Desktop\test2.xlsx");
excelApplication.ActiveWorkbook.Saved = true;
// Close the Excel Application
excelApplication.Quit();
connection.Close();
//Release or clear the COM object
releaseObject(excelWorkBook);
releaseObject(excelApplication);
}
return dataTable;
}
public static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
//MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
Is there any mistakes that I've overlooked?
Or do I need to add some libraries?
Thank you.
User contributions licensed under CC BY-SA 3.0