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

Question

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");
    }
}

if the image is missing it does not save a null value so what does it save? And which part of all this code tells the DB to store a null value? — stuartd
– stuartd, Commented Jun 29, 2018 at 23:01
Also, to set a value as null in sql server you must pass DBNull.Value as your parameter. — Sal
– Sal, Commented Jun 29, 2018 at 23:13
What I've done in the past is leave the field that you want NULL out of the INSERT SQL. I know this can be a pain, but it works! — MikeAinOz
– MikeAinOz, Commented Jun 30, 2018 at 0:45

Evandro de Paula · Accepted Answer · 2018-06-29 23:39:14Z

5

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.

answered Jun 29, 2018 at 23:39

Evandro de Paula

2,6522 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evandro de Paula Over a year ago

Could you provide more details (which approach did you use? exception? scenario?)?

Float · Accepted Answer · 2024-08-01 01:46:13Z

0

thanks to @Evandro de Paula cmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = DBNull.Value; it's work for me command.Parameters.Add("@IMGNV", SqlDbType.VarBinary).Value = dataRow["IMGNV"]; type of IMGNV is varbinary(MAX)

answered Aug 1, 2024 at 1:46

Float

392 bronze badges

Comments

Mansoor · Accepted Answer · 2020-01-30 17:30:08Z

-1

**Very Simple Solution

C# Text

query = "insert into Customer (CustomerCode,LdegerCode,CustomerPicture) values ('0001','9999',NULL)"

Sql query Text

insert into Customer (CustomerCode,LdegerCode,CustomerPicture) values ('0001','9999',NULL)

if You use DBNull.Value its save Empty String in Column

answered Jan 30, 2020 at 17:30

Mansoor

1315 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related