Stuck with a problem while making a simple winform program using access database..

Question

0.00/5 (No votes)

See more:

.NET4.5

C#

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ACCESS_DATABASE
{
    public partial class DailyMaterial : Form
    {
        OleDbConnection con = new OleDbConnection();
        public DailyMaterial()
        {
            InitializeComponent();
        }

        private void DailyMaterial_Load(object sender, EventArgs e)
        {
            try
            { 
                con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Carlos\Documents\Office.accdb;
Persist Security Info=False;";
                con.Open();
              label68.Text = "Connection successful";
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR: "+ex);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                con.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = con;
                cmd.CommandText = "insert into DailyMaterial (Dat,Milk Powder,Corn Flour,Flour,Ammonium Bi Carbonate,Sodium Bi Carbonate,"+
                "Baking Powder,Acid Sodium Pyro Phospate,Calcium Carbonate,Coca POwder Normal,Coca Powder Black,Glucose Powder,Sweet Cocnut,"+
                "Ethyl Vanillin,Citric Acid,Caramel,Lecithin,Gulucose Lquid,Sugar,Salt,Vegetable Fat)"
  +"values('"+dateTimePicker1.Value+"','"+textBox1.Text+"','"+textBox2.Text + "','"+ textBox3.Text +"','"+ textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + textBox10.Text + "','" + textBox11.Text + "','" + textBox12.Text + "','" + textBox13.Text + "','" + textBox14.Text + "','" + textBox15.Text + "','" + textBox16.Text + "','" + textBox17.Text + "','" + textBox18.Text + "','" + textBox19.Text + "','" + textBox20.Text + "',
       cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR: "+ex);
            }
        }

Now I'm Getting The ERROR:"Syntax error in INSERT INTO statement."

What I have tried:

I have tried everything from my side what i can do but no i'm fedup of this problem :@

Posted 20-May-16 22:52pm

Member 12528711

Updated 22-May-16 0:11am

Patrice T

v3

Add a Solution

5 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 · 2016-05-20T23:13:00

Don't do 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. Use Parametrized queries instead.
And if you want column names that contain spaces, you need to quote them:

SQL

INSERT INTO DailyMaterial (Dat, `Milk Powder`, `Corn Flour`, ...

Your problem will disappear at the same time...

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
        {
        cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
        cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
        cmd.ExecuteNonQuery();
        }
    }

BTW: Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...

Patrice T · Answer 2 · 2016-05-22T00:11:00

Solution 5

As I understand it, your code is wrong

C#

...
textBox18.Text + "','" + textBox19.Text + "','" + textBox20.Text + "',
       cmd.ExecuteNonQuery();

I think you forgot the end of INSERT INTO statement.

C#

...
textBox18.Text + "','" + textBox19.Text + "','" + textBox20.Text + "'");
       cmd.ExecuteNonQuery();

Even if this correct this code, you absolutely need to pay attention to code injection as stated in all solutions.

Posted 22-May-16 0:11am

Patrice T

Comments

George Jonsson 22-May-16 6:55am

You were probably the only one reading that slab of code to the end. :P

Patrice T 22-May-16 7:09am

May be :)

George Jonsson · Answer 3 · 2016-05-20T23:18:00

First of all, you should learn to use parameterized queries. The benefits are, for example:
1. Remove the risk of SQL injection attacks.
2. Makes the code easier to read and easier to debug.
3. You can set the type of your parameter, especially good for DateTime variables.

OleDbCommand.Parameters Property (System.Data.OleDb)[^]

c# - using parameters inserting data into access database - Stack Overflow[^]

That said I have air some concern about the number of columns you have and their specific names.
Wouldn't it be better to have two tables, one called DailyMaterials and one called Ingredients?

DailyMaterial

Id   Date         Description
1    2016-05-21   Some description if necessary

Ingredients

Id   IngredientName   Amount   DailyMaterialId
1    Milk Powder      30 g     1
2    Corn Flour       200 g    1
etc

