Picture Box Read Image directly from Varbinary

[MrCapuchino]

Hello,

I have an application where I save an image to a database in a column of type VARBINARY(1024) I used 1024 because I think is the number of bytes and I want to handle images no larger than 1MB.

I have my code to save the image and it works.

The thing is that when I try to retrieve the image it tells me Parameter is invalid. I check in the debugger and it brings the byte[] array from the database so the problem is in the MemoryStream I have searched a lot but have not found an answer here is my code to retrieve the image:

string sKey = dgvCatalog.SelectedRows[0].Cells[0].Value.ToString();
            byte[] bImage = new byte[0];
            
            conn.Open();
            cmd = new SqlCommand("SELECT description, picture FROM Books WHERE ISBN ='" + sKey + "';", conn);
            dr = cmd.ExecuteReader();
            dr.Read();
            tbBookDescription.Text = dr[0].ToString();
            bImage = (byte[])dr["picture"];
            MemoryStream ms = new MemoryStream(bImage);            
            pbItemImage.Image = Image.FromStream(ms);
            dr.Close();
            conn.Close();

What am i doing wrong?
Thanks in advance.

[Momerath]

Your varbinary only holds 1024 bytes (1KB) not 1MB. Are your images supposed to be over 1KB in size?

[WildBamaBoy]

Your buffer needs to be 1048576 if you want to get up to a megabyte. Warning though, reading cuts off when the buffer is full so if your image turns out to be bigger than a megabyte you're not going to have the full image and it will throw an exception like it is now.

[MrCapuchino]

Ok so you are telling me that the problem is varbinary(1024) right?

Thanks

[WildBamaBoy]

Try increasing the size to 1048576 and see if it works.

[Momerath]

varbinary takes values up to 8000, or 'max'. This has to do with DB paging, but you don't have to worry about that. Just set it to max and it will hold images up to 2GB (1000 MB).

[MrCapuchino]

Hi,

I changed it to VARBINARY(MAX), I saved the picture to the database, then I retrieve it, funny thing the byte[] array it always gets from the database is of size 1024 and still gives me the parameter is invalid problem. I put my save image code to see if the problem is not generating there, any ideas?

try
                {
                    FileStream fs = new FileStream(sPicture, FileMode.OpenOrCreate, FileAccess.Read);
                    byte[] bImage = new byte[fs.Length];
                    fs.Read(bImage, 0, Convert.ToInt32(fs.Length));
                    fs.Close();

                    conn.Open();
                    cmd = new SqlCommand("INSERT INTO Books VALUES (@ISBN, @title, @author, @publisher, @description, @price, @category, @picture);", conn);
                    cmd.Parameters.Add("@ISBN", SqlDbType.VarChar, 10).Value = tbISBN.Text.Trim();
                    cmd.Parameters.Add("@title", SqlDbType.VarChar, 50).Value = tbTitle.Text.Trim();
                    cmd.Parameters.Add("@author", SqlDbType.VarChar, 50).Value = tbAuthor.Text.Trim();
                    cmd.Parameters.Add("@publisher", SqlDbType.VarChar, 50).Value = tbPublisher.Text.Trim();
                    cmd.Parameters.Add("@description", SqlDbType.VarChar, 200).Value = tbDescription.Text.Trim();
                    cmd.Parameters.Add("@price", SqlDbType.Money).Value = tbPrice.Text.Trim();
                    cmd.Parameters.Add("@category", SqlDbType.VarChar, 30).Value = cbCategory2.Text;
                    cmd.Parameters.Add("@picture", SqlDbType.VarBinary, 1024).Value = bImage;
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Record Saved!");
                }
                catch (SqlException sqle)
                {
                    MessageBox.Show(sqle.ErrorCode.ToString());
                }

[MrCapuchino]

Oh I see the problem hehe

I changed the parameter to just VARBINARY instead of VARBINARY(1024) in the save code and now it works.

Thanks for your help.