0

I am developing a database application on C# using MS Access for my perfume shop, i have created a table in ms access, named as "MIX", having columns (Brand name,Stock quantity,Retail price,Original price). I want to add their field through C# window form that i have created.

I am trying the following query for inserting my data but all the time i am getting an error "Syntax error in INSERT INTO statement" 

 private void button1_Click(object sender, EventArgs e)
    {
        con.Open();
        string str = string.Format("Insert into MIX([Brand name],Stock quantity,Retail price,Original price)Values('" + textBox1.Text + "'," + textBox2.Text + "," + textBox3.Text + "," + textBox4.Text + ")");
        OleDbCommand cmd = new OleDbCommand(str,con);
        cmd.ExecuteNonQuery();
        MessageBox.Show("Successfull");
        cmd.Dispose();
    }
Improve this question
1
  • Please show the value of str in your question. Commented Nov 28, 2013 at 1:52

3 Answers 3

Reset to default
3

Four things:

  1. You don't need string.Format when you're just concatenating values

  2. Column names with spaces must be surrounded by square brackets:

    string str = "Insert into MIX " + 
             "([Brand name],[Stock quantity],[Retail price],[Original price]) " + 
             "Values('" + textBox1.Text + "'," + textBox2.Text + "," + textBox3.Text + "," + textBox4.Text + ")";

  3. You should learn how to use Parameters instead of concatenating SQL:

    string str = "Insert into MIX " + 
             "([Brand name],[Stock quantity],[Retail price],[Original price]) " + 
             "Values (?,?,?,?)");
OleDbCommand cmd = new OleDbCommand(str,con);
cmd.Parameters.AddWithValue("brand",textBox1.Text);
... repeat for other values
  4. It appears you're reusing a shared OleDbConnection object. This is not a best practice since connections are pooled in .NET and are cheap to recreate after the first usage, and you don't hae to worry about leaving a connection open throughout the life of your application.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks D Stanley. it works. Actually i am new to MS access and C#. I hope i can learn much through this.
@user3043233 Glad it works - yes there are several "best practices" when working with commands that unfortunately aren't taught in most tutorials. Good luck in your learning!
0

This is a really bad idea since the values are being directly dropped into the query, but the problem you are experiencing is the multi-word column names:

Insert into MIX([Brand name],Stock quantity,Retail price,Original price)

These need to have bracket around them:

Insert into MIX([Brand name],[Stock quantity],[Retail price],[Original price])[
Improve this answer

Comments

0

You need to enclose column names that contain spaces insquare brackets []. Also, you need to use parameters instead of concatenating values to the SQL query like that. Google "SQL injection attack" to know the reason why.

Insert into MIX([Brand name],[Stock quantity],[Retail price],[Original price])Values(?,?,?,?)

You replace those ?'s with values this way:

cmd.Parameters.AddWithValue("?", Textbox1.text);
cmd.Parameters.AddWithValue("?", Textbox2.text);
cmd.Parameters.AddWithValue("?", Textbox3.text);
cmd.Parameters.AddWithValue("?", Textbox4.text);

Be aware that the order matters.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.