Save a picture into database without binding source

Question

I tried to save a picture into database using Sqlcommand . When I save , there is an exception throw said " Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query." here is the code:

private void btn_save_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        SqlCommand cmd1= new SqlCommand();
        SqlCommand cmd2 = new SqlCommand();
        string squ1;

        squ1 = "INSERT INTO Customer (cus_name, cus_address, cus_Image)Values('" + textBox1.Text + "' , '" + textBox2.Text  + "', '"+pictureBox1 .Image +"');";

        con.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\ProgramData\MyDB\TestingDB.mdf;Integrated Security=True;Connect Timeout=30";
        con.Open();

        cmd1.Connection = con;
        cmd1.CommandText = squ1;
        cmd1.ExecuteNonQuery();
        con.Close ();
    }
    // the browser button to get a picture
    private void btn_browseImage_Click(object sender, EventArgs e)
    {
        OpenFileDialog f = new OpenFileDialog();
        if (f.ShowDialog () == DialogResult .OK )
        {
            pictureBox1.ImageLocation  = f.FileName;
        }

@kimoshisagat I would recommend using parameterized query's do a google search on that as well as how to store images into a database of datatype `varbinary` — MethodMan, May 12 '15 at 19:42

score 1 · Answer 1 · answered May 12 '15 at 19:47

You have to pass the image data as a varbinary parameter to the query:

using (var con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\ProgramData\MyDB\TestingDB.mdf;Integrated Security=True;Connect Timeout=30"))
using (var cmd1 = new SqlCommand("INSERT INTO Customer (cus_name, cus_address, cus_Image)Values(@name, @address, @image);", con))
{
    var imageData = new MemoryStream();
    pictureBox1.Image.Save(imageData, pictureBox1.Image.RawFormat);

    cmd1.Parameters.AddWithValue("@name", textBox1.Text);
    cmd1.Parameters.AddWithValue("@address", textBox2.Text);
    cmd1.Parameters.Add("@image", SqlDbType.VarBinary).Value = imageData.ToArray();


    con.Open();
    var result = cmd1.ExecuteNonQuery();
}

And you should really read up on how to use SqlCommand to avoid future SQL injection.

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

The statement '"+pictureBox1.Image +"' will actually call pictureBox1.Image.ToString() which is not the binary content of the image. Use SqlParameters to add your binary data. You can find a solution here...

edited May 23 '17 at 12:06

Community

1
1

answered May 12 '15 at 19:38

Peter Schneider

2,879
1
14
17

Please include basic information from that link, so others may get a rough idea of what it includes. – Der Kommissar May 12 '15 at 19:38
The basic information is that you should use SqlParameters and provide binary data. Actually this question is a duplicate! – Peter Schneider May 12 '15 at 19:42

Save a picture into database without binding source

2 Answers2