CODEWITHSUNDEEP
c#sql
Fred
Fred

Reputation: 2478

C# altering SQL table

I have this code:

try
{
    OpenDatabaseConnection();
    sql += @"ALTER TABLE @TableName " +
            "ADD @ColumnName @DataType(@Size)";

    using (SqlCommand command = conn.CreateCommand())
    {
        command.CommandText = sql;
        command.Parameters.Add("@TableName", SqlDbType.VarChar).Value = tableName;
        command.Parameters.Add("@ColumnName", SqlDbType.NVarChar).Value = columnName;
        command.Parameters.Add("@DataType", SqlDbType.NVarChar).Value = dataType;
        command.Parameters.Add("@Size", SqlDbType.NVarChar).Value = size;

        command.ExecuteNonQuery();
    }
    CloseDatabaseConnection();
}
catch (Exception ex)
{
     ArrowMessageBoxes.ArrowErrorMessage(ex.Message);
}
finally
{
     CloseDatabaseConnection();
}

and when this code runs, I get the following error: Incorrect syntax near '@TableName'.

There must be something I am missing.

Upvotes: 0

Views: 247

Answers (2)

Fred
Fred

Reputation: 2478

sql += @"ALTER TABLE [" + tableName + "] " +
        "ADD [" + columnName + "] " + dataType + " (" + size + ")";

Upvotes: -1

lc.
lc.

Reputation: 116528

DDL can't contain parameters. If you insist on creating/altering tables and columns dynamically, you'll need to create dynamic SQL and escape very, very carefully.

Upvotes: 4

Related Questions