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

Question

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-85ALBAVS\SQLEXPRESS;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)

Your `using` statements are actually not doing anything: part of the commented lines should go inside the `Using cmd ...` statement. Refer to this question: https://stackoverflow.com/q/23185990/1732224 (it's c#, but I think that you should be able to see the problem). — Fer García, Sep 26 '18 at 16:50
Remember that `Using` statements are just "containers" that help with creation and destruction of certain objects; they're often used to access unmanaged resources (like database connections or files) in a controlled way. — Fer García, Sep 26 '18 at 16:59
Despite its appealing name, you probably *don't* want to be using the [`image` data type](https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-2017). It's been deprecated since *2005*! — Damien_The_Unbeliever, Sep 28 '18 at 06:06
@Damien_The_Unbeliever Oh, good to know that tho. Is `varbinary(MAX)` is more recommended instead of `image` data type? — not_Prince, Sep 28 '18 at 06:15

score 1 · Accepted Answer · answered Sep 28 '18 at 05:51

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-85ALBAVS\SQLEXPRESS;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

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

1 Answers1