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.
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?