How to insert null value to image column in SQL Server c#

I have been trying to figure out how to save image into database with both null and image values. For my code it saves the image but if the image is missing it does not save a null value.

public string STDNAME { get; set; }
public string Image { get; set; }
DateTime Date1 = DateTime.Now;


This the code that I used to save the data

public string imagepath { get; set; }

public bool Insert(StudentC c)
{
bool isSuccess = false;

SqlConnection conn = new SqlConnection(myconnstring);

try
{
byte imageBT = null;

FileStream fstream = new FileStream(this.Image, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fstream);
imageBT = br.ReadBytes((int)fstream.Length);
string sql = "INSERT INTO STUDENT (STDNAME,imagepath,Image,Date) VALUES (@STDNAME,@imagepath,@Image,@Date)";

SqlCommand cmd = new SqlCommand(sql, conn);

cmd.Parameters.AddWithValue("@STDNAME", c.STDNAME);
cmd.Parameters.AddWithValue("@imagepath", c.imagepath);
cmd.Parameters.AddWithValue("@Image", imageBT);
cmd.Parameters.AddWithValue("@Date", Date1);

conn.Open();

int rows = cmd.ExecuteNonQuery();

if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine("nMessage ---n{0}", ex.Message);
}
finally
{
conn.Close();
}

return isSuccess;
}


This code is for browsing the image

//browse image
private void button6_Click(object sender, EventArgs e)
{
OpenFileDialog f = new OpenFileDialog();
f.Filter = "All Files|*.*|JPEGs|*.jpg|Bitmaps|*.bmp|GIFs|*.gif";
f.FilterIndex = 2;

if (f.ShowDialog() == DialogResult.OK)
{
pictureBox2.Image = Image.FromFile(f.FileName);
pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
pictureBox2.BorderStyle = BorderStyle.Fixed3D;
textBox7.Text = f.SafeFileName.ToString();
string picPath = f.FileName.ToString();
textBox7.Text = picPath;
pictureBox2.ImageLocation = picPath;
}
}


This is the code to supplies the values to store

private void button5_Click(object sender, EventArgs e)
{
c.STDNAME = textBox2.Text;

c.Image = textBox7.Text;
c.imagepath = textBox7.Text;

bool success = c.Insert(c);

if (success == true)
{
MessageBox.Show("Data has been saved");
//Clear();
}
else
{
// label4.Text = "Data Has not been saved";
MessageBox.Show("Data has not been saved");
}
}


2 Answers
2

For adding adding null to the image column, make sure you specify the type (e.g. VarBinary) as the example below. In addition, make sure the image column accepts null.

cmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = DBNull.Value;


Moreover, the following approach may lead to the exception further below:

cmd.Parameters.AddWithValue("@Image", DBNull.Value);


--- Exception ---

System.Data.SqlClient.SqlException (0x80131904): Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.


Before creating the table, just make the column of image accepts null values.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.