MaChaToc MaChaToc - 2 months ago 7
SQL Question

Replacing an image in a database with c# (using UPDATE)

I have a database with images stored in it and I want to replace those images with new ones. Here is the thing: I have a picturebox and in this picturebox I am downloading an image. This image is the one that I want to use to replace the current image of my databaserow where my column "Id" is 6.
Right now I am trying to do it with UPDATE but it is not working and I am wondering why. Here is my code:

private void buttonReplace_Click(object sender, EventArgs e)
{
string sql = "SELECT * FROM [Insert_Image]"; //name of database
SqlDataAdapter dA = new SqlDataAdapter(sql, cn);
DataTable dT = new DataTable();
dA.Fill(dT);
int z = dT.Rows.Count + 1;
int i = 6;

try
{
cn.Open();
byte[] img = null;
FileStream fs = new FileStream(imgLoc, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
img = br.ReadBytes((int)fs.Length);

string strSql = "UPDATE [Insert_Image] SET Picture = " + @img + " WHERE Id LIKE '" + i + "'";
SqlCommand cmd = new SqlCommand(strSql, cn);

cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
MessageBox.Show(Convert.ToString(ex));
}
finally
{
cn.Close();
}
}


My Problem is that something is saved in my database, but whatever I am saving with this code is not the binary-code for an image. I checked my filestream and that is correct as I am using the same filestream I used for uploading to the picturebox. I would be very glad for any help or ideas.

Answer

do not hard code value in the query, instead use.

        cmd.Parameters.Addnew SqlParameter("@img", img));   

check this cheet sheet on SQL Injection

        string strSql = "UPDATE [tablename] SET Picture=@img WHERE Id  LIKE '%@i%'";

        SqlCommand cmd = new SqlCommand(strSql, cn);
        cmd.Parameters.Add(new SqlParameter("@img", img));
        cmd.Parameters.Add(new SqlParameter("@i", i));
        cmd.ExecuteNonQuery();
Comments