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.