"System.OutOfMemoryException: 'Out of memory.'" when reading image from SQL Serv-CodePudding

I have images assigned to every button in my VB.NET form, the images come from SQL Server. The data type is varbinary(MAX).

This is my code:

Using con As New SqlConnection("con string")
    Dim sql As String = "SELECT * FROM Inventory WHERE ID=@ID"
    Using cmd As New SqlCommand(sql, con)
        cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = 3
        con.Open()
        Using myreader As SqlDataReader = cmd.ExecuteReader()
            If myreader.Read() AndAlso Not DBNull.Value.Equals(myreader("Image")) Then
                Boton3.Text = myreader("Item")
                Boton3.Enabled = myreader("ONOFF")
                Dim ImgSql() As Byte = DirectCast(myreader("Image"), Byte())
                Using ms As New MemoryStream(ImgSql)
                    Boton3.BackgroundImage = Image.FromStream(ms)
                    con.Close()
                End Using
            Else
                Boton3.Text = myreader("Item")
                Boton3.BackgroundImage = Nothing
                Boton3.Enabled = myreader("ONOFF")
            End If
        End Using
    End Using
End Using

The platform is 64bit. I'm thinking it might have to do with not disposing properly, but I'm not sure since I'm new to coding.

EDIT SHOWING NEW CODE AND HOW I RETRIVE MORE THAN ONE RECORD:

Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
        Dim dt As DataTable
        Try
            dt = GetInventoryDataByID(1)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try
        If dt.Rows.Count > 0 Then
            Boton1.Text = dt.Rows(0)("Articulo").ToString
            Boton1.Enabled = CBool(dt.Rows(0)("ONOFF"))
            If Not DBNull.Value.Equals(dt.Rows(0)("Imagen")) Then
                Dim ImgSql() As Byte = DirectCast(dt.Rows(0)("Imagen"), Byte())
                Using ms As New MemoryStream(ImgSql)
                    Boton1.BackgroundImage = Image.FromStream(ms)
                End Using
            Else
                Boton1.BackgroundImage = Nothing
            End If
        Else
            MessageBox.Show("No records returned")
        End If
        Dim dt2 As DataTable
        Try
            dt2 = GetInventoryDataByID(2)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try
        If dt2.Rows.Count > 0 Then
            Boton2.Text = dt2.Rows(0)("Articulo").ToString
            Boton2.Enabled = CBool(dt2.Rows(0)("ONOFF"))
            If Not DBNull.Value.Equals(dt2.Rows(0)("Imagen")) Then
                Dim ImgSql() As Byte = DirectCast(dt2.Rows(0)("Imagen"), Byte())
                Using ms As New MemoryStream(ImgSql)
                    Boton2.BackgroundImage = Image.FromStream(ms)
                End Using
            Else
                Boton2.BackgroundImage = Nothing
            End If
        Else
            MessageBox.Show("No records returned")
        End If
    End Sub
    Private Function GetInventoryDataByID(id As Integer) As DataTable
        Dim dt As New DataTable
        Dim sql As String = "SELECT Imagen, Articulo, ONOFF FROM Inventario WHERE ID=@ID"
        Using con As New SqlConnection("CON STRING"),
            cmd As New SqlCommand(sql, con)
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id
            con.Open()
            Using myreader As SqlDataReader = cmd.ExecuteReader()
                dt.Load(myreader)
            End Using
        End Using
        Return dt
    End Function
End Class

CodePudding user response：

You don't want to hold a connection open while you update the user interface. Separate you user interface code from your database code.

If you put a comma at the end of the first line of the outer Using block, both the command and the connection are included in same block. Saves a bit of indenting.

You are passing an integer to the @ID parameter but you have set the SqlDbType as a VarChar. Looks like a problem. I changed the SqlDbType to Int.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt As DataTable
    Try
        dt = GetInventoryDataByID(3)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try
    If dt.Rows.Count > 0 Then
        Boton3.Text = dt.Rows(0)("Item").ToString
        Boton3.Enabled = CBool(dt.Rows(0)("ONOFF"))
        If Not DBNull.Value.Equals(dt.Rows(0)("Image")) Then
            Dim ImgSql() As Byte = DirectCast(dt.Rows(0)("Image"), Byte())
            Using ms As New MemoryStream(ImgSql)
                Boton3.BackgroundImage = Image.FromStream(ms)
            End Using
        Else
            Boton3.BackgroundImage = Nothing
        End If
    Else
        MessageBox.Show("No records returned")
    End If
End Sub

Private Function GetInventoryDataByID(id As Integer) As DataTable
    Dim dt As New DataTable
    Dim sql As String = "SELECT * FROM Inventory WHERE ID=@ID"
    Using con As New SqlConnection("con string"),
            cmd As New SqlCommand(sql, con)
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id
        con.Open()
        Using myreader As SqlDataReader = cmd.ExecuteReader()
            dt.Load(myreader)
        End Using
    End Using
    Return dt
End Function

EDIT Add Dispose on image

    If Not DBNull.Value.Equals(dt.Rows(0)("Image")) Then
        Dim ImgSql() As Byte = DirectCast(dt.Rows(0)("Image"), Byte())
        Using ms As New MemoryStream(ImgSql)
            If Boton3.BackgroundImage IsNot Nothing Then
                Boton3.BackgroundImage.Dispose()
            End If
            Boton3.BackgroundImage = Image.FromStream(ms)
        End Using
    Else
        If Boton3.BackgroundImage IsNot Nothing Then
            Boton3.BackgroundImage.Dispose()
        End If
    End If

CodePudding user response：

I resolved this issue by simply not using buttons. Instead I used pictureboxes as buttons and that resolved the issue. Im guesssing the problem is that buttons don't allow as much memory as pictureboxes.