C# MySQL syntax error

Question

I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''spectra' WHERE specId=42' at line 1

While running this code:

public System.Drawing.Image GetImage(int index)
{
 using (MySqlCommand command = connection.CreateCommand())
 {
  //command.CommandText = "SELECT imageObj FROM spectra WHERE specId=42"; <== Works OK!

  command.CommandText = "SELECT imageObj FROM @tname WHERE specId=@index";
  command.Parameters.AddWithValue("@index", index);
  command.Parameters.AddWithValue("@tname", "spectra");

  using (MySqlDataReader reader = command.ExecuteReader())
  {
   if (reader.Read())
   {
    return (System.Drawing.Image)Serial.ByteArrayToObject((byte[])reader[0]);
   }
  }
 }
 return null;
}

I think the problems is the quotes around spectra . How can I remove them?

Answer 1

You can't replace a table name with parameters. Sadly, that is just not supported. Only parameter values in the WHERE clause can be substituted this way.

You'll have to do the substitution yourself instead of relying on the MySqlCommand object. Something like this should work:

string tableName = "spectra";
command.CommandText = 
    String.Format( "SELECT imageObj FROM {0} WHERE specId=@index", tableName );
command.Parameters.AddWithValue("@index", index);