CODEWITHSUNDEEP
c#sql-server
Yusuf Mohamed
Yusuf Mohamed

Reputation: 65

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

Upvotes: 4

Views: 8363

Answers (3)

Float
Float

Reputation: 39

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)

Upvotes: 0

Mansoor
Mansoor

Reputation: 133

**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

Upvotes: -1

Evandro de Paula
Evandro de Paula

Reputation: 2642

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.

Upvotes: 4

Related Questions