Click here to Skip to main content
13,797,135 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

2.1K views
1 bookmarked
Posted 21 Sep 2018
Licenced CPOL

Passing DbNull.Value to an SQL Image column

, 21 Sep 2018
Rate this:
Please Sign up or sign in to vote.
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:

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.

License

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

Share

About the Author

OriginalGriff
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 2 Pin
dmjm-h25-Sep-18 14:40
memberdmjm-h25-Sep-18 14:40 
QuestionOut of curiosity Pin
Wendelius21-Sep-18 21:24
mentorWendelius21-Sep-18 21:24 
Answerno overload with this signature Pin
Peter BCKR21-Sep-18 0:58
memberPeter BCKR21-Sep-18 0:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.181207.3 | Last Updated 21 Sep 2018
Article Copyright 2018 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid