Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Passing DbNull.Value to an SQL Image column

4.00/5 (4 votes)
21 Sep 2018CPOL 10.7K  
If you try to pass a DbNull.Value to an SQL Image column, you get an exception: Operand type clash: nvarchar is incompatible with image - not sure why it's throwing that, but it can be done.

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:

C#
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!

C#
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:

C#
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:

VB.NET
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)