Skip to content
Advertisement

VB.NET > How to insert an image with Using statement and retrieve it to be displayed?

This is the code I’m working on it to insert an image into database. I think there is nothing wrong with the first part. However, somehow this code is not functioning as it supposed to (no image inserted into database). I’m not sure whether the commented part of code is related to the execution or not. How should I develop it in a more viable manner? This command is intended to be executed along with saving a list of typed in data into database. Any help would be nice.

    Using con As SqlConnection = New SqlConnection("Data Source=LAPTOP-85ALBAVSSQLEXPRESS;Initial Catalog=Portal;Integrated Security=True")
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO Photo (Img, Pid) VALUES (@Img, @Pid)", con)
            Using ms As New MemoryStream
                pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat)

                cmd.Parameters.Add("@Img", SqlDbType.Image).Value = ms.ToArray()
                cmd.Parameters.Add("@Pid", SqlDbType.VarChar).Value = TextBox1.Text
            End Using
        End Using
    End Using

    'con.Open()

    'If Command.ExecuteNonQuery() = 1 Then
    '    MessageBox.Show("Profile successfully registered!", "Message", MessageBoxButtons.OK)
    'Else
    '    MessageBox.Show("Failed. Try again.", "Message", MessageBoxButtons.OK)
    'End If
    'con.Close()
End Sub

This is the code I’m using to retrieve image from database and display it. There is a glitch with this code. If no image found related to the specific Pid, it would pop-up error 'There is no row at position 0.'(means no image was uploaded). But this doesn’t affects the overall program execution much, as long as the image is uploaded along with the typed in data together successfully, it would be fine.

Dim command As New SqlCommand("Select * From Photo Where Pid = @Pid", con)
    command.Parameters.Add("@Pid", SqlDbType.VarChar).Value = TextBox1.Text
    Dim table As New DataTable()
    Dim adapter As New SqlDataAdapter(command)
    adapter.Fill(table)
    Dim img() As Byte
    img = table.Rows(0)(0)
    Dim ms As New MemoryStream(img)
    pictureBox1.Image = Image.FromStream(ms)

Answer

I fixed the issue and this is the code for it. Do feel free to check it out and hope it helps someone in future 🙂

Using con As SqlConnection = New SqlConnection("Data Source=LAPTOP-85ALBAVSSQLEXPRESS;Initial Catalog=Portal;Integrated Security=True")
            Using cmd As SqlCommand = New SqlCommand("INSERT INTO Photo (Img, Pid) VALUES (@Img, @Pid)", con)
                Using ms As New MemoryStream
                    pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat)

                    cmd.Parameters.Add("@Img", SqlDbType.Image).Value = ms.ToArray()
                    cmd.Parameters.Add("@Pid", SqlDbType.VarChar).Value = TextBox1.Text
                    con.Open()
                    cmd.ExecuteNonQuery()
                    MessageBox.Show("Profile has been successfully registered!", "Thank you", MessageBoxButtons.OK)

                End Using
            End Using
        End Using