Error in Load Data infile query in C#

Question

I am having problem with the following code:-

 private void btnUpload_Click(object sender, EventArgs e)
    {

        string nm = txtFilename.Text;
        string qry = "LOAD DATA INFILE 'D:\\HHTFiles\\" + nm + "' INTO TABLE `table1`.`location`FIELDS TERMINATED BY '-->'LINES TERMINATED BY '\r\n'(Barcode,BinLoc);";
        cmd = new OdbcCommand(qry, con);
        int i = cmd.ExecuteNonQuery();
        if (i > 0)
        {
            MessageBox.Show(" File loaded successfully...");
        }


    }

private void btnBrowse_Click(object sender, EventArgs e)
    {
        OpenFileDialog openfiledailog1 = new OpenFileDialog();
        openfiledailog1.InitialDirectory = "D:\\HHTFiles\\";
        openfiledailog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
        openfiledailog1.FilterIndex = 2;
        openfiledailog1.RestoreDirectory = true;

        if (openfiledailog1.ShowDialog() == DialogResult.OK)
        {
            try
            {
                if ((openfiledailog1.OpenFile()) != null)
                {

                     txtFilename.Text = openfiledailog1.SafeFileName.ToString();

                }
            }
            catch (Exception ex)
            {
              MessageBox.Show("Error: Could not read file from disk. Original error: "      + ex.Message);
            }
        }
    }

It runs properly, if I give the full file path in my query like :- string qry = "LOAD DATA INFILE 'D:\HHTFiles\ ABC.txt' INTO TABLE table1.locationFIELDS TERMINATED BY '-->'LINES TERMINATED BY '\r\n'(Barcode,BinLoc);"; But it throws error when the file path is passed in a String variable.

Error:- ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.48-community]File 'D:HHTFilesABC.txt' not found (Errcode: 2)

RookieCoder · Accepted Answer · 2012-07-26 06:16:27Z

2

@Aghilas It got solved :) Though I am not sure why I got error in first place. Anway, I just replaced '\\' with '/' and it ran fine.

        string nm = txtFilename.Text;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.Append("LOAD DATA INFILE ");
        stringBuilder.Append(Path.Combine("'D:/HHTFiles/", nm));
        stringBuilder.Append("' INTO TABLE `table1`.`location`FIELDS TERMINATED BY '-->'LINES TERMINATED BY '\r\n'(Barcode,BinLoc);");

answered Jul 26, 2012 at 6:16

RookieCoder

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aghilas Yakoub · Accepted Answer · 2012-07-26 07:48:04Z

0

try with Path.Combine

Path.Combine("D:\\HHTFiles\\", nm);

And use StringBuilder in order to build your query

 StringBuilder stringBuilder = new StringBuilder();
 stringBuilder.Append("LOAD DATA INFILE ");
 stringBuilder.Append(Path.Combine("D:\\HHTFiles\\", nm));
 stringBuilder.Append(" INTO TABLE table1.location");
 stringBuilder.Append(" FIELDS TERMINATED BY '-->'"); 
 stringBuilder.Append(" LINES TERMINATED BY (");              
 stringBuilder.Append(Barcode.BinLoc);
 stringBuilder.Append(")");

 string qry = stringBuilder.ToString();

edited Jul 26, 2012 at 7:48

answered Jul 25, 2012 at 12:04

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

4 Comments

RookieCoder Over a year ago

Thanks for your quick reply. I tried your code, still I am getting the earlier error. ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.48-community]File 'D:HHTFilesABC.txt' not found (Errcode: 2)

Aghilas Yakoub Over a year ago

Happy to reply you, Verify that D:HHTFiles/ABC.txt exist over path

RookieCoder Over a year ago

Yes, ABC.txt file exist in the specified path.I even applied breakpoint on the above piece of code.It shows qry="LOAD DATA INFILE 'D:\\HHTFiles\\ABC.txt' INTO TABLE table1.locationFIELDS TERMINATED BY '-->'LINES TERMINATED BY '\r\n'(Barcode,BinLoc);".If I run this query on Mysql Editor, it runs fine and 1 row gets affected.But the same query is not working in C#.I am so confused...

Aghilas Yakoub Over a year ago

Hello RookieCoder try with new query i update them, test et tell me the reponse

Collectives™ on Stack Overflow

Error in Load Data infile query in C#

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related