Exporting dates from mysql table to .CSV

Question

0.00/5 (No votes)

See more:

Hello there,

I tried to export a MySQL Table to a .csv but there is something that isn't working and I can't find out what it is. I first tried to get the dates saved into a DataTable and then export them to the .csv using this function:

* The error: "

C#

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1" at sda.Fill(data);

<pre>public void CreateCSVFile(DataTable dtDataTablesList, string strFilePath)
        {
            // Create the CSV file to which grid data will be exported.
            StreamWriter sw = new StreamWriter(strFilePath, false);
            //First we will write the headers.
            int iColCount = dtDataTablesList.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
                sw.Write(dtDataTablesList.Columns[i]);
                if (i < iColCount - 1)
                {
                    sw.Write("", "");
                }
            }
            sw.Write(sw.NewLine);

            // Now write all the rows.
            foreach (DataRow dr in dtDataTablesList.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();
        }

What I have tried:

MySqlConnection connection = CDBAccess.GetCon;
MySqlCommand sSql = new MySqlCommand("SELECT * from mytable;", connection);
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = sSql;
DataTable data = new DataTable();
sda.Fill(data);
String path = @"C:\Users\Public\Documents\MyDocument";
CreateCSVFile(data, path);

Posted 3-Oct-16 19:21pm

TheRealProgrammer

Updated 3-Oct-16 23:50pm

Add a Solution

Comments

MayurDighe 4-Oct-16 1:33am

"TheRealProgrammer";)

Use Debugger.

TheRealProgrammer 4-Oct-16 1:38am

What does this change? It still stops at sda.Fill(data) with the same error.

MayurDighe 4-Oct-16 1:46am

It will save much time.
You will understand what data is passing to what variable? Also you can ensure data passed is correct or not?

It stops at sda.fill(data)... because machine doesn't understand what to do next?
You have explicity teach machine what's next? That's why Debugger come into picture, to know where code fails actually.

TheRealProgrammer 4-Oct-16 1:55am

NowDate "20161004075341" string
+ connection {MySql.Data.MySqlClient.MySqlConnection} MySql.Data.MySqlClient.MySqlConnection
+ sSql {MySql.Data.MySqlClient.MySqlCommand} MySql.Data.MySqlClient.MySqlCommand
+ sda {MySql.Data.MySqlClient.MySqlDataAdapter} MySql.Data.MySqlClient.MySqlDataAdapter
+ data {} System.Data.DataTable
path null string

Looks like something with my MySqlComand an DataAdapter is wrong.

MayurDighe 4-Oct-16 1:59am

"path null string"

make sure each line of code is having or passing correct values to next.
Use debugger and do some Brainstorm.

TheRealProgrammer 4-Oct-16 2:02am

Not the path is wrong, something with the MySQL string is wrong.

TheRealProgrammer 4-Oct-16 2:16am

Why is sSql returning "{MySql.Data.MySqlClient.MySqlCommand}" and not "SELECT * from mytable" ?

MayurDighe 4-Oct-16 2:22am

Exactly. That's why - Use debugger.
Go Line by Line.

TheRealProgrammer 4-Oct-16 2:24am

I can't find anything.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TheRealProgrammer · Accepted Answer · 2016-10-03T23:50:00

This would be the solution:

MySqlDataAdapter sda = new MySqlDataAdapter();
                        sda.SelectCommand = cmdDataBase;

                        DataTable data = new DataTable();
                        sda.Fill(data);
                        BindingSource aSource = new BindingSource();
                        aSource.DataSource = data;
                        dataGridView1.DataSource = aSource;
                        sda.Update(data);

                        StringBuilder sb = new StringBuilder();

                        string[] columnNames = data.Columns.Cast