MySQL Insert Operation fails with syntax error

0

Today I wanted to write a Code that scans all files in the "Documents" Folder of the User, takes the Text and the Filenames and puts them into a database(just as some kind of test if I could do that, not for getting data). But if it Inserts the content into the database, my program says there is a syntax error with the text in the files in my Documents Folder.

My Code:

using System;
using MySql.Data.MySqlClient;
using System.IO;

namespace HelloWorld
{
    class Program
    {
        static void
        Main(string[] args)
        {
            Console.WriteLine("Getting Connection ...");

            var datasource = @"localhost";//your server
            var database = "database"; //your database name
            var username = "username"; //username of server to connect
            var password = "password"; //password

            //your connection string 
            string connStr = $"Server={datasource};Database={database};Uid={username};Pwd={password}";
            //create instanace of database connection
            using (var conn = new MySqlConnection(connStr))
            {
                try
                {
                    Console.WriteLine("Openning Connection ...");

                    //open connection
                    conn.Open();

                    Console.WriteLine("Connection successful!");
                }
                catch (Exception e)
                {
                    Console.WriteLine("Error: " + e.Message);
                }
                string path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                var txtFiles = Directory.EnumerateFiles(path, "*.*");
                foreach (string currentFile in txtFiles)
                {
                    string currentfile_txt = File.ReadAllText(currentFile);
                    var stm = $"INSERT INTO file_contents(File_Name, File_Content) VALUES ({currentFile}, {currentfile_txt});";
                    var cmd = new MySqlCommand(stm, conn);
                    cmd.ExecuteNonQuery();
                }

            }
        }
    }
}

But if I run it, it gives me this Error:

Unhandled exception. MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':\Users\49176\Documents\aaa.txt, )' at line 1

It seems like it handles the text in the aaa.txt as SQL Code. How can I change that? (btw: In the txt file are just some random letters)

I hope you can help me

c#
mysql
sql
asked on Stack Overflow Jun 19, 2020 by af2111 • edited Jun 20, 2020 by GMB

1 Answer

3

Your problem is missing quotes

var stm = $"INSERT INTO file_contents(File_Name, File_Content) 
            VALUES ('{currentFile}', '{currentfile_txt}');";

But better to do this

var stm = $"INSERT INTO file_contents(File_Name, File_Content) VALUES (@1, @2);";
var cmd = new MySqlCommand(stm, conn);
cmd.Parameters.AddWithValue("@1", "temp")
cmd.Parameters.AddWithValue("@2", "temp")

foreach (string currentFile in txtFiles)
{
    cmd.Parameters[0].Value = currentFile;
    cmd.Parameters[1].Value = File.ReadAllText(currentFile);
    cmd.ExecuteNonQuery();
}
answered on Stack Overflow Jun 19, 2020 by T.S. • edited Jun 19, 2020 by T.S.

User contributions licensed under CC BY-SA 3.0