CODEWITHSUNDEEP
c#sql-serverdatagridviewpicturebox
Ole M
Ole M

Reputation: 347

C#/SQL: Varbinary(max) column to DataGridView to Picturebox

I am absolutely befuddled at the moment. Kind madams, good sirs; please do assist!

This is what I am doing:

I have a WinForms application connected to a SQL database with a varbinary(max) column for storing pictures. In my parent form (form1), I have a DataGridView (dgv1). Double-clicking on any row will bring up a new form (form2), where I can edit the row data, and where there's also a PictureBox (pb1).

If I add a picture, and then go back and refresh the DataGridView, and try to open the same row, it'll give me this error message:

errormessage

If I go into the database and set the value of the varbinary(max) column back to NULL (from < Binary data >), and try to open the same row again, it'll work fine.

Let me show you my code:

// this runs when I double click a row in dgv1

ChildForm myChildForm = new ChildForm();
myChildForm.txt1.Text = dgv1.CurrentRow.Cells[0].Value.ToString();
myChildForm.txt2.Text = dgv1.CurrentRow.Cells[1].Value.ToString();
myChildForm.txt3.Text = dgv1.CurrentRow.Cells[2].Value.ToString();

if (DBNull.Value != dgv1.CurrentRow.Cells[3].Value) {
    MemoryStream mStream = new MemoryStream((byte[])dgv1.CurrentRow.Cells[3].Value);
    myChildForm.pb1.Image = Image.FromStream(mStream);

myChildForm.ShowDialog();

// this is on a button, and how I send the edited info from *form2* to the database

byte[] imageData;

    using (SqlConnection sqlCon = new SqlConnection(connectionString))
    using (SqlCommand sqlCmd = new SqlCommand())
    using (MemoryStream mStream = new MemoryStream()) {

        sqlCon.Open();
        sqlCmd.Connection = sqlCon;
        sqlCmd.Parameters.AddWithValue("@Parameter1", txt1.Text.ToString());
        sqlCmd.Parameters.AddWithValue("@Parameter2", txt2.Text.ToString());
        sqlCmd.Parameters.AddWithValue("@Parameter2", txt2.Text.ToString());

        imageData = mStream.ToArray();
        sqlCmd.Parameters.AddWithValue("@Parameter3", SqlDbType.VarBinary).Value = imageData;
        pb1.Image.Save(mStream, ImageFormat.Jpeg);

        SqlCmd.ExecuteNonQuery();

If anyone can give me any hints as to what I'm doing wrong, I'd appreciate that a bunch! Thanks!

Upvotes: 0

Views: 696

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89090

Well, first off imageData is going to be an empty byte[] because you're copying from the MemoryStream before you write the image data to the stream.

imageData = mStream.ToArray();
sqlCmd.Parameters.AddWithValue("@Parameter3", SqlDbType.VarBinary).Value = imageData;
pb1.Image.Save(mStream, ImageFormat.Jpeg);

At a minimum, write the data to the MemoryStream before copying it to the byte[] and create the SqlParameter properly.

pb1.Image.Save(mStream, ImageFormat.Jpeg);
imageData = mStream.ToArray();
var pImg = sqlCmd.Parameters.Add("@Parameter3",SqlDbType.VarBinary, -1);
pImg.Value = imageData;

Upvotes: 1

Related Questions