Error: System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing operator) in query expression

Question

I'm trying to export the results of a stored procedure to Excel and receiving the following error when running: System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing operator) in query expression. Below is the code I am using. The excel file gets created but there is no data populating the file.

string TableColumns = "";

// Get the Column List from Data Table so can create Excel Sheet with Header
foreach (DataTable table in ds.Tables)
{
    foreach (DataColumn column in table.Columns)
    {
        TableColumns += column + "],[";
    }
}

// Replace most right comma from Columnlist
TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
TableColumns = TableColumns.Remove(TableColumns.Length - 2);

//Use OLE DB Connection and Create Excel Sheet
Excel_OLE_Con.ConnectionString = connstring;
Excel_OLE_Con.Open();
Excel_OLE_Cmd.Connection = Excel_OLE_Con;
Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
Excel_OLE_Cmd.ExecuteNonQuery();

//Write Data to Excel Sheet from DataTable dynamically
foreach (DataTable table in ds.Tables)
{
    String sqlCommandInsert = "";
    String sqlCommandValue = "";
    foreach (DataColumn dataColumn in table.Columns)
    {
        sqlCommandValue += dataColumn + "],[";
    }

    sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
    sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
    sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue + ") VALUES(";

    int columnCount = table.Columns.Count;
    foreach (DataRow row in table.Rows)
    {
        string columnvalues = "";
        for (int i = 0; i < columnCount; i++)
        {
            int index = table.Rows.IndexOf(row);
            columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";
        }
        columnvalues = columnvalues.TrimEnd(',');
        var command = sqlCommandInsert + columnvalues + ")";
        Excel_OLE_Cmd.CommandText = command;
        Excel_OLE_Cmd.ExecuteNonQuery();
    }
}
Excel_OLE_Con.Close();
Dts.TaskResult = (int)ScriptResults.Success;

This line `columnvalues += "'" + table.Rows[index].ItemArray[i] + "'` looks like it could lead to a problem. Have you printed out the broken query and looked at it? If any of your values in those table rows contain a `'` character it will break this query. See this wiki for more information: [unfiltered escape characters](https://en.wikipedia.org/wiki/SQL_injection#Incorrectly_filtered_escape_characters) — pstrjds, Apr 02 '19 at 15:42
On a note unrelated to your question. You are concatenating strings in a loop in order to build up your query. You should really switch that to use [StringBuilder](https://learn.microsoft.com/en-us/dotnet/api/system.text.stringbuilder?view=netframework-4.7.2) and here is a [link](https://stackoverflow.com/a/1825808/416574) as to why. — pstrjds, Apr 02 '19 at 15:50
Yes, that is EXACTLY what is happening. The name 0'Brien is causing it to fail. Is there any way around that? — Michael Feuti, Apr 02 '19 at 15:50
Possible duplicate of [single quotes escape during string insertion into a database](https://stackoverflow.com/questions/11912412/single-quotes-escape-during-string-insertion-into-a-database) — pstrjds, Apr 02 '19 at 15:58
pstrjds-would you be able to show me how to escape it. I'm sorry but I seem unable to get this part right. — Michael Feuti, Apr 02 '19 at 16:09
I will add an untested answer with parameterized query, but you can see details on escaping in the linked duplicate. — pstrjds, Apr 02 '19 at 16:20

pstrjds · Answer 1 · 2019-04-02T16:56:15.413

So the issue is related to you have an unescaped single quote in your data. Two options for dealing with that are to escape the single quote (turn it into two single quotes):

string myData = "This string won't work";
string myDataEscaped = myData.Replace("'", "''");

or the other option (and more robust) is to use a parameterized query. I will use just the lower part of your code where you are doing this query build up and insertion and show you how that can be done (along with some cleanup and making use of StringBuilder). Note I did not compile and test this as I don't have any test data to use.

var tableColumns = string.Join(",", columns.Cast<DataColumn>().Select(x => "[" + x.ColumnName + "]"));
var insertBase = $"INSERT into {SheetName} ({tableColumns}) VALUES(";
int columnCount = table.Columns.Count;
foreach (DataRow row in table.Rows)
{
    Excel_OLE_Cmd.Parameters.Clear(); // Since you are reusing the command you have to clear the parameters

    var command = new StringBuilder(insertBase);
    var index = table.Rows.IndexOf(row);

    // I will assume you always have at least one column, otherwise these lines would fail
    // Add the first row before the loop that way we don't have to delete the end comma
    var param = $"@param_{index}_0";
    command.Append(param);
    Excel_OLE_Cmd.Parameters.AddWithValue(param, table.Rows[index].ItemArray[0]);
    for (int i = 1; i < columnCount; ++i)
    {
        param = $"@param_{index}_{i}"
        command.Append("," + param);
        Excel_OLE_Cmd.Parameters.AddWithValue(param, table.Rows[index].ItemArray[i]);
    }
    command.Append(")");
    Excel_OLE_Cmd.CommandText = command.ToString();
    Excel_OLE_Cmd.ExecuteNonQuery();
}

Error: System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing operator) in query expression

1 Answers1