How can upload image file to ms access database using C#

Question

1.00/5 (1 vote)

See more:

protected void Button2_Click(object sender, EventArgs e)
    {
        OleDbConnection con = new OleDbConnection();
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Database/registration.accdb");
        con.Open();
            OleDbDataAdapter SQLAdapter = new OleDbDataAdapter("insert into tblproducts([pname],[pprice],[pselprice],[pcatid],[psubid],[psize],[pmaterial],[pcolor],[pdimension],[pitemweight],[pmaxweight],[pdescription],[pproductdetails]) values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + DropDownList1.SelectedItem.Value + "','" + DropDownList2.SelectedItem.Value + "','" + DropDownList3.SelectedItem.Value + "','" + TextBox7.Text + "','" + TextBox8.Text + "','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox11.Text + "','" + TextBox12.Text + "','" + TextBox13.Text + "')", con);
            DataTable DT = new DataTable();
            SQLAdapter.Fill(DT);
            TextBox1.Text = "";
            TextBox2.Text = "";
            TextBox3.Text = "";
            DropDownList1.SelectedItem.Value = "";
            DropDownList2.SelectedItem.Value = "";
            DropDownList3.SelectedItem.Value = "";
            TextBox7.Text = "";
            TextBox8.Text = "";
            TextBox9.Text = "";
            TextBox10.Text = "";
            TextBox11.Text = "";
            TextBox12.Text = "";
            TextBox13.Text = "";
            TextBox1.Focus();
            
        
        string pid = "select pid from tblproducts where id= (select max(id) from products)";
           
        if (FileUpload1.HasFile)
        {
            string SavePath = Server.MapPath("~/Images/ProductImages/") + pid;
            if (!Directory.Exists(SavePath))
            {
                Directory.CreateDirectory(SavePath);
            }
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(SavePath + "\\" + TextBox1.Text.ToString().Trim() + "01");
            OleDbDataAdapter SQLAdapter1 = new OleDbDataAdapter("insert into tblpimage([pid],[picname],[extension]) values('" + pid + "','" + TextBox1.Text.ToString().Trim() + "01" + "','" + Extension + "')", con);
            DataTable Dm = new DataTable();
            SQLAdapter1.Fill(Dm);
        }
        Response.Write("Congratulations, Product Added Successfully!!!");
    }

What I have tried:

i want to save product detail and product image in two different tables tblproducts and tblpimage with id(autonumber value) of tblproduct table attached to tblpimage table which are linked with foreign key. after writting this code it shows syntax error please help me to correct this error this is my school project

Posted 6-Sep-21 8:35am

Kaise karen?

Updated 6-Sep-21 16:57pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-09-06T09:07:00

Don't do it like that: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that through your whole app, and the syntax error will probably go away at the same time.

Then you can think about uploading image data: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^]

Wendelius · Answer 2 · 2021-09-06T16:57:00

As said in solution 1, always use parameters. Another observation is that you try to handle next key values in your program. This should not be done as it may lead to key conflicts, instead you should use the AutoNumber functionality in Access. See AutoNumber - Wikipedia[^]

For an example how to use it, have a look at Retrieving Identity or Autonumber Values - ADO.NET | Microsoft Docs[^]

How can upload image file to ms access database using C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0