Karthik_Mahalingam · Answer 4 · 2016-05-20T23:41:00

try this

C#

private void button1_Click(object sender, EventArgs e)
       {
           try
           {
               con.Open();
               OleDbCommand cmd = new OleDbCommand();
               cmd.Connection = con;

               string sql = "insert into DailyMaterial (Dat,[Milk Powder],[Corn Flour],Flour,[Ammonium Bi Carbonate],[Sodium Bi Carbonate]," +
               "[Baking Powder],[Acid Sodium Pyro Phospate],[Calcium Carbonate],[Coca POwder Normal],[Coca Powder Black],[Glucose Powder],[Sweet Cocnut]," +
               "[Ethyl Vanillin],[Citric Acid],Caramel,Lecithin,[Gulucose Lquid],Sugar,Salt,[Vegetable Fat]) values ( " +
               "@Dat,@Milk_Powder,@Corn_Flour,@Flour,@Ammonium_Bi_Carbonate,@Sodium_Bi_Carbonate,@Baking_Powder,@Acid_Sodium_Pyro_Phospate,@Calcium_Carbonate,@Coca_POwder_Normal,@Coca_Powder_Black,@Glucose_Powder,@Sweet_Cocnut,@Ethyl_Vanillin,@Citric_Acid,@Caramel,@Lecithin,@Gulucose_Lquid,@Sugar,@Salt,@Vegetable_Fat)";
               cmd.CommandText = sql;
               cmd.Parameters.AddWithValue("@Dat", dateTimePicker1.Value);
               cmd.Parameters.AddWithValue("@Milk_Powder", textBox1.Text);
               cmd.Parameters.AddWithValue("@Corn_Flour", textBox2.Text);
               cmd.Parameters.AddWithValue("@Flour", textBox3.Text);
               cmd.Parameters.AddWithValue("@Ammonium_Bi_Carbonate", textBox4.Text);
               cmd.Parameters.AddWithValue("@Sodium_Bi_Carbonate", textBox5.Text);
               cmd.Parameters.AddWithValue("@Baking_Powder", textBox6.Text);
               cmd.Parameters.AddWithValue("@Acid_Sodium_Pyro_Phospate", textBox7.Text);
               cmd.Parameters.AddWithValue("@Calcium_Carbonate", textBox8.Text);
               cmd.Parameters.AddWithValue("@Coca_POwder_Normal", textBox9.Text);
               cmd.Parameters.AddWithValue("@Coca_Powder_Black", textBox10.Text);
               cmd.Parameters.AddWithValue("@Glucose_Powder", textBox11.Text);
               cmd.Parameters.AddWithValue("@Sweet_Cocnut", textBox12.Text);
               cmd.Parameters.AddWithValue("@Ethyl_Vanillin", textBox13.Text);
               cmd.Parameters.AddWithValue("@Citric_Acid", textBox14.Text);
               cmd.Parameters.AddWithValue("@Caramel", textBox15.Text);
               cmd.Parameters.AddWithValue("@Lecithin", textBox16.Text);
               cmd.Parameters.AddWithValue("@Gulucose_Lquid", textBox17.Text);
               cmd.Parameters.AddWithValue("@Sugar", textBox18.Text);
               cmd.Parameters.AddWithValue("@Salt", textBox19.Text);
               cmd.Parameters.AddWithValue("@Vegetable_Fat", textBox20.Text);
               cmd.ExecuteNonQuery();
               con.Close();
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
       }

You should always use Parameterised query instead of concatenating the string values to form a query/statement, it will lead to SQL Injection[^] attacks.

Sergey Alexandrovich Kryukov · Answer 5 · 2016-05-21T06:24:00

Some more on SQL injection:

Your approach is wrong from the very beginning. The query composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

Are you getting the idea? The string taken from a control can be anything, including… a fragment of SQL code.

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

—SA

Stuck with a problem while making a simple winform program using access database..

5 solutions

Solution 1

Solution 5

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0