Insert and Update Image in sql

Question

I am storing images to the database in the table test (id, name, image), by reading images from a picture box.

This is my code:

private void browse_Click(object sender, EventArgs e)
{
    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    openFileDialog1.Filter = "(*.BMP;*.JPG;*.GIF;*.JPEG;*.PNG)|*.BMP;*.JPG;*.GIF;*.JPEG;*.PNG";
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        imgloc = openFileDialog1.FileName.ToString();
        pictureBox1.ImageLocation = imgloc;
    }
}

private void save_Click(object sender, EventArgs e)
{
    byte[] img = null;
    FileStream fs = new FileStream(imgloc, FileMode.Open, FileAccess.Read);
    BinaryReader br = new BinaryReader(fs);
    img = br.ReadBytes((int)fs.Length);
    SqlConnection CN = new SqlConnection(constring);
    string Query = "insert into test (id,name,image) values('" + txtid.Text + "','" + txtname.Text + "',@img)";
    CN.Open();
    cmd = new SqlCommand(Query, CN);
    cmd.Parameters.Add(new SqlParameter("@img", img)); 
    cmd.ExecuteNonQuery();
    CN.Close();
}

It works but I would like to know how to use the update command here.

instead of insert use UPDATE query

Satinder singh
– Satinder singh

2013-07-25 11:26:44 +00:00
Commented Jul 25, 2013 at 11:26 — Satinder singh
– Satinder singh, Commented Jul 25, 2013 at 11:26
What do you mean with "update command"?

Oscar
– Oscar

2013-07-25 11:27:17 +00:00
Commented Jul 25, 2013 at 11:27 — Oscar
– Oscar, Commented Jul 25, 2013 at 11:27

Anant Dabhi · Accepted Answer · 2013-07-25 11:31:42Z

3

private void update_Click(object sender, EventArgs e)
    {
        byte[] img = null;
        FileStream fs = new FileStream(imgloc, FileMode.Open, FileAccess.Read);
        BinaryReader br = new BinaryReader(fs);
        img = br.ReadBytes((int)fs.Length);
        SqlConnection CN = new SqlConnection(constring);

        // this is a smaple query for update statement and update where id=@id
        string Query = "update test set name=@name,image=@img where id=@id ";

        CN.Open();
        cmd = new SqlCommand(Query, CN);
        cmd.Parameters.Add(new SqlParameter("@img", img));
        cmd.Parameters.Add(new SqlParameter("@id", txtid.Text));
        cmd.Parameters.Add(new SqlParameter("@name", txtname.Text));
        cmd.ExecuteNonQuery();
        CN.Close();
    }

answered Jul 25, 2013 at 11:31

Anant Dabhi

11.2k3 gold badges33 silver badges51 bronze badges

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

Comments

sm_ · Accepted Answer · 2013-07-25 11:31:32Z

0

Your code and query should be like this :

SqlConnection CN = new SqlConnection(constring);
string Query = "Update test Set name=@Name,image=@Image where id=@id"
CN.Open();
cmd = new SqlCommand(Query, CN);
cmd.Parameters.Add(new SqlParameter("@Image", img));
cmd.Parameters.Add(new SqlParameter("@Name",txtname.Text));
cmd.Parameters.Add(new SqlParameter("@id",txtid.Text));
cmd.ExecuteNonQuery();
CN.Close();

answered Jul 25, 2013 at 11:31

sm_

2,6122 gold badges19 silver badges34 bronze badges

Comments

yogesh lawate · Accepted Answer · 2013-07-25 12:03:42Z

0

Just delete that particular record using your Id field and Fire the Save query again, if updating is difficult.

answered Jul 25, 2013 at 12:03

yogesh lawate

2271 gold badge2 silver badges11 bronze badges

Comments

barnacle.m · Accepted Answer · 2020-03-30 12:08:17Z

0

 SqlConnection con = Connectionclass.SQLCONNECTION();
 SqlDataAdapter da = new SqlDataAdapter();
 string query = ("Update Doctor set ID ='" + idtxt.Text + "',Name='" + nametxt.Text + "',Contact='" + contactxt.Text + "',CNIC='" + cnictxt.Text + "',Address='" + addresstxt.Text + "',Qualification='" + qualitxt.Text + "',specialization='" + specialtxt.Text + "',Gender='" + gendertxt.Text + "',DOB='" + dobtxt.Text + "', Fee='"+textBox1.Text+"',Date='" + System.DateTime.Today.ToString("dd-MM-yyyy") + "', Picture= @image  where ID='" + idtxt.Text + "'");
 da.UpdateCommand = new SqlCommand(query, con);
 con.Open();
 da.UpdateCommand.Parameters.Add("image", SqlDbType.VarBinary).Value = binaryphoto;
 int RowsEffected = da.UpdateCommand.ExecuteNonQuery();
 con.Close();

edited Mar 30, 2020 at 12:08

barnacle.m

2,2193 gold badges42 silver badges87 bronze badges

answered Mar 30, 2020 at 11:23

Sajjad Khalid

11 bronze badge

1 Comment

barnacle.m Over a year ago

Hi there, thanks for your answer. Please try explain your solution a little better with the steps you followed to solve the problem. Additionally, when pasting in code, remember to format it with Ctrl+K or backticks.

Collectives™ on Stack Overflow

Insert and Update Image in sql

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related