Class 'System.DBNull' cannot be indexed because it has no default property

Question

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        cn.Open()
        Dim arrimage() As Byte
        Dim ms As New MemoryStream()
        If (pb1.Image IsNot Nothing) Then
            pb1.Image.Save(ms, pb1.Image.RawFormat)
            arrimage = ms.GetBuffer
            ms.Close()
        End If
        With cmd
            .Connection = cn
            .CommandText = "INSERT INTO [Example]([PName],[Pic])VALUES(@a2,@a1)"
            .Parameters.Add("a0", OleDbType.VarChar).Value = tName.Text
            .Parameters.Add("a1", OleDbType.Binary).Value = IIf(pb1.Image IsNot Nothing, arrimage, DBNull.Value())
            .Dispose()
            .ExecuteNonQuery()
        End With
        cn.Close()
    End Sub

Answer 1

You have multiple issues in your code. In order of appearance:

Dont use GetBuffer()

As noted on MSDN, the buffer can be up to twice the size of the data in the stream. This will bloat the database needlessly with extra nulls. Use ToArray() instead.

Since images in a DB have to be converted to and from a byte array, consider archiving the images to a folder and store just the name in the database. You can then prepend the archive folder name to load an image quickly.

Rather than RawFormat, I would encode it to something like JPEG.

Use Using blocks

Anything which has a .Dispose method usually needs to be disposed. This would apply to the OleDBCommand object (MemStream really doesnt need to be disposed, but that is an implementaion detail).

Using blocks incoporate Dim, New and Dispose in one handy, easy to use block:

Using foo As New FooBar()
   ...
End Using

The first line declares a foo variable, and creates an instance of FooBar which you can use inside the block. At the end, it is disposed of automatically.

Don't Use Global DBCommand objects

Your code does not show cmd being declared or created, so it must be a form level object. Don't Do That. There is nothing reusable about a DBCommand object unless all your app does is one thing.

In the code you add 2 parameters. The next time you go to use it, it could still have those 2 and the code will add 2 more which is more than the SQL query requires. In this case, the code disposes of it, but that means the next time you go to reference it you will get an ObjectDisposedException.

• As noted, your code calls Dispose before ExecuteNonQuery which will crash - you cant use a disposed object.

DBNull.Value is not a method

As for the compiler error, you have this:

IIf(pb1.Image IsNot Nothing, arrimage, DBNull.Value())

DBNull.Value is a property, not a method, so the parens are not needed. Also, you should use the "new" If operator rather than the old IIF function. The operator is short-circuited so the part/clause that does not apply is ignored:

' should evaluate the data (arrimage) not its source
If(pb1.Image IsNot Nothing, arrimage, DBNull.Value)   ' no parens

Revamped code:

cn.Open()
Dim arrimage() As Byte = Nothing

If (pb.Image IsNot Nothing) Then
    Using ms As New MemoryStream()
        pb.Image.Save(ms, ImageFormat.Jpeg)
        arrimage = ms.ToArray()
    End Using
End If

Dim sql = "INSERT INTO [Example]([PName],[Pic]) VALUES (@a2,@a1)"

Using cmd As New OleDbCommand(sql, cn)
    cmd.Parameters.Add("a0", OleDbType.VarChar).Value = tName.Text
    If arrimage IsNot Nothing Then
        cmd.Parameters.Add("a1", OleDbType.VarBinary).Value = arrimage 
    Else
        cmd.Parameters.Add("a1", OleDbType.VarBinary).Value = DBNull.Value
    End If
    cmd.ExecuteNonQuery()
End Using

cn.Close()

• Since the command object is useless without both the query and the connection, I prefer to pass them in the constructor. It makes the code shorter as well as assures that it has what it needs
• Connections also ought to be created, used and disposed of each time
• It is also easy to create a handy extension method for converting an image to a byte array