CODEWITHSUNDEEP
c#sql-serverdatabaseimage
me990th
me990th

Reputation: 9

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

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.

Upvotes: 0

Views: 2364

Answers (2)

Daniel Lorenz
Daniel Lorenz

Reputation: 4336

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.

Upvotes: 2

mituw16
mituw16

Reputation: 5250

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

Upvotes: 0

Related Questions