Passing DbNull.Value to an SQL Image column

Introduction

This came from a QA question, and I figured that with a "straight" AddWithValue parameter the system didn't know what type of column it was going into, so this threw an exception:

try
    {
    using (SqlConnection con = new SqlConnection(strConnect))
        {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("INSERT INTO MyTable (Photo) VALUES (@P)", con))
            {
            cmd.Parameters.AddWithValue("@P", DBNull.Value);
            int result = cmd.ExecuteNonQuery();
            }
        }
    }
catch (Exception ex)
    {
    Console.WriteLine(ex.Message);
    }

OK, I thought - easy: tell it!

try
    {
    using (SqlConnection con = new SqlConnection(strConnect))
        {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("INSERT INTO MyTable (Photo) VALUES (@P)", con))
            {
            cmd.Parameters.AddWithValue("@P", SqlDbType.Image).Value = DBNull.Value;
            int result = cmd.ExecuteNonQuery();
            }
        }
    }
catch (Exception ex)
    {
    Console.WriteLine(ex.Message);
    }

But ... no. That throws the same error.

The solution

After some playing, I found that if you create the SqlParameter and pass it, it's nasty, but it works:

try
    {
    using (SqlConnection con = new SqlConnection(strConnect))
        {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("INSERT INTO MyTable (Photo) VALUES (@P)", con))
            {
            SqlParameter ip = new SqlParameter("@P", SqlDbType.Image);
            ip.Value = DBNull.Value;
            cmd.Parameters.Add(ip);
            int result = cmd.ExecuteNonQuery();
            }
        }
    }
catch (Exception ex)
    {
    Console.WriteLine(ex.Message);
    }

Or in VB:

Try

    Using con As SqlConnection = New SqlConnection(strConnect)
        con.Open()

        Using cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable (Photo) VALUES (@P)", con)
            Dim ip As SqlParameter = New SqlParameter("@P", SqlDbType.Image)
            ip.Value = DBNull.Value
            cmd.Parameters.Add(ip)
            Dim result As Integer = cmd.ExecuteNonQuery()
        End Using
    End Using
Catch ex As Exception
    Console.WriteLine(ex.Message)
End Try

Why? Dunno - without dragging through the reference sources I have no idea. And I lack the time for that right now...

History

2018-10-21 First version.