Issue with executing an SQL command using C#

[Garry_G]

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;
a2 = _names;
//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());

Continue reading...