CODEWITHSUNDEEP
c#databasewinforms
Farid-ur-Rahman
Farid-ur-Rahman

Reputation: 1869

What is wrong here? In my code (Inserting Image into Database - C#)

I am trying to insert an image in my access database from C# 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 show me an error: Syntax error in INSERT INTO statement. What is wrong here in my code? I can successfully insert text to the fields of database by using the same query.

Upvotes: 5

Views: 1120

Answers (6)

Clayton
Clayton

Reputation: 457

Try entering img as @img in your sql query:

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

EDIT: also end your sql query with a ';'. I added it in the query above.

Upvotes: 0

BizApps
BizApps

Reputation: 6130

Try this and add parameters to your other 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();
}

Best Regards

Upvotes: 1

Ravi Gadag
Ravi Gadag

Reputation: 15861

may be some input data is not in good form, leading to Sql injection . so i suggest you to try like this . with parametrized query. also try to give some good descriptive name to your columns. Image will be ambigious. as it will be treated as 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);

Upvotes: 0

Mr Lister
Mr Lister

Reputation: 46579

Image is a reserved word, so I assume you should put this word in quotes or square brackets in the SQL query.

Upvotes: 6

Rowland Shaw
Rowland Shaw

Reputation: 38130

You try to set the value for parameter "@img", but your query only mentions "img", so instead you need to set the query text thus:

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

Upvotes: 0

Jorge Cohen
Jorge Cohen

Reputation: 1522

I believe you forget to add an @-sign before your parameter:

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

should be

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

Upvotes: 0

Related Questions