0

I have problem when I'm trying to run my program, When I was on my first loop, it worked, but on the second loop

"vshost.exe has stopped working"

error was shown, when I debugged it the error was on my ExecuteReader(). can somebody please help me regarding this?

here's the first part of my code:

//start here

public void ConvertToText(string _fileUrl, string _fileName) {

    //The connection string to the excel file
    string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _fileUrl + ";Extended Properties=Excel 12.0;";
    //The query
    string strSQL = "SELECT * FROM [Sheet1$]";
    //The connection to that file
    using(OleDbConnection conn = new OleDbConnection(connstr))



    //The command 
    using (OleDbCommand cmd = new OleDbCommand(strSQL, conn))
    {
        conn.Open();
        DataTable dt = new DataTable();
        try
        {


            string extension = System.IO.Path.GetExtension(_fileName);
            string result = _fileName.Substring(0, _fileName.Length - extension.Length);
            using (OleDbDataReader dr1 = cmd.ExecuteReader())
            {
                StreamWriter sw = new StreamWriter(@"C:\Users\jhrnavarro\Documents\From SIr Boo\GBOC\Activation\Destination\" + result + ".txt");
                if (dr1.Read())
                {
                    dt.Load(dr1);
                }

                int iColCount = dt.Columns.Count;

                for (int i = 0; i < iColCount; i++)
                {
                    sw.Write("'" + dt.Columns[i] + "'");
                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
                // Now write all the rows.

                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {
                        if (!Convert.IsDBNull(dr[i]))
                        {
                            sw.Write("'" + dr[i].ToString() + "'");
                        }
                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }
                    sw.Write(sw.NewLine);
                }
                sw.Close();
                Console.WriteLine("File is saved");
            }
        }
        catch (OleDbException caught)
        {
            Console.WriteLine(caught.Message);
        }

        finally
        {
            conn.Close();
        }

        Console.Read();

    }


}
JanMichael
  • 31
  • 1
  • 12
  • I would recommend that you handle the logic of writing to text files outside of this method call. you cannot open a costly resource the database and do writing to a text file. You can have a separate method that gets the datatable and then writes to the text file. – Saravanan May 26 '13 at 10:01
  • Could you explain what this code is supposed to do? The only loop in this code is on the ColumnsCollection. Are you trying also to write the data rows? – Steve May 26 '13 at 10:04
  • @saravanan Thank you so much for your quick response. Regarding this, how am i going to that since my data source is dynamic? thanks again – JanMichael May 26 '13 at 10:26
  • @Steve my code is for to Convert Excel files to Text files. and fetching excel files from different folder within parent folder. My error is on ExecuteReader. I don't know why is this happening. – JanMichael May 26 '13 at 10:28
  • @Steve just posted the edit code., sorry i forgot! – JanMichael May 26 '13 at 12:40

2 Answers2

0

I have rewritten your code in this way

//The connection string to the excel file
string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _fileUrl + ";Extended Properties=Excel 12.0;";
//The query
string strSQL = "SELECT * FROM [Sheet1$]";

//The connection to that file
using(OleDbConnection conn = new OleDbConnection(connstr))
using(OleDbCommand cmd = new OleDbCommand(strSQL, conn))
{
    conn.Open();
    DataTable dt = new DataTable();
    try
    {
        using(OleDbDataReader dr1 = cmd.ExecuteReader())
        {
            dt.Load(dr1);
        }

        string result = Path.GetFileNameWithoutExtension(_fileName);
        string outFile = Path.Combine(@"C:\Users\Administrator\Desktop\GBOC\Activation\Destination", result + ".txt");
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < dt.Columns.Count - 1; i++)
            sb.AppendFormat("'{0}',", dt.Columns[i].ColumnName);

        sb.Length--;
        sb.AppendLine();
        foreach(DataRow r in dt.Rows)
        {
            for (int i = 0; i < dt.Columns.Count - 1; i++)
                sb.AppendFormat("'{0}',", r[i].ToString());
            sb.Length--;
            sb.AppendLine();
        }
        using(StreamWriter sw = new StreamWriter(outFile))
            sw.Write(sb.ToString());
     }
     catch(Exception ex)
     {
        MessageBox.Show(ex.Message);
     }
}

I have changed the way in which you build the output file name using the simple GetFileNameWithoutExtension. Then I have added the appropriate using statement to your code to effectively dispose the objects involved in the database access and in the file writing.
Finally, I have used a StringBuilder to create the buffer to write your first the column names and then the data extracted by your rows in the output file with only one call. (And removed internal checks for the last column)

Caveats: No checking for null values in rows. The dimension of your Excel file could be a problem.

Steve
  • 213,761
  • 22
  • 232
  • 286
  • Hi @Steve Thanks for the code. When I tried it , its still not working. Do you have any idea why this error is coming up? – JanMichael May 26 '13 at 11:10
  • Same error? If yes then probably is something external to this inner part. Did you write a method where you pass the name of the second file? Have you checked if it exists and is readable (no permissions problems)? Adding a using around the OleDbDataReader itself – Steve May 26 '13 at 11:20
  • on my ConnectionString that's where i pass the second file. yes all files are existing and readable. – JanMichael May 26 '13 at 11:51
  • I don't understand this. You change the value of the global called `_fileUrl` and call this code? Could you add to your question the calling code? – Steve May 26 '13 at 12:09
  • Hi @Steve i just posted my code here. please see above. thank you. – JanMichael May 26 '13 at 12:23
  • Can't help more, I just tried my code with two Excel files of mine and also with your code. No problems at all, just changed the `Sheet1$` to match mine and the output directory: `ConvertToText(@"D:\temp\results.xlsx", @"test1.txt"); ConvertToText(@"D:\temp\results1.xlsx", @"test2.txt");` – Steve May 26 '13 at 12:51
0

Have you tried placing a breakpoint at the location where the program crashes and then walk through the rest of the code with F11 and the Locals window? That has helped me quite a few times in the past.

Jim Conace
  • 21
  • 2