What is wrong here? In my code (Insert image into database - C #)

Question

I am trying to insert an image into my access database from winform. I am using the following code:

    private void button1_Click(object sender, EventArgs e)
    {
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb");
        OleDbCommand cmd = con.CreateCommand();
        cmd.CommandText = "INSERT INTO Table1 (Product, Manufacturer, Description, Price, Image) VALUES ('Column1', 'Column2', 'Column3', 'Column4', @img)";
        byte[] yourPhoto = imageToByteArray(pictureBox1.Image);
        cmd.Parameters.AddWithValue("@img", yourPhoto);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public byte[] imageToByteArray(System.Drawing.Image iImage)
    {
        MemoryStream mMemoryStream = new MemoryStream();
        iImage.Save(mMemoryStream, System.Drawing.Imaging.ImageFormat.Png);
        return mMemoryStream.ToArray();
    }

When I run the code, it shows me an error: Syntax error in INSERT INTO statement. What is wrong with my code? I can successfully insert text into database fields using the same query.

Answer 1

Image is a reserved word, so I assume that you should put this word in quotation marks or square brackets in an SQL query.

Answer 2

Try this and add the parameters to another column:

 private void button1_Click(object sender, EventArgs e)
    {
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb");
        OleDbCommand cmd = con.CreateCommand();
        cmd.CommandText = "INSERT INTO Table1 (Product, Manufacturer, Description, Price,[Image]) VALUES (@Product,@Manufacturer,@Description,@Price,@Image)";
        byte[] yourPhoto = imageToByteArray(pictureBox1.Image);
        cmd.Parameters.AddWithValue("@Product", "yourProductValue");
        cmd.Parameters.AddWithValue("@Manufacturer","yourManufacturerValue");
        cmd.Parameters.AddWithValue("@Description", "yourDescriptionValue");
        cmd.Parameters.AddWithValue("@Price","yourPriceValue");
        cmd.Parameters.AddWithValue("@Image", yourPhoto);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

public byte[] imageToByteArray(System.Drawing.Image iImage)
{
    MemoryStream mMemoryStream = new MemoryStream();
    iImage.Save(mMemoryStream, System.Drawing.Imaging.ImageFormat.Png);
    return mMemoryStream.ToArray();
}

Regards

Answer 3

img @img SQL-:

cmd.CommandText = "INSERT INTO Table1 (Product, Manufacturer, Description, Price, Image) VALUES ('Column1', 'Column2', 'Column3', 'Column4', @img);";

EDIT: sql ;. .

Answer 4

, @-sign :

    cmd.CommandText = "INSERT INTO Table1 (Product, Manufacturer, Description, Price, Image) VALUES ('Column1', 'Column2', 'Column3', 'Column4', img)";

    cmd.CommandText = "INSERT INTO Table1 (Product, Manufacturer, Description, Price, Image) VALUES ('Column1', 'Column2', 'Column3', 'Column4', @img)";

Answer 5

You are trying to set the value for the "@img" parameter, but only "img" is mentioned in your request, so instead you need to set the request text:

cmd.CommandText = "INSERT INTO Table1 (Product, Manufacturer, Description, Price, Image) VALUES ('Column1', 'Column2', 'Column3', 'Column4', @img )";

Answer 6

there may be some input that is not in good shape, resulting in an Sql injection. so I suggest you try this. with a parameterized query. also try giving some nice descriptive name for your columns. The image will be ambiguous. as this will be considered as a keyword.

cmd.CommandText = "INSERT INTO Table1 (Product, Manufacturer, Description, Price, [Image]) VALUES (@column1, @column2, @column3, @column4, @img )";
cmd.Parameters.AddWithValue("@column1", yourval);
cmd.Parameters.AddWithValue("@column2", yourval);
cmd.Parameters.AddWithValue("@column3", yourval);
cmd.Parameters.AddWithValue("@column4", yourval);
cmd.Parameters.AddWithValue("@img", yourPhoto);