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?

Keep in mind, you can't cast a byte to byte[]...

Cole Tobin
– Cole Tobin

2012-06-20 00:33:06 +00:00
Commented Jun 20, 2012 at 0:33 — Cole Tobin
– Cole Tobin, Commented Jun 20, 2012 at 0:33
Irrelevant.. Function works fine when the syntax is ok.

Igor
– Igor

2012-06-20 00:35:06 +00:00
Commented Jun 20, 2012 at 0:35 — Igor
– Igor, Commented Jun 20, 2012 at 0:35

Katie Kilian · Accepted Answer · 2012-06-20 00:32:46Z

4

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);

answered Jun 20, 2012 at 0:32

Katie Kilian

7,0335 gold badges45 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Igor Over a year ago

Works fine now.. Thanks for the fast response.

Collectives™ on Stack Overflow

C# MySQL syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related