C# MySql connection. How to pass image?

Question

I have one class called Functions which will store Insert, Update and Delete functions. The problem is that I cannot figure out how to pass the image(which I get from picturebox). Here's what I tried: Functions class:

 public static Functions Insert(String u, String v, byte[] img)
        {

 String query = string.Format("INSERT INTO example(Name, Description) VALUES ('{0}', '{1}, {2}')", u, v,img);
            MySqlCommand cmd = new MySqlCommand(query, dbConn);

            dbConn.Open();

            cmd.ExecuteNonQuery();

            if (cmd.ExecuteNonQuery() == 1)
            {
                MessageBox.Show("Succesfully added!");

            }

            int id = (int)cmd.LastInsertedId;

            Functions func = new Functions(id,u,v,img);

            dbConn.Close();

            return func;

        }

Button in Form1:

    private void button2_Click(object sender, EventArgs e)
    {

        String u = textBox2.Text;
        String v = textBox3.Text;


        MemoryStream ms = new MemoryStream();
        pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat);
        byte[] img = ms.ToArray();


        currf = Functions.Insert(u, v, img);
    }

Here's the error message:

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Additional information: Field 'Image' doesn't have a default value

You specify two columns and give three values. You’re trying to use String.Format with a byte array. Use parameters. — Sami Kuhmonen, Apr 14 '18 at 11:20
Possible duplicate of [C# saving images to MySql database as blob](https://stackoverflow.com/questions/34358528/c-sharp-saving-images-to-mysql-database-as-blob) — ProgrammingLlama, Apr 14 '18 at 11:20

score 1 · Accepted Answer · answered Apr 14 '18 at 11:21

1

Using parameters in a query is pretty simple. First you prepare the sql command text using placeholders for the parameters. Then you add the parameters to the MySqlCommand.Parameters collection, finally you pass everything to the database engine that use the parameters collection to properly insert your data into the underlying table

// Notice that you are missing the third field (the image one)
// Please replace Image with the correct name of the image field in your table
string query = @"INSERT INTO example (Name, Description, Image) 
                 VALUES (@name, @description, @img";
MySqlCommand cmd = new MySqlCommand(query, dbConn);
cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = u;
cmd.Parameters.Add("@description", MySqlDbType.VarChar).Value = v;
cmd.Parameters.Add("@img", MySqlDbType.Binary).Value = img;
dbConn.Open();
// Do not execute the query two times.
// cmd.ExecuteNonQuery();
if (cmd.ExecuteNonQuery() == 1)
{
    MessageBox.Show("Succesfully added!");
    int id = (int)cmd.LastInsertedId;
    ....
}
else
{
    // failure msg ?
}

answered Apr 14 '18 at 11:21

Steve

213,761
22
232
286

Thanks! I tried it, but it gives me a new kind of exception that I can not figure out where's coming from: Fatal error encountered during command execution. – Freak0345 Apr 14 '18 at 11:57
On which line do you get the exception? Can you please add the definition of the table _example_ used in the code above? – Steve Apr 14 '18 at 12:13
1

Edit: I found what's the problem from InnerException. It was @description parameter. Thanks again. – Freak0345 Apr 14 '18 at 12:18
Is it something that need to be fixed in the answer above? – Steve Apr 14 '18 at 12:22
No, it was my bad. I just used different names for the description parameter. – Freak0345 Apr 14 '18 at 12:27

C# MySql connection. How to pass image?

1 Answers1