C# : how to upload image to a SQL Server database using varbinary(Max)

Question

I have been trying to upload images to my database, but I get this error:

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

Here is my code:

con.Open();

cmd = new SqlCommand("SELECT * FROM ImagePosts", con);
ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);

int id = ds.Tables[0].Rows.Count + 1;

byte[] buffer = File.ReadAllBytes(file);

SqlCommand cmd2 = new SqlCommand("INSERT INTO Images (Id, Title, Image) VALUES('" + id + "', '" + textBox1.Text + "', '" + "@image" + "')", con);

var binary1 = cmd2.Parameters.Add("@image", SqlDbType.VarBinary, -1);
binary1.Value = buffer;

cmd2.ExecuteNonQuery();
con.Close();
this.Close();

Edit: my bad, I forgot to remove the parenthesis around @image.

Leaving aside the actual error here, I don't think it is wise to store files in a database. Why don't you use the filesystem instead, and store the filepaths in the database? stackoverflow.com/a/13421029/1666620 — user1666620
– user1666620, Commented Apr 6, 2020 at 15:22
remove the single quotes around the @image parameter. and use the parameter approach for the textBox1.Text too. — Cee McSharpface
– Cee McSharpface, Commented Apr 6, 2020 at 15:29
@user1666620 two reasons pro: transactional integrity, one-file-contains-all backup. two reasons contra external storage and path reference: external paths can move, and server may not always have access to them unless (5 not obvious conditions are met), and would require a separate logic to safely write and retrieve them. — Cee McSharpface
– Cee McSharpface, Commented Apr 6, 2020 at 15:38
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Apr 6, 2020 at 16:37
And don't just parameterize SOME of the column values - do that for ALL of the them! — SMor
– SMor, Commented Apr 6, 2020 at 17:18

Daniel Lorenz · Accepted Answer · 2020-04-06 15:41:02Z

The issue is that you are inserting '@image' when it should just be @image. If you put '', that is saying you want to insert the value "@image" into the field. Drop the '' and it should work. However, I would also recommend doing the same thing for the Textbox.Text or else you can get Sql Injected:

           con.Open();
        cmd = new SqlCommand("SELECT * FROM ImagePosts", con);
        ad = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        ad.Fill(ds);
        int id = ds.Tables[0].Rows.Count + 1;


        byte[] buffer = File.ReadAllBytes(file);
        SqlCommand cmd2 = new SqlCommand("INSERT INTO Images (Id, Title, Image) VALUES('" + id + "', @Title, @image)", con);
        cmd2.Parameters.Add("@Title", textBox1.Text);
        var binary1 = cmd2.Parameters.Add("@image", SqlDbType.VarBinary, -1);
        binary1.Value = buffer;
        cmd2.ExecuteNonQuery();
        con.Close();
        this.Close();

You could even do it with the ID one, too.

mituw16 · Accepted Answer · 2020-04-06 15:33:54Z

0

You don't need to wrap varbinary data in single quotes in sql server. Try this

SqlCommand cmd2 = new SqlCommand("INSERT INTO Images (Id, Title, Image) VALUES('" + id + "', '" + textBox1.Text + "',  @image)", con);

edited Apr 6, 2020 at 15:33

answered Apr 6, 2020 at 15:29

mituw16

5,2584 gold badges31 silver badges56 bronze badges

3 Comments

Cee McSharpface Over a year ago

(why? it would mean the string '@image', which is implicitely a varchar, instead of the @image parameter's value, which at the same time explains the error message OP gets)

mituw16 Over a year ago

I don't understand what you're asking....but varbinary data is not encapsulated by single quotes in sql server

Cee McSharpface Over a year ago

I felt that your expression "don't need to" is too weak and does not explain much: it's not optional - it's the cause for the error they're seeing. this is a suggestion to add the explanation to your answer. And while OP is at fixing their statement, they should remove the injection vulnerability that textBox1.Text is, too.

Collectives™ on Stack Overflow

C# : how to upload image to a SQL Server database using varbinary(Max)

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related