CODEWITHSUNDEEP
c#
user3043233
user3043233

Reputation:

C# database application using Ms access 2010

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();
    }

Upvotes: 0

Views: 164

Answers (3)

D Stanley
D Stanley

Reputation: 152624

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.

Upvotes: 3

System Down
System Down

Reputation: 6270

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.

Upvotes: 0

Howard Renollet
Howard Renollet

Reputation: 4739

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])[

Upvotes: 0

Related Questions