Link to home
Start Free TrialLog in
Avatar of Billy Ma
Billy MaFlag for Hong Kong

asked on

C# ODBC Microsoft Access Driver] Could not find file '(unknown)'.

I have written a code to import file from a directory.
If I place the source file not in the Application.StartupPath, then it will result in the following error, why? but the error is somewhat freak!! it is not about the file cannot be access, it's about the Microsoft Access Driver....(i.e., the connection)

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'. User generated image
anyone can help?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.Odbc;
using System.Threading;

namespace GMSAuditApplication
{
    public partial class Import : Form
    {
        string connectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=gms_application_check.mdb;Uid=;Pwd=;";

        OdbcConnection con = null;
        OdbcCommand command = null;
        OdbcDataReader dr = null;
        string statement = null;

        public Import()
        {
            InitializeComponent();

            backgroundWorker1.WorkerReportsProgress = true;
            backgroundWorker1.WorkerSupportsCancellation = true;
        }

        private void Import_Load(object sender, EventArgs e)
        {
            //con = new OdbcConnection(connectionString);
            //con.Open();

            //statement = @"SELECT ref_code_id, ref_code_value FROM reference_code WHERE ref_code_module = 'IMPORT' AND ref_code_type = 'FILE_TYPE'";
            //command = new OdbcCommand(statement, con);

            //cbxFileType.Items.Add(" - Please select a report type - ");
            //cbxFileType.SelectedItem = " - Please select a report type - ";

            //dr = command.ExecuteReader();
            //while (dr.Read())
            //{
            //    cbxFileType.Items.Add(dr.GetString(1));
            //}

            //con.Close();
            //con.Dispose();
        }

        private void btnSelectSourceFilePath_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            //dialog.Filter = "Microsoft Access Database (*.mdb)|*.txt|All files (*.*)|*.*";
            //dialog.Filter = "Microsoft Access Database (*.mdb)|*.mdb";
            dialog.InitialDirectory = Application.StartupPath;
            dialog.Title = "Select a file";

            if (dialog.ShowDialog() == DialogResult.OK)
            {
                txtFilePath.Text = dialog.FileName;
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            pbImport.Value = 0;
            lblPercentage.Text = "0%";
            lblStatus.Text = "Loading...";
            backgroundWorker1.RunWorkerAsync();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            backgroundWorker1.CancelAsync();
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            con = new OdbcConnection(connectionString);
            con.Open();
            string sourceFilePath = txtFilePath.Text;

            if (File.Exists(sourceFilePath))
            {
                string filename = sourceFilePath.Substring(sourceFilePath.LastIndexOf(@"\") + 1);
                string current_datetime = System.DateTime.Now.ToString("yyyyMMddhmmss");

                int line_num = 0;

                string user_name = "";
                string full_name = "";

                StreamReader sr;

                sr = new StreamReader(File.OpenRead(sourceFilePath), Encoding.GetEncoding("UTF-8"));

                //double filePercentage = ((double)num_file) / ((double)total_num_file);
                //int ProgressPercentage = (int)(filePercentage * 100);
                //backgroundWorker1.ReportProgress(ProgressPercentage, "Reading..." + filename + "(" + num_file + " out of " + total_num_file + ")");

                string line = sr.ReadLine();
                
                while (line != null)
                {
                    line_num = line_num + 1;

                    if (line.IndexOf("User name") != -1)
                    {
                        user_name = line.Substring("User name".Length);
                        user_name = user_name.Trim();

                        string statement = @"SELECT * FROM windows_domain_user_list WHERE date_time = '" + current_datetime + "' AND filename = '" + filename + "' AND user_name = '" + user_name + "'";
                        command = new OdbcCommand(statement, con);
                        dr = command.ExecuteReader();

                        bool is_exist = false;

                        while (dr.Read())
                        {
                            is_exist = true;
                            break;
                        }

                        command.Dispose();

                        if (is_exist == false)
                        {
                            statement = @"INSERT INTO windows_domain_user_list(date_time, filename, user_name) VALUES('" + current_datetime + "', '" + filename + "', '" + user_name + "')";
                            //System.Console.WriteLine(stat);
                            command = new OdbcCommand(statement, con);
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }

                    }

                    if (line.IndexOf("Full Name") != -1)
                    {
                        full_name = line.Substring("Full Name".Length);
                        full_name = full_name.Trim();
                        full_name = full_name.Replace("'", "''");

                        statement = @"UPDATE windows_domain_user_list " +
                                     "SET full_name = '" + full_name + "' " +
                                     "WHERE date_time = '" +  current_datetime + "' " +
                                     "AND filename ='" + filename + "' " + 
                                     "AND user_name = '" + user_name + "' ";

                        //System.Console.WriteLine(stat);
                        command = new OdbcCommand(statement, con);
                        command.ExecuteNonQuery();
                        command.Dispose();


                    }

                    line = sr.ReadLine();
                }

                sr.Close();
                sr.Dispose();

                if (backgroundWorker1.CancellationPending)
                {
                    e.Cancel = true;
                    return;
                }
            }

            con.Close();
            con.Dispose();
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            int progressPercentage = e.ProgressPercentage;
            pbImport.Value = progressPercentage; //update progress bar
            lblPercentage.Text = progressPercentage + "%";
            lblStatus.Text = e.UserState.ToString();
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Cancelled)
            {
                lblStatus.Text = "The task has been cancelled";
            }
            else if (e.Error != null)
            {
                lblStatus.Text = "Error. Details: " + (e.Error as Exception).ToString();
            }
            else
            {
                lblStatus.Text = "The task has been completed";
            }

        }

        private void Import_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
        }
    }
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of nishant joshi
nishant joshi
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Billy Ma

ASKER

yes, it's there.

The problem is my another application is ok
and then I create a new application using the old application by just coping the code...

 User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think the problem is not in the access, but in the Backround Worker.
Because the background worker cannot access the UI element.

I am trying to access the txtFilePath.Text

so, anyone can let me kow how can I access the txtFilePath.Text in the Background Worker?

Thanks!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
because user can choose the file path.
and then my program will create another thread for reading that file....
I tried many times, if I hard code the path, it will be no problems.
If I let user to choose the path, it will be a problem.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
jagrut_patel:
yes, it will work if hard-code the path

it won't work if the path is selected by the user....
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial