How do i handle a multiple null image values from Microsoft SQL Server database with vb.net code

Question

I was hoping someone could point me in the right direction with my current code. I'm getting an error while opening my program:

Data is Null. This method or property cannot be called on null values.

I put an else statement for btn.backgroundimage but still get that error.

Here is what my code looks like:

Sub FillItems()
    Try
        con = New SqlConnection(cs)
        con.Open()
        Dim PictureCol As Integer = 1 ' the column # of the BLOB field
        Dim cmdText1 As String = "SELECT RTRIM(ProductName),Image from Temp_Stock_Company INNER JOIN Product ON Product.PID=Temp_Stock_Company.ProductID where ShowPOS='Yes'"
        cmd = New SqlCommand(cmdText1)
        cmd.Connection = con
        cmd.CommandTimeout = 0
        rdr = cmd.ExecuteReader()
        flpItems.Controls.Clear()
        Do While (rdr.Read())
            'Dim btn As New Button
            'btn.Text = rdr.GetValue(0)
            'btn.TextAlign = ContentAlignment.MiddleCenter
            'btn.BackColor = Color.SteelBlue
            'btn.ForeColor = Color.White
            'btn.FlatStyle = FlatStyle.Popup
            'btn.Width = 125
            'btn.Height = 60
            'btn.Font = New System.Drawing.Font("Microsoft Sans Serif", 10.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
            'UserButtons.Add(btn)
            'flpItems.Controls.Add(btn)
            Dim b(rdr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
            rdr.GetBytes(PictureCol, 0, b, 0, b.Length)
            Dim ms As New System.IO.MemoryStream(b)
            Dim Dflp As New FlowLayoutPanel
            Dflp.Size = New System.Drawing.Size(197, 197)
            Dflp.BackColor = Color.Black
            Dflp.BorderStyle = BorderStyle.None
            Dflp.FlowDirection = FlowDirection.TopDown
            Dim btn As New Button
            Dim btnX As New Button
            btn.Text = rdr.GetValue(0)
            btn.Width = 197
            btn.Height = 197
            If DBNull.Value.Equals(rdr(1)) = False Then
                btn.BackgroundImage = Image.FromStream(ms)
                btn.BackgroundImageLayout = ImageLayout.Stretch
            Else
                btn.BackgroundImage = My.Resources._12
                btn.BackgroundImageLayout = ImageLayout.Stretch
            End If
            btn.FlatStyle = FlatStyle.Flat
            btn.FlatAppearance.BorderSize = 0
            btn.Text = rdr.GetValue(0)
            btn.Font = New System.Drawing.Font("Segoe UI Semibold", 1.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
            btn.TextAlign = System.Drawing.ContentAlignment.BottomCenter
            btn.ForeColor = System.Drawing.Color.Black
            btnX.Text = rdr.GetValue(0)
            btnX.FlatStyle = FlatStyle.Flat
            btnX.Width = 0
            btnX.Height = 0
            btnX.FlatAppearance.BorderSize = 0
            btnX.Text = rdr.GetValue(0)
            btnX.Font = New System.Drawing.Font("Segoe UI Semibold", 8.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
            btnX.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
            btnX.ForeColor = Color.White
            btnX.BackColor = Color.SteelBlue
            UserButtons.Add(btn)
            UserButtons.Add(btnX)
            Dflp.Controls.Add(btn)
            Dflp.Controls.Add(btnX)
            flpItems.Controls.Add(Dflp)
            AddHandler btn.Click, AddressOf Me.btnItems_Click
            AddHandler btnX.Click, AddressOf Me.btnItems_Click
        Loop
        con.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
    End Try
End Sub

im guessing the issue is between line 37-43 because it doesnt seem to handle the null values — Trends Ave, Jan 23 '21 at 21:33
Remove the try/catch so the program will stop in Visual Studio debugger at the exact line where the error occurs. And line numbers are not really helpful here — Steve, Jan 23 '21 at 21:35
System.InvalidOperationException was unhandled HResult=-2146233079 Message=The port is closed. Source=System — Trends Ave, Jan 23 '21 at 21:41
You probably got a `DBNull` in that column. You should really use `rdr.IsDBNull(0)` — Charlieface, Jan 23 '21 at 21:42
**See also https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice for best practice with ADO.Net** You need to dispose your connection and reader objects with `Using` — Charlieface, Jan 23 '21 at 21:42
charlieface where would i input that: If rdr.IsDBNull(0) = False Then btn.BackgroundImage = Image.FromStream(ms) btn.BackgroundImageLayout = ImageLayout.Stretch Else btn.BackgroundImage = My.Resources._12 btn.BackgroundImageLayout = ImageLayout.Stretch End If — Trends Ave, Jan 23 '21 at 21:45
No you want that on the line `Dim b...` although personally, instead of `GetBytes`, I would just use `Dim b = If(rdr.IsDBNull(0), null, DirectCast(rdr.GetValue(0) As Byte()))` and then make sure to check for null when you convert to image lower down. — Charlieface, Jan 24 '21 at 02:03
That is a long time to hold a connection open, updating the user interface and all. Just fill a DataTable, close and dispose the connection. Then play with your data. — Mary, Jan 25 '21 at 05:53
@Steve you don't need to remove try/catch for Visual Studio debugger to stop where an exception is thrown. You just need to change the exception settings for the particular exception type from the default break if unhandled to break if thrown. Exception settings are under Debug / Windows / Exception Settings. — Craig, Jan 25 '21 at 14:15

score 0 · Answer 1 · answered Jan 25 '21 at 08:23

You do not want to hold the connection open while you update the user interface so I have used a DataTable which will hold your data after the connection is closed and disposed. Using...End Using blocks will close and dispose your database objects. Any class in the framework that shows you a .Dispose method should be enclosed in Using blocks. This includes streams.

Load the DataTable. Check for DBNull and only then get your byte array.

I used AdventureWorks (a sample database available from Microsoft) to test.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim dt As New DataTable
    Using cn As New SqlConnection(My.Settings.Adventure),
                    cmd As New SqlCommand("Select Top 10 ProductPhotoID, ThumbNailPhoto From Production.ProductPhoto;", cn)
        cn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    For Each row As DataRow In dt.Rows
        Dim btn As New Button
        If Not IsDBNull(row(1)) Then
            Dim b() As Byte = DirectCast(row(1), Byte())
            Using ms As New System.IO.MemoryStream(b)
                btn.BackgroundImage = Image.FromStream(ms)
            End Using
        Else
            btn.BackgroundImage = Image.FromFile("C:\Users\***\Desktop\Graphics\TreeFrog.jpg")
        End If
        btn.BackgroundImageLayout = ImageLayout.Stretch
        btn.Width = 50
        btn.Height = 50
        FlowLayoutPanel1.Controls.Add(btn)
    Next
End Sub

How do i handle a multiple null image values from Microsoft SQL Server database with vb.net code

1 Answers1