Convert image to binary to store in database in asp.net

Question

I want to store the image(any type of image) into the database by using varbinary(MAX).

My Database:

CREATE TABLE [dbo].[Pic]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [picture] VARBINARY(MAX) NULL, 
    [origin] NVARCHAR(100) NULL
)

My code:

protected void Button1_Click(object sender, EventArgs e)
{
    byte[] imagebyte = File.ReadAllBytes(Server.MapPath("~/") + imageUpload1);

    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "insert into Pic values('"+ imagebyte +"','"+ lblOrigin.Text +"')";
    cmd.ExecuteNonQuery();
}

when I run my code, I get this error:

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. at cmd.ExecuteNonQuery();

How can I solve this problem?

Images are binary. The problem is that you're using string concatenation to create queries, something that exposes you to SQL injection attacks, conversion errors and actually makes it impossible to pass dates, numbers or ... images without conversion issues. Use parameterized queries instead. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 27, 2019 at 8:31
Does this answer your question? How to insert PictureBox to Sql Server Database Varbinary(MAX) with C#? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 27, 2019 at 8:32

GvS · Accepted Answer · 2019-11-27 08:36:13Z

2

Always use parameterized sql queries, to retrieve data, but also to store data. This will prevent SQL injection attacks from happening, and will enable you to store (large) binary objects in your database.

using (SqlCommand cmd = con.CreateCommand()) {
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = "insert into Pic (picture, origin) values(@image, @origin)";

  // cmd.Parameters.AddWithValue("@image", imagebyte);
  cmd.Parameters.Add("@image", SqlDbType.VarBinary);
  cmd.Parameters["@image"].Value = imagebyte;

  cmd.Parameters.AddWithValue("@origin", lblOrigin.Text);
  cmd.ExecuteNonQuery();
}

edited Nov 27, 2019 at 8:36

answered Nov 27, 2019 at 8:28

GvS

52.5k16 gold badges104 silver badges139 bronze badges

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

1 Comment

Panagiotis Kanavos Over a year ago

AddWithValue is dangerous as string concatenation. In this case it will assume that the actual parameter size equals the length of the values, whether it does or not. This can easily lead to truncation

Hounddog75 · Accepted Answer · 2019-11-30 08:27:54Z

0

Here is a resource for you to look at. I think this will solve your problem.

Just a note: Use stored procedures when doing operations on a database in your code.

answered Nov 30, 2019 at 8:27

Hounddog75

756 bronze badges

Collectives™ on Stack Overflow

Convert image to binary to store in database in asp.net

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related