none
Issue with executing an SQL command using C# RRS feed

  • Question

  • I've got the following code that appears to insert the proper values into an SQL query string using parameters.

    The SQL is made using a series of loops to dynamically make the SQL and then to populate it using the Parameters option. The SQL string appears to be getting generated ok, and the correct data appears to being loaded into the Command Parameters. But I'm getting an 'Incorrect syntax near 'CDU-1227'. Incorrect syntax near '@JobID'.' error when attempting to execute it. This is the first parameter being fed into the SQL.

        _tempInsertSQL = "INSERT INTO @TableName(" + _temp1 + ") VALUES (" + _temp2 + ")"; //Appears as expected!
                             
        SqlCommand msCommand = MsSqlConnector.CreateCommand();
        msCommand.CommandText = _tempInsertSQL;
        msCommand.Parameters.AddWithValue("@TableName", (string)reader1["name"]);
        string a1 = String.Empty;
        string a2 = String.Empty;
        string b1 = String.Empty;
        string b2 = String.Empty;
        for (var i = 0; i < _names.Count; i++)
        {
            a1 = (string)"@" + _names[i];
            a2 = _names[i];
            //msCommand.Parameters.AddWithValue(a1, a2);
            msCommand.Parameters.Add(new SqlParameter(a1, a2));
         }
         for (var j = 0; j < _values.Count; j++)
         {
             b1 = (string)"@" + _values[j] + j.ToString();
             b2 = _values[j];
             //msCommand.Parameters.AddWithValue(b1, b2);
             msCommand.Parameters.Add(new SqlParameter(b1, b2));
                                            }
    
         //The following code is only a test to try to see what the contents of the string with the added paramiters is.
           String commandtext = msCommand.CommandText; foreach (SqlParameter p in msCommand.Parameters) commandtext = commandtext.Replace(p.ParameterName, p.Value.ToString());
                                            
    
         msCommand.ExecuteNonQuery();        //This errors but the sql this generates does run in SQL Maagement studio

    Any ideas?

    PS. I know this can be a lot cleaner, there's a lot in the code just to try and see why this isn't executing.

    I attempted to see what was in this using the CommandText SQL option, and this did produce SQL that run fine directly from the SQL Management studio, but not from the ExecuteNonQuery() command in C#.

     String commandtext = msCommand.CommandText; foreach (SqlParameter p in msCommand.Parameters) commandtext = commandtext.Replace(p.ParameterName, p.Value.ToString());
                                           


    • Edited by Garry_G Tuesday, November 26, 2019 8:52 PM
    Tuesday, November 26, 2019 8:51 PM

All replies

  • Can you show the raw SQL statement as the code does not indicate any issue so please show the INSERT statement.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, November 26, 2019 9:07 PM
    Moderator
  • Hi Garry_G, 

    Thank you for posting here.

    According to your question, I try to make a test, but I need more information.

    >> INSERT INTO @TableName(" + _temp1 + ") VALUES (" + _temp2 + ")

    Could you provide more information about your database design and the query sentence? It will be beneficial for us to analyze your problem.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 27, 2019 5:45 AM
    Moderator
  •  msCommand.ExecuteNonQuery();        //This errors but the sql this generates does r

    You can debug breakpoint on the line, use Quickwatch on the msCommand object, find the T-SQL in the object that will be executed, copy it to SSMS and execute the T-SQL that will point to the error.

    Wednesday, November 27, 2019 6:01 AM
  • The SQL generated doesn't give an error is SSMS, only when being executed from the code.
    Friday, November 29, 2019 12:52 PM
  • The SQL generated doesn't give an error is SSMS, only when being executed from the code.

    So you are saying is that you stopped code execution at line

     msCommand.ExecuteNonQuery();        //This errors but the sql this generates does r

    on a debug breakpoint, you used Quickwatch on the msCommand object,   copied the formulated T-TQL the command object is about to execute or is going to execute and I am not talking about some other line of code where that is not the line of code above,  and you copied the T-SQL in the msCommand object that now has the T-SQL with the Incorrect syntax. . You copied that T-SQL out of the command object using Quickwatch and tried to execute it in SSMS, right?

    I don't think you did that,  and you got the T-SQL somewhere else or before you hit that above line where the msCommand object has the bad T-SQL it's trying to execute.

    Look around in the command object with Quickwatch and find all occurrences of the T-SQL in it and try to execute it in SSMS. 

    I have used the technique over the years where the code formulated bad T-SQL that can't be seen by the necked-eye, but the syntax error was exposed becuase I used the technique I am talking about to get the bad T-SQL out of the command object just before ExecuteNonQuery is executed and executed the bad T_SQL in SSMS that exposed the location of the syntax error.





    • Edited by DA924x Friday, November 29, 2019 2:03 PM
    Friday, November 29, 2019 1:48 PM
  • I just noticed that this appers to be happening with other SQLite calls as well.

    The simplest I found is this delete:

    E.g. The first option runs fine, the second shows a Syntax error:

    //Option 1

    SQLiteCommand sqLiteCommand1 = new SQLiteCommand("DELETE FROM " + dataTable.TableName, sQLiteConnecter); sqLiteCommand1.ExecuteNonQuery();

    //Option 2

    using (SQLiteCommand sqLiteCommand1 = new SQLiteCommand("DELETE FROM @tableName", sQLiteConnecter)) { sqLiteCommand1.Parameters.Add(new SQLiteParameter("@tableName", dataTable.TableName)); sqLiteCommand1.ExecuteNonQuery(); }



    • Edited by Garry_G Friday, November 29, 2019 1:56 PM
    Friday, November 29, 2019 1:52 PM
  • I just noticed that this appers to be happening with other SQLite calls as well.

    The simplest I found is this delete:

    E.g. The first option runs fine, the second shows a Syntax error:

    //Option 1

    SQLiteCommand sqLiteCommand1 = new SQLiteCommand("DELETE FROM " + dataTable.TableName, sQLiteConnecter); sqLiteCommand1.ExecuteNonQuery();

    //Option 2

    using (SQLiteCommand sqLiteCommand1 = new SQLiteCommand("DELETE FROM @tableName", sQLiteConnecter)) { sqLiteCommand1.Parameters.Add(new SQLiteParameter("@tableName", dataTable.TableName)); sqLiteCommand1.ExecuteNonQuery(); }



    SQLite? So what does SSMS have to do with SQLite, which I have never used?
    Friday, November 29, 2019 2:08 PM
  •  "INSERT INTO @TableName(" ...

    Sorry, this is not valid. SQL Server does not allow the name of the table to be a parameter.

    You can resort to concatenating the table name in the SQL statement. After all you are already concatenating the list of fields and values, so you are not getting any benefit from parametrization anyway.

    Friday, November 29, 2019 2:30 PM
    Moderator