Getting a syntax error with INSERT INTO statement

Question

I'm receiving the error of "C# Syntax error in INSERT INTO statement" for this part of code below:

public static void SaveCurrentPlaylistToSheet(YouTubeVideo[] CurrentPlaylist, string playlistTitle)
{
    ActiveSheetTitle = "Test"; //playlistTitle;
    string currentTitle;

    if (FileCreation)
    {
        NewPlaylist = true;
    }

    using (conn = new OleDbConnection(ConnectionString))
    {
        conn.Open();
        try
        {
            cmd = new OleDbCommand();
            cmd.Connection = conn;

            currentTitle = "Test";

            for (int i = 0; i < CurrentPlaylist.Length; i++)
            {
                //currentTitle = Regex.Replace(CurrentPlaylist[i].title, " ", "");
                //currentTitle = currentTitle.Replace(",", "");

                using (cmd = conn.CreateCommand())
                {
                    if (NewPlaylist)
                    {
                        NewPlaylist = false;
                        cmd.CommandText = "INSERT INTO [" + ActiveSheetTitle + "$] (F1) VALUES (?)";
                    }
                    else
                    {
                        cmd.CommandText = "INSERT INTO [" + ActiveSheetTitle + "] (F1) VALUES (?)";
                    }
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = currentTitle;

                    cmd.ExecuteNonQuery(); 
                }
            }
        }
        catch (Exception ex)
        {
            string filePath = @"C:\Dropbox\amitai\Programming\C# projects\Youtube Playlists\Error.txt";

            using (StreamWriter writer = new StreamWriter(filePath, true))
            {
                writer.WriteLine("Message :" + ex.Message + "<br/>" + Environment.NewLine + "StackTrace :" + ex.StackTrace +
                   "" + Environment.NewLine + "Date :" + DateTime.Now.ToString());
                writer.WriteLine(Environment.NewLine + "-----------------------------------------------------------------------------" + Environment.NewLine);
            }
        }

        cmd = null;
        conn.Close();
        cmd.Dispose();
        conn.Dispose();
    }

    ActiveSheetTitle = null;
}

The connection-string details are:

ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
                    " + MyPath + ";" +
                    "Mode=ReadWrite;" + "Mode=ReadWrite;" + //ReadOnly=false" +
                    "Extended Properties=\"Excel 12.0;HDR=NO; IMEX =1;\"";

and "myPath" is retrieved from:

public static string MyPath { get; set; } = @"\MyPlaylists.xlsx";
...

using (var dialog = new FolderBrowserDialog())
{
    dialog.Description = "Please choose directory";
    if (dialog.ShowDialog() == DialogResult.OK)  //check for OK. They might press cancel, so don't do anything if they did.
    {
        string path = dialog.SelectedPath;
        MyPath = path + MyPath;
    }
}

By googling I have found solutions for this error which are contacted to using a reserved keywords as parameters or receiving some space input, so for testing purpose I have set the currentTitle and ActiveSheetTitle as "Test", but still the error remains.

Any help for solving the issue will be appreciated.

the output of the exception is: StackTrace : at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() — Amitai Weil, Jun 05 '18 at 08:11
$ indicates the excel table/sheet already exists in workbook. The exception appears also if removing it. — Amitai Weil, Jun 05 '18 at 08:27
Also, note that your command creation is outside the for loop. Thus you continue to add new parameters to the same command. This will not work correctly. Create the command inside the loop or create the parameter outside the loop and inside the loop just change the parameter's value — Steve, Jun 05 '18 at 20:20
Thanks for the help, i've found that the 'ActiveSheetTitle' was nulled somewhere in the way and that caused the error. After fixing that I'm still facing an issue with the command - 'The Microsoft Access database engine could not find the object 'Test$'. Make sure the object exists and that you spell its name and the path name correctly.' I've edited the code to last version including the connection string — Amitai Weil, Jun 06 '18 at 09:49
Are you sure about the ending $?. Usually this character is required to identify a sheet if you want to use it as a Table. In your code above there is a block with the ending $ and one without the $. The message should come from the block without the ending $ — Steve, Jun 06 '18 at 10:00
I'm not sure about nothing here, since up till now all attempts result with an exception. removing the '$' results with `The Microsoft Access database engine could not find the object 'Test'. Make sure the object exists and that you spell its name and the path name correctly.` — Amitai Weil, Jun 06 '18 at 10:14
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172585/discussion-between-steve-and-amitai-weil). — Steve, Jun 06 '18 at 10:21

Amitai Weil · Answer 1 · 2018-06-10T09:15:37.367

-1

Thanks for all the help. With the help of the comments about the question I have figured out that the exception arises since the file doesn't exist at that stage. Hope this will help somebody encountering a similar problem.

edited Jun 10 '18 at 09:15

answered Jun 07 '18 at 08:39

Amitai Weil

57
2
10

Getting a syntax error with INSERT INTO statement

1 Answers